MySQL编程进阶之：`ZEROFILL`和`UNSIGNED`属性的用法与影响。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位观众老爷，大家好！我是你们的老朋友，今天咱们聊点MySQL里的小技巧，但用好了能让你的数据库更上一层楼的东西：ZEROFILL 和 UNSIGNED 属性。

(一) 啥是ZEROFILL？别告诉我你只想着零食！

好家伙，一说 ZEROFILL，我猜不少人脑子里冒出来的是不是各种零食？薯片、辣条、小蛋糕… 但咱们今天说的 ZEROFILL 可跟吃没啥关系，它跟数据库里数字的显示方式有关。

简单来说，ZEROFILL 就是让你的数字字段在显示的时候，如果位数不够，前面自动用 0 来填充。听起来有点像银行卡号，但比银行卡号更灵活。

1. 声明ZEROFILL：

假设我们要创建一个用户表，其中用户ID是整数类型，并且我们希望用户ID始终显示为6位数，不足6位的用0填充。

CREATE TABLE users (
  user_id INT(6) ZEROFILL PRIMARY KEY,
  username VARCHAR(50)
);

注意：

INT(6) 里的 6 指定的是显示宽度，不是存储大小。INT 类型的存储大小是固定的，跟括号里的数字没关系。
ZEROFILL 只能用于整数类型，比如 INT、TINYINT、SMALLINT、MEDIUMINT、BIGINT。
如果字段已经存在，可以使用 ALTER TABLE 来添加 ZEROFILL 属性。

2. 插入数据看看效果：

INSERT INTO users (user_id, username) VALUES (1, '张三');
INSERT INTO users (user_id, username) VALUES (123, '李四');
INSERT INTO users (user_id, username) VALUES (999999, '王五');

3. 查询结果：

SELECT * FROM users;

你将会看到类似这样的结果：

user_id	username
000001	张三
000123	李四
999999	王五

看到没？ user_id 不足 6 位的，前面自动补了 0。

4. ZEROFILL 的隐式特性：

当你给一个字段加上 ZEROFILL 属性的时候，MySQL 会默默地给它加上 UNSIGNED 属性。也就是说，它只能存储非负数。如果你想存储负数，就不能用 ZEROFILL。

5. ZEROFILL 的优缺点：

优点：
- 使数据更易读，尤其是在需要固定位数的场景下。
- 在某些排序场景下，可以简化排序逻辑（比如按用户ID排序）。
缺点：
- 实际上存储的还是整数，只是显示的时候做了处理。
- 会强制 UNSIGNED 属性，限制了负数的存储。
- 纯粹是显示特性，并不会影响数据的实际存储。

6. ZEROFILL 的使用场景：

订单号、编号等需要固定位数的场景。 比如订单号 20240101000001，可以确保所有订单号都是 14 位。
需要按数字大小排序，但数据位数不一致的场景。 比如用户ID，如果位数不一致，排序结果可能不符合预期。

7. 取消 ZEROFILL 属性：

如果你想取消 ZEROFILL 属性，可以使用 ALTER TABLE 语句：

ALTER TABLE users MODIFY user_id INT(6) NULL;

注意：你需要重新定义字段类型，并且不能包含 ZEROFILL 关键字。 NULL 只是一个示例，你需要根据实际情况选择合适的属性。

(二) UNSIGNED：不签名的整数，我的地盘我做主！

UNSIGNED 的意思就是“无符号”，专门用来修饰整数类型。简单来说，就是让整数只能存储非负数（0 和正数），不能存储负数。

1. 声明 UNSIGNED：

CREATE TABLE products (
  product_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  price DECIMAL(10, 2) UNSIGNED,
  quantity INT UNSIGNED
);

这里，product_id 和 quantity 都使用了 UNSIGNED 属性。

2. UNSIGNED 的好处：

使用了 UNSIGNED 之后，你的整数类型可以存储更大的正数。这是因为原本用来表示正负号的那一位，现在也可以用来存储数字了。

整数类型	存储大小 (字节)	有符号范围	无符号范围
TINYINT	1	-128 到 127	0 到 255
SMALLINT	2	-32768 到 32767	0 到 65535
MEDIUMINT	3	-8388608 到 8388607	0 到 16777215
INT	4	-2147483648 到 2147483647	0 到 4294967295
BIGINT	8	-9223372036854775808 到 9223372036854775807	0 到 18446744073709551615
DECIMAL(m,d)	m+2 (approx.)	-10^(m-d) 到 10^(m-d)	0 到 10^(m-d)

3. 使用场景：

自增ID： AUTO_INCREMENT 的字段通常都会设置为 UNSIGNED，因为ID一般都是正数。
数量、金额等不可能为负数的场景。 比如商品数量、订单金额、用户积分等。
存储时间戳。 通常时间戳都是正数。

4. 注意事项：

DECIMAL 类型也可以使用 UNSIGNED 属性。
使用 UNSIGNED 属性后，如果插入负数，MySQL 会报错或者自动转换为 0，具体行为取决于 MySQL 的版本和配置。

5. 示例：

-- 创建一个订单表
CREATE TABLE orders (
  order_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  total_amount DECIMAL(10, 2) UNSIGNED NOT NULL,
  order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入数据
INSERT INTO orders (user_id, total_amount) VALUES (1, 99.99);
INSERT INTO orders (user_id, total_amount) VALUES (2, 199.99);

-- 查询结果
SELECT * FROM orders;

(三) ZEROFILL 和 UNSIGNED 的爱恨情仇

就像一对欢喜冤家，ZEROFILL 和 UNSIGNED 经常一起出现，但它们的关系又有点复杂。

1. ZEROFILL 强制 UNSIGNED：

前面说过，当你给一个字段加上 ZEROFILL 属性的时候，MySQL 会自动给它加上 UNSIGNED 属性。这是因为 ZEROFILL 主要用于显示，而负数没有补 0 的意义。

2. UNSIGNED 不是强制 ZEROFILL：

反过来，UNSIGNED 属性并不会自动添加 ZEROFILL 属性。你可以单独使用 UNSIGNED，而不用 ZEROFILL。

3. 如何同时使用 ZEROFILL 和 UNSIGNED：

CREATE TABLE items (
  item_id INT(10) UNSIGNED ZEROFILL PRIMARY KEY,
  item_name VARCHAR(50)
);

这样，item_id 既是无符号的，又是用 0 填充的。

(四) 进阶用法：ZEROFILL 的格式化输出

虽然 ZEROFILL 本身只是一个显示属性，但我们可以结合其他函数，实现更灵活的格式化输出。

1. LPAD 函数：

LPAD(str, len, padstr) 函数可以将字符串 str 左填充到 len 长度，用 padstr 填充。

SELECT LPAD(123, 6, '0'); -- 输出 '000123'

2. 结合 LPAD 和 UNSIGNED：

即使你的字段没有 ZEROFILL 属性，你也可以使用 LPAD 函数来格式化输出：

CREATE TABLE articles (
  article_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255)
);

INSERT INTO articles (title) VALUES ('MySQL 教程');
INSERT INTO articles (title) VALUES ('Python 教程');

SELECT LPAD(article_id, 6, '0') AS formatted_id, title FROM articles;

这样，即使 article_id 没有 ZEROFILL 属性，你也可以得到格式化后的ID。

(五) 最佳实践：ZEROFILL 和 UNSIGNED 的正确使用姿势

谨慎使用 ZEROFILL： 除非你有明确的需求，否则不要随意使用 ZEROFILL。因为它会强制 UNSIGNED 属性，并且仅仅是显示特性，可能会造成误解。
明确 UNSIGNED 的适用场景： 在数量、金额、ID 等不可能为负数的场景下，优先考虑使用 UNSIGNED 属性。
使用 LPAD 函数进行更灵活的格式化输出： 如果你只需要格式化输出，而不是改变数据的存储方式，可以使用 LPAD 函数。
考虑数据迁移的兼容性： 如果你的数据库需要迁移到其他系统，需要考虑 ZEROFILL 和 UNSIGNED 属性的兼容性。不同的数据库系统对这些属性的支持可能有所不同。
注释你的代码： 在使用 ZEROFILL 和 UNSIGNED 属性时，添加注释说明原因，方便其他人理解你的代码。

(六) 总结：

ZEROFILL 和 UNSIGNED 都是 MySQL 中很有用的属性，但需要谨慎使用。理解它们的特性和适用场景，才能更好地利用它们，提高数据库的效率和可维护性。

特性	ZEROFILL	UNSIGNED
作用	数字字段自动补 0	整数类型只能存储非负数
适用类型	整数类型 (INT, TINYINT, 等)	整数类型和 DECIMAL
隐式特性	强制 UNSIGNED	无
存储影响	无	扩大正数存储范围
使用场景	需要固定位数的编号、ID等	数量、金额等不可能为负数的场景、自增ID等
注意事项	仅仅是显示特性，会强制 UNSIGNED	插入负数可能会报错或自动转换为0

好了，今天的讲座就到这里。希望大家能对 ZEROFILL 和 UNSIGNED 有更深入的了解。记住，技术就像工具，用对了就能事半功倍，用错了就可能适得其反。咱们下次再见！

发表回复 取消回复

发表回复取消回复