在 MySQL 数据库中将整数的前导零存储为 INTEGER [英] Storing leading zeros of integers in MySQL database as INTEGER

问题描述

我需要 MySQL 将数字存储在整数字段中并保持前导零.我不能使用 zerofill 选项，因为我当前的字段是 Bigint(16) 并且数字可以在前导零的数量上有所不同.IE:0001 - 0005，然后可能需要存储 008 - 010.我不关心数字的唯一性(这些不被用作 ID 或任何东西)，但我仍然需要将它们最好存储为 INTS.

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.

在 PHP 中使用 CHAR/VARCHAR 然后将值类型转换为整数的问题意味着通过查询对结果进行排序会导致字母数字排序，IE: SORT BY number ASC 会产生

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

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 :)

推荐答案

将数字保存为整数.

然后使用函数LPAD() 显示用零填充的数字(左):

Then use function LPAD() to show the numbers (left) padded with zeros:

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.

这篇关于在 MySQL 数据库中将整数的前导零存储为 INTEGER的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！