如何实现数字类型的数据自动补充前导0

在MySQL中，ZEROFILL是一种用于在数字字段上进行填充零的属性。该属性通常用于确保数字达到指定长度时，左侧用零进行填充。这在某些特定场景下非常有用，例如确保订单号或者其他标识符具有固定的长度。

1. 案例演示

1.1 语法简介

关于ZEROFILL属性是作用于数字类型的列上的，属于列属性，对于的语法简单描述如下：

CREATE TABLE your_table (
    your_column INT ZEROFILL,
    -- 或者
    your_column INT UNSIGNED ZEROFILL
);
 

1.2 案例

下面简单举例演示，假设我们有一个存储订单编号的表，我们希望订单编号始终为5位数，并在不足5位时用零进行填充。

先创建表，SQL脚本如下：

# 创建表
CREATE TABLE orders (
    id  int primary key auto_increment,
    order_id INT(5) UNSIGNED ZEROFILL,
    product_name VARCHAR(50),
    order_date DATE
);

写入一些数据

INSERT INTO orders (order_id, product_name, order_date) VALUES
(1, 'Product A', '2024-01-10'),
(22, 'Product B', '2024-01-11'),
(333, 'Product C', '2024-01-12'),
(4444, 'Product D', '2024-01-13');

查看结果如下

可见结果显示时按照5位数显示订单编码字段内容，不足5位的在其左侧进行前导0填充。

1.3 超过指定长度如何显示

上例中，如果插入的订单编号超过5位，存储和显示会截断么？答案是否定的，例如：

# 插入一条订单编号是6位的记录
INSERT INTO orders (order_id, product_name, order_date) 
VALUES  (1111111, 'Product E', '2024-01-10')

结果显示如下，可以正常显示：

2. 其他方式实现类似功能

除了ZEROFILL属性，还可以通过在查询时使用LPAD函数来实现类似的功能。LPAD的三个参数分别为列名、目标长度、和填充字符。例如：

SELECT LPAD(order_id, 8, '0') AS padded_order_id, product_name, order_date
FROM orders;

差别：

使用LPAD指定长度时，会出现截断的情况，例如：

SELECT LPAD(order_id, 5, '0') AS padded_order_id, product_name, order_date
FROM orders;

结果为：

3. 小结

在以下场景中，可以考虑用zerofill属性或使用lpad函数来实现需求：

• 标识符填充：在需要保持标识符固定长度的场景下，比如订单编号、产品编号等
• 对齐要求：当在表格或报告中需要数字对齐时，ZEROFILL属性可以确保数字始终占据相同的字符宽度
• 数据整齐性：用于提高数据整齐性，方便人工查看和处理