在 MySQL 中使用 zerofill 有什么好处？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5256469/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the benefit of zerofill in MySQL?
提问by diEcho
I just want to know what is the benefit/usage of defining ZEROFILLfor INTDataType in MySQL?
我只是想知道什么是确定的利益/用途ZEROFILL用于INT在数据类型MySQL？
`id` INT UNSIGNED ZEROFILL NOT NULL
回答by Mark Byers
When you select a column with type ZEROFILLit pads the displayed value of the field with zeros up to the display width specified in the column definition. Values longer than the display width are not truncated. Note that usage of ZEROFILLalso implies UNSIGNED.
当您选择带有类型的列时，ZEROFILL它会用零填充字段的显示值，直至列定义中指定的显示宽度。长于显示宽度的值不会被截断。请注意， 的用法ZEROFILL也意味着UNSIGNED。
Using ZEROFILLand a display width has no effect on how the data is stored. It affects only how it is displayed.
使用ZEROFILL和显示宽度对数据的存储方式没有影响。它只影响它的显示方式。
Here is some example SQL that demonstrates the use of ZEROFILL:
下面是一些示例 SQL，它演示了如何使用ZEROFILL：
CREATE TABLE yourtable (x INT(8) ZEROFILL NOT NULL, y INT(8) NOT NULL);
INSERT INTO yourtable (x,y) VALUES
(1, 1),
(12, 12),
(123, 123),
(123456789, 123456789);
SELECT x, y FROM yourtable;
Result:
结果：
x y
00000001 1
00000012 12
00000123 123
123456789 123456789
回答by Phil
One example in order to understand, where the usage of ZEROFILLmight be interesting:
一个例子来理解，其中的用法ZEROFILL可能很有趣：
In Germany, we have 5 digit zipcodes. However, those Codes may start with a Zero, so 80337is a valid zipcode for munic, 01067is a zipcode of Berlin.
在德国，我们有 5 位邮政编码。但是，这些代码可能以零开头，因此80337munic 的有效01067邮政编码也是柏林的邮政编码。
As you see, any German citizen expects the zipcodes to be displayed as a 5 digit code, so 1067looks strange.
如您所见，任何德国公民都希望邮政编码显示为 5 位代码，因此1067看起来很奇怪。
In order to store those data, you could use a VARCHAR(5)or INT(5) ZEROFILLwhereas the zerofilled integer has two big advantages:
为了存储这些数据，您可以使用 a VARCHAR(5)orINT(5) ZEROFILL而零填充整数有两大优点：
- Lot lesser storage space on hard disk
- If you insert
1067, you still get01067back
- 硬盘上的存储空间更少
- 如果您插入
1067，您仍然会01067返回
Maybe this example helps understanding the use of ZEROFILL.
也许这个例子有助于理解ZEROFILL.
回答by Elijah Saounkine
It's a feature for disturbed personalities who like square boxes.
对于喜欢方形盒子的不安人士来说，这是一项功能。
You insert
你插入
1
23
123
but when you select, it pads the values
但是当您选择时，它会填充值
000001
000023
000123
回答by Ken
It helps in correct sorting in the case that you will need to concatenatethis "integer" with something else (another number or text) which will require to be sorted as a "text" then.
如果您需要将此“整数”与其他需要排序为“文本”的其他内容（另一个数字或文本）连接起来，它有助于正确排序。
for example,
例如，
if you will need to use the integer field numbers (let's say 5) concatenatedas A-005 or 10/0005
如果您需要使用连接为 A-005 或 10/0005的整数字段编号（假设为 5）
回答by Marlin
I know I'm late to the party but I find the zerofill is helpful for boolean representations of TINYINT(1). Null doesn't always mean False, sometimes you don't want it to. By zerofilling a tinyint, you're effectively converting those values to INT and removing any confusion ur application may have upon interaction. Your application can then treat those values in a manner similar to the primitive datatype True = Not(0)
我知道我迟到了，但我发现 zerofill 对 TINYINT(1) 的布尔表示很有帮助。Null 并不总是意味着 False，有时您不希望它如此。通过对 tinyint 进行零填充，您可以有效地将这些值转换为 INT 并消除您的应用程序在交互时可能产生的任何混淆。然后，您的应用程序可以以类似于原始数据类型 True = Not(0) 的方式处理这些值
回答by zloctb
mysql> CREATE TABLE tin3(id int PRIMARY KEY,val TINYINT(10) ZEROFILL);
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO tin3 VALUES(1,12),(2,7),(4,101);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM tin3;
+----+------------+
| id | val |
+----+------------+
| 1 | 0000000012 |
| 2 | 0000000007 |
| 4 | 0000000101 |
+----+------------+
3 rows in set (0.00 sec)
mysql>
mysql> SELECT LENGTH(val) FROM tin3 WHERE id=2;
+-------------+
| LENGTH(val) |
+-------------+
| 10 |
+-------------+
1 row in set (0.01 sec)
mysql> SELECT val+1 FROM tin3 WHERE id=2;
+-------+
| val+1 |
+-------+
| 8 |
+-------+
1 row in set (0.00 sec)
回答by Adeel
If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.
如果为数字列指定 ZEROFILL，MySQL 会自动向该列添加 UNSIGNED 属性。
Numeric data types that permit the UNSIGNED attribute also permit SIGNED. However, these data types are signed by default, so the SIGNED attribute has no effect.
允许 UNSIGNED 属性的数字数据类型也允许 SIGNED。但是，默认情况下这些数据类型是有符号的，因此 SIGNED 属性不起作用。
Above description is taken from MYSQL official website.
以上描述摘自MYSQL官网。
回答by Daniel Kutik
When used in conjunction with the optional (nonstandard) attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(4) ZEROFILL, a value of 5 is retrieved as 0005.
当与可选（非标准）属性 ZEROFILL 结合使用时，空格的默认填充将替换为零。例如，对于声明为 INT(4) ZEROFILL 的列，值 5 检索为 0005。
回答by Simon H
ZEROFILL
零填充
This essentially means that if the integer value 23 is inserted into an INT column with the width of 8 then the rest of the available position will be automatically padded with zeros.
这实质上意味着如果将整数值 23 插入到宽度为 8 的 INT 列中，则可用位置的其余部分将自动用零填充。
Hence
因此
23
becomes:
变成：
00000023
