在MySQL中将某些列的值前加零

Question

我收到了一个CSV文件，其中一个字段是8位数，有些以0开头。该字段已经以数字形式发送给我。因此，我现在丢失了一些前导零。

我已经将该字段转换为varchar，现在需要执行以下操作：

现在我的情况是：

12345678
1234567

我需要这个:

12345678
01234567

- mpg

Answer 1

将字段改回数字类型并使用ZEROFILL以保留零

或者

使用LPAD()

SELECT LPAD('1234567', 8, '0');

- John Conde

---

请注意ZEROFILL。"截至MySQL 8.0.17版本，对于数值数据类型，已不再推荐使用ZEROFILL属性，整数数据类型的显示宽度属性也不再推荐使用。在未来的MySQL版本中，您应该预计将删除对整数数据类型的ZEROFILL和显示宽度的支持。" - https://dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html - Dexter

Answer 2

可能：

select lpad(column, 8, 0) from table;

---

根据下面mylesg的问题进行编辑:

好的，修改查询看起来可行 - 但是我该如何使其永久保留（即在表中进行更改）？我尝试使用UPDATE而非SELECT

我假设您使用了类似以下的查询：

UPDATE table SET columnName=lpad(nums,8,0);

如果这是成功的，但表中的值仍然没有前导零，那么我建议您可能将列设置为数字类型？如果是这种情况，那么您需要更改表格，使列为文本/ varchar（）类型以保留前导零：

首先：

ALTER TABLE `table` CHANGE `numberColumn` `numberColumn` CHAR(8);

其次，运行更新：

UPDATE table SET `numberColumn`=LPAD(`numberColum`, 8, '0');

这样做可以保留前导零；但缺点是该列不再是严格的数值类型，因此您可能需要执行更严格的验证（根据您的用例）以确保在该列中输入的内容都是数字。

参考文献：

• lpad()函数。

- David Thomas

---

好的，似乎已经在查询中进行了更改 - 但是我如何使其永久地保留（更改）在表中呢？我尝试使用UPDATE而不是SELECT。 - mpg

Answer 3

当我将电话号码数据从Excel导入到MySQL数据库时，遇到了类似的问题。因此，我有一个简单的技巧，无需识别电话号码的长度（因为我的数据中电话号码的长度各不相同）：

UPDATE table SET phone_num = concat('0', phone_num) 

我只是在phone_num前面加了一个0。

- Sitti Munirah Abdul Razak

---

不行，因为根据设置和字段类型（char），你可能会遇到错误码1406：“数据长度超过列的最大值…” - Cyril Jacquart

Answer 4

使用LPAD()的先前答案是最佳的。但是，如果您想要进行特殊或高级处理，这里有一种方法可以更好地控制填充。此外，还可以使用其他结构来实现相同的效果。

UPDATE
    mytable
SET
    mycolumn = CONCAT(
        REPEAT(
            "0",
            8 - LENGTH(mycolumn)
        ),
        mycolumn
    )
WHERE
    LENGTH(mycolumn) < 8;

- Dan

Answer 5

请记住，如果您的值具有比LPAD长度更多的字符，MySQL将把它截断到该长度，因此您应该添加额外的IF并进行长度检查。

IF(LENGTH(column)<8,LPAD(column,8,0),column)

- zomers