在MySQL数据库中将整数的前导零存储为INTEGER
在MySQL数据库中将整数的前导零存储为INTEGER
我需要在MySQL中使用整数字段存储数字并保留前导零。由于我当前的字段是Bigint(16)，而且数字的前导零数量可能会变化，所以我无法使用zerofill选项。例如：0001-0005，然后008-010可能需要被存储。我对数字的唯一性不关心（它们不被用作ID或其他任何东西），但我仍然希望它们被存储为INTS类型。
使用CHAR/VARCHAR然后在PHP中将值转换为整数的问题是，通过查询对结果进行排序会导致按字母数字顺序排序，例如：SORT BY number ASC将产生以下结果：
001 002 003 1 100 101 102 2
显然不是按数字顺序排序，而是按字母数字顺序排序，这是不希望的。
希望有一些巧妙的解决方法 🙂
问题的原因是MySQL数据库默认情况下不会在存储整数时保留前导零。解决方法是使用LPAD()函数将数字左侧填充零以显示前导零。
为了实现这个解决方法，首先需要将数字存储为整数类型。然后，使用LPAD()函数来在数字左侧填充零。例如，使用SELECT语句和LPAD()函数可以将数字14填充为7位长的字符串，其中填充字符为零。
如果填充零的字符数是可变的，可以在表中添加另一列来存储填充零的长度。
然而，需要注意的是，当对数值类型使用LPAD()函数时，可能会对性能产生影响。这是因为LPAD()函数是一个字符串函数。特别是在查询大量记录并且LPAD()函数用于格式化数值类型输出的情况下，可能会影响性能。
通过将数字存储为整数类型，并使用LPAD()函数在需要时填充前导零，可以解决MySQL数据库中存储整数时丢失前导零的问题。然而，需要注意LPAD()函数可能对性能产生影响的情况。
存储MySQL数据库中整数的前导零作为整数类型的问题出现的原因是为了进行排序操作。在MySQL中，如果将整数存储为CHAR或VARCHAR类型的字符串，那么在排序时字符串类型的列会按照字符串的规则进行排序，而不是按照整数的规则进行排序。这意味着带有前导零的整数可能无法正确地排序。
为了解决这个问题，可以使用CAST函数将字符串列转换为有符号整数类型，以便进行排序。具体的解决方法是使用以下语句进行排序操作：
ORDER BY CAST(`col_name` AS SIGNED) DESC
此外，也可以使用将字符串列与0相加的方式来进行排序操作，例如：
ORDER BY col_name + 0 DESC
这样可以将字符串列按照整数的规则进行排序，从而解决了存储整数的前导零问题。