MySQL将前导数字添加到列中的现有ID
我有一个名为 country_id 的 mysql 数据库列，例如:
I got a mysql database column named country_id, like:
country_id
----------
1
2
59
435
2714
我现在要完成的是为每个 ID 添加前导零，因此结果将是:
What I'm trying to accomplish now is to add leading zero's to each ID, so the result would be:
country_id
----------
0001
0002
0059
0435
2714
每个 ID 最多应包含 4 位数字.就是这样.
Each ID should have max 4 digits. That's it.
我可以在 PHPmyAdmin 中使用任何 SQL 语句以上述方式更新这个 country_id 列吗?
Is there any SQL statement I could use in PHPmyAdmin to update this country_id column in the way described above?
最好的问候！
推荐答案
在列上声明ZEROFILL:
mysql> create table foobar (nr integer(4) ZEROFILL);
Query OK, 0 rows affected (0.31 sec)
mysql> INSERT INTO foobar VALUES (1),(12),(123),(1234),(12345);
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM foobar;
| 0001
| 0012
| 0123
| 1234
| 12345
相关文章