简单却强大：MySQL ZEROFILL让编号管理变得更轻松

点击上方蓝字关注我

    在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属性可以确保数字始终占据相同的字符宽度

• 数据整齐性：用于提高数据整齐性，方便人工查看和处理

往期精彩回顾

1.  MySQL高可用之MHA集群部署

2.  mysql8.0新增用户及加密规则修改的那些事

3.  比hive快10倍的大数据查询利器-- presto

4.  监控利器出鞘：Prometheus+Grafana监控MySQL、Redis数据库

5.  PostgreSQL主从复制--物理复制

6.  MySQL传统点位复制在线转为GTID模式复制

7.  MySQL敏感数据加密及解密

8.  MySQL数据备份及还原（一）

9.  MySQL数据备份及还原（二）

扫码关注