MySQL - 如何用“0"填充邮政编码?

2021-11-20 00:00:00 sql database mysql database-design

在我的 MySQL InnoDB 数据库中,我有要清理的脏邮政编码数据.

In my MySQL InnoDB database, I have dirty zip code data that I want to clean up.

干净的邮政编码数据是当我拥有邮政编码的所有 5 位数字时(例如90210").

The clean zip code data is when I have all 5 digits for a zip code (e.g. "90210").

但出于某种原因,我在我的数据库中注意到,对于以0"开头的邮政编码,0 已被删除.

But for some reason, I noticed in my database that for zipcodes that start with a "0", the 0 has been dropped.

因此,邮政编码为00544"的Holtsville, New York"在我的数据库中存储为544"

So "Holtsville, New York" with zipcode "00544" is stored in my database as "544"

"Dedham, MA" 邮政编码为 "02026" 在我的数据库中存储为 "2026".

"Dedham, MA" with zipcode "02026" is stored in my database as "2026".

我可以运行什么 SQL 将前面的0"填充到任何长度不是 5 位的邮政编码?意思是,如果邮政编码的长度是 3 位数字,则在前面填充00".如果邮政编码的长度为 4 位数字,则前面仅填充0".

What SQL can I run to front pad "0" to any zipcode that is not 5 digits in length? Meaning, if the zipcode is 3 digits in length, front pad "00". If the zipcode is 4 digits in length, front pad just "0".

更新:

我只是将邮政编码更改为数据类型 VARCHAR(5)

I just changed the zipcode to be datatype VARCHAR(5)

推荐答案

将您的邮政编码存储为 CHAR(5) 而不是数字类型,或者在您的应用程序从数据库加载它时用零填充它.一种使用 sprintf() 的 PHP 方法:

Store your zipcodes as CHAR(5) instead of a numeric type, or have your application pad it with zeroes when you load it from the DB. A way to do it with PHP using sprintf():

echo sprintf("%05d", 205); // prints 00205
echo sprintf("%05d", 1492); // prints 01492

或者你可以用 LPAD():

Or you could have MySQL pad it for you with LPAD():

SELECT LPAD(zip, 5, '0') as zipcode FROM table;

这是一种更新和填充所有行的方法:

Here's a way to update and pad all rows:

ALTER TABLE `table` CHANGE `zip` `zip` CHAR(5); #changes type
UPDATE table SET `zip`=LPAD(`zip`, 5, '0'); #pads everything

相关文章