php 将 MySQL 数据库中整数的前导零存储为 INTEGER
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6873354/
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
Storing leading zeros of integers in MySQL database as INTEGER
提问by Michael
I need MySQL to store numbers in a integer field and maintain leading zeros. I cannot use the zerofill option as my current field is Bigint(16) and numbers can vary in amount of leading zeros. IE: 0001 - 0005, then 008 - 010 may need to be stored. I am not concerned about uniqueness of numbers (these aren't being used as IDs or anything) but I still need them to be stored preferably as INTS.
我需要 MySQL 将数字存储在整数字段中并保持前导零。我不能使用 zerofill 选项，因为我当前的字段是 Bigint(16) 并且数字的前导零数量可能会有所不同。IE: 0001 - 0005，那么可能需要存储 008 - 010。我不关心数字的唯一性（这些不用作 ID 或任何东西），但我仍然需要将它们最好存储为 INTS。
The issue using CHAR/VARCHAR and then typecasting the values as integers in PHP means that sorting results via queries leads to alphanumeric sorting, IE: SORT BY number ASC would produce
在 PHP 中使用 CHAR/VARCHAR 然后将值类型转换为整数的问题意味着通过查询对结果进行排序会导致字母数字排序，即：SORT BY number ASC 会产生
001
002
003
1
100
101
102
2
Clearly not in numerical order, but in alphanumeric order, which isn't wanted.
显然不是按数字顺序，而是按字母数字顺序，这是不想要的。
Hoping for some clever workarounds :)
希望有一些聪明的解决方法:)
回答by ypercube??
Keep the numbers stored as integers.
保持数字存储为整数。
Then use function LPAD()to show the numbers (left) padded with zeros:
然后使用函数LPAD()显示用零填充的数字（左）：
SELECT LPAD( 14, 7, '0') AS padded;
| padded |
-----------
| 0000014 |
If the number of zerofill characters is variable, add another column in the table with that (zerofill) length.
如果 zerofill 字符的数量是可变的，则在表中添加具有该（zerofill）长度的另一列。
回答by Shakti Singh
回答by Kangkan
You can not store integer with leading zeroes. One way is keeping it in varchar as well as int columns. In this case, you need two columns, one value and the other intValue and while sorting, you can use the intValue for sorting. This is easy to implement.
您不能存储带有前导零的整数。一种方法是将它保存在 varchar 和 int 列中。在这种情况下，您需要两列，一个值和另一个 intValue，在排序时，您可以使用 intValue 进行排序。这很容易实现。
Select Value from Table order by intValue;
The other option can be using two columns, one valu and othe NumOfZero and use these for the desired results. This is complex, but might be light on the DB.
另一种选择可以使用两列，一个值和另一个 NumOfZero，并将它们用于所需的结果。这很复杂，但对 DB 来说可能很简单。
回答by Nour
Change the structure of the field and make the Attributes UNSIGNED_ZEROFILLto keep the zeros.
更改字段的结构并使属性UNSIGNED_ZEROFILL保持零。
But you should be careful to the Length of the field, because it's gonna return all the rest numbers to zeros so put the length of your field
但是您应该小心字段的长度，因为它会将所有其余数字返回为零，因此请输入您的字段的长度
