将前导零添加到 MySQL 列中的某些值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11165104/
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
Adding a leading zero to some values in column in MySQL
提问by mpg
I have a CSV file sent to me in CSV. The field of interest is 8 digits. Some of those started with a 0. The field was sent numeric. So, I now have dropped some leading zeros.
我有一个以 CSV 格式发送给我的 CSV 文件。感兴趣的字段是 8 位数字。其中一些以 0 开头。该字段以数字形式发送。所以，我现在去掉了一些前导零。
I already converted the field to varchar. I now need to do this:
我已经将该字段转换为 varchar。我现在需要这样做：
I have this now:
我现在有这个：
12345678
1234567
I need to have this:
我需要这个：
12345678
01234567
回答by John Conde
回答by David says reinstate Monica
Possibly:
可能：
select lpad(column, 8, 0) from table;
Editedin response to question from mylesg, in comments below:
编辑响应问题从mylesg，在下面的评论：
ok, seems to make the change on the query- but how do I make it stick (change it) permanently in the table? I tried an UPDATE instead of SELECT
好的，似乎对查询进行了更改 - 但我如何使其永久粘贴（更改）在表中？我尝试了 UPDATE 而不是 SELECT
I'm assuming that you used a query similar to:
我假设您使用了类似于以下内容的查询：
UPDATE table SET columnName=lpad(nums,8,0);
If that was successful, but the table's values are still without leading-zeroes, then I'd suggest you probably set the column as a numeric type? If that's the case then you'd need to alter the table so that the column is of a text/varchar() type in order to preserve the leading zeroes:
如果这成功了，但表的值仍然没有前导零，那么我建议您可能将该列设置为数字类型？如果是这种情况，那么您需要更改表，以便该列是 text/varchar() 类型以保留前导零：
First:
第一的：
ALTER TABLE `table` CHANGE `numberColumn` `numberColumn` CHAR(8);
Second, run the update:
其次，运行更新：
UPDATE table SET `numberColumn`=LPAD(`numberColum`, 8, '0');
This should, then, preserve the leading-zeroes; the down-side is that the column is no longer strictly of a numeric type; so you may have to enforce more strict validation (depending on your use-case) to ensure that non-numerals aren't entered into that column.
然后，这应该保留前导零；不利的一面是该列不再是严格的数字类型；因此您可能必须执行更严格的验证（取决于您的用例）以确保非数字不会输入到该列中。
References:
参考：
回答by Sitti Munirah Abdul Razak
I had similar problem when importing phone number data from excel to mysql database. So a simple trick without the need to identify the length of the phone number (because the length of the phone numbers varied in my data):
将电话号码数据从 excel 导入 mysql 数据库时，我遇到了类似的问题。所以一个简单的技巧，无需识别电话号码的长度（因为电话号码的长度在我的数据中有所不同）：
UPDATE table SET phone_num = concat('0', phone_num)
I just concated 0 in front of the phone_num.
我只是在phone_num.
