MySQL 带前导零的 LPAD
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17612920/
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
LPAD with leading zero
提问by jaczes
I have table with invoice numbers. Guidelines say that numbers should have 6 or more digits. First of all tried to do:
我有发票号码表。指南说数字应该有 6 位或更多位。首先尝试做：
UPDATE t1 SET NUMER=CONCAT('00000',NUMER) WHERE LENGTH(NUMER)=1;
UPDATE t1 SET NUMER=CONCAT('0000',NUMER) WHERE LENGTH(NUMER)=2;
UPDATE t1 SET NUMER=CONCAT('000',NUMER) WHERE LENGTH(NUMER)=3;
UPDATE t1 SET NUMER=CONCAT('00',NUMER) WHERE LENGTH(NUMER)=4;
UPDATE t1 SET NUMER=CONCAT('0',NUMER) WHERE LENGTH(NUMER)=5;
but that isn't efficient, and even pretty. I tried LPADfunction, but then came problem because function :
但这效率不高，甚至很漂亮。我尝试了LPAD函数，但后来出现了问题，因为函数：
UPDATE t1 SET NUMER=LPAD(NUMER,6,'0') WHERE CHAR_LENGTH(NUMER)<=6 ;
returns ZERO rows affected. Also googled and they say that putting zero into quotes will solve problem, but didn't, any help ? It's daily import.
返回受影响的零行。也用谷歌搜索，他们说在引号中加上零可以解决问题，但没有，有什么帮助吗？这是日常进口。
EDIT: Column NUMER is INT(19) and contain already data like :
编辑： NUMER 列是 INT(19) 并且已经包含如下数据：
NUMER
----------
1203
12303
123403
1234503
...
(it's filled with data with different length from 3 to 7 digits by now)
（现在已经填充了3到7位不同长度的数据）
回答by Gustav Bertram
I think you should consider that the guidelines you read apply to how an invoice should be displayed, and not how it should be storedin the database.
我认为您应该考虑您阅读的指南适用于发票的显示方式，而不是发票的存储方式。
When a number is stored as an INT, it's a pure number. If you add zeros in front and store it again, it is still the same number.
当一个数字以 INT 形式存储时，它就是一个纯数字。如果在前面加零并再次存储，它仍然是相同的数字。
You could select the NUMER field as follows, or create a view for that table:
您可以按如下方式选择 NUMER 字段，或为该表创建一个视图：
SELECT LPAD(NUMER,6,'0') AS NUMER
FROM ...
Or, rather than changing the data when you select it from the database, consider padding the number with zeros when you display it, and only when you display it.
或者，不要在从数据库中选择数据时更改数据，而是考虑在显示时用零填充数字，并且仅在显示时使用。
I think your requirement for historical data to stay the same is a moot point. Even for historical data, an invoice numbered 001203 is the same as an invoice numbered 1203.
我认为您对历史数据保持不变的要求是有争议的。即使对于历史数据，编号为 001203 的发票也与编号为 1203 的发票相同。
However, if you absolutely must do it the way you describe, then converting to a VARCHAR field may work. Converted historical data can be stored as-is, and any new entries could be padded to the required number of zeros. But I do not recommend that.
但是，如果您绝对必须按照您描述的方式进行操作，那么转换为 VARCHAR 字段可能会起作用。转换后的历史数据可以按原样存储，任何新条目都可以填充到所需数量的零。但我不建议这样做。
回答by Erik Ekman
UPDATE t1 SET NUMER=LPAD(NUMER,6,'0') WHERE CHAR_LENGTH(NUMER)<=6 ;will not do what you expect since the NUMERfield is an int. It will create the string '001234' from the int 1234 and then cast it back into 1234 - that is why there is no change.
UPDATE t1 SET NUMER=LPAD(NUMER,6,'0') WHERE CHAR_LENGTH(NUMER)<=6 ;不会做您期望的事情，因为该NUMER字段是int. 它将从 int 1234 创建字符串 '001234' ，然后将其转换回 1234 - 这就是没有更改的原因。
Change NUMERto type int(6) zerofilland MySQL will pad it for you each time you read it.
更改NUMER为类型int(6) zerofill，每次阅读时，MySQL 都会为您填充它。
If you really want zeros stored in the database, you have to change the type to CHAR/VARCHAR, then your LPADupdate statement will work.
如果您真的希望将零存储在数据库中，则必须将类型更改为CHAR/ VARCHAR，然后您的LPAD更新语句将起作用。
回答by bencoder
The field in the table is an int column so it just stores a number. There's no way to pad out the data in the table. 1 == 001 == 000000000001. This is the same number.
表中的字段是一个 int 列，因此它只存储一个数字。没有办法填充表中的数据。1 == 001 == 000000000001。这是同一个数字。
You should do the padding at the application level (the system that pulls the data out of the table). What happens when the order number goes above 999999? You would then have to update all the data in the table to add an extra 0. This kind of thing should not be done at the database level.
您应该在应用程序级别（从表中提取数据的系统）进行填充。当订单号超过 999999 时会发生什么？然后，您必须更新表中的所有数据以添加额外的 0。这种事情不应该在数据库级别进行。
You could also select the data out with an LPAD:
您还可以使用 LPAD 选择数据：
SELECT LPAD(NUMER,6,'0'), [other_columns] FROM t1;
Alternative, As CBroe mentioned you could change the datatype to be INT(6) ZEROFILLso that it displays correctly but this will have to be modified if it goes above 999999 as mentioned above..
替代方案，正如 CBroe 提到的，您可以更改数据类型以INT(6) ZEROFILL使其正确显示，但是如果如上所述超过 999999，则必须对其进行修改。
