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

相关文章