MySQL - 如何用“0”填充邮政编码？

MySQL - how to front pad zip code with "0"?

提问by TeddyR

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

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

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

干净的邮政编码数据是当我拥有邮政编码的所有 5 位数字时（例如“90210”）。

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

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

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

因此，“纽约Holtsville的”与邮政编码“ 00544”存储在我的数据库“ 544”

and

和

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

邮政编码为“ ”的“ Dedham, MA”02026作为“ 2026”存储在我的数据库中。

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".

我可以运行什么 SQL 到前面填充“0”到任何长度不是 5 位的邮政编码？意思是，如果邮政编码的长度为 3 位数，则在前面填充“00”。如果邮政编码的长度为 4 位，则前面仅填充“0”。

UPDATE:

更新：

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

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

回答by quantumSoup

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():

将您的邮政编码存储为 CHAR(5) 而不是数字类型，或者在您从数据库加载它时让您的应用程序用零填充它。一种使用 PHP 执行此操作的方法sprintf()：

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

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

或者你可以让 MySQL 为你填充它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

回答by Anax

You need to decide the length of the zip code (which I believe should be 5 characters long). Then you need to tell MySQL to zero-fill the numbers.

您需要确定邮政编码的长度（我认为应该是 5 个字符长）。然后你需要告诉 MySQL 对数字进行零填充。

Let's suppose your table is called mytableand the field in question is zipcode, type smallint. You need to issue the following query:

假设您的表被调用，mytable并且有问题的字段是zipcode, type smallint。您需要发出以下查询：

ALTER TABLE mytable CHANGE `zipcode` `zipcode`
    MEDIUMINT( 5 ) UNSIGNED ZEROFILL NOT NULL;

The advantage of this method is that it leaves your data intact, there's no need to use triggers during data insertion / updates, there's no need to use functions when you SELECTthe data and that you can always remove the extra zeros or increase the field length should you change your mind.

这种方法的优点是它使您的数据保持完整，在数据插入/更新期间无需使用触发器，在您SELECT输入数据时无需使用函数，并且您始终可以删除多余的零或增加字段长度你改变主意了。

回答by Craig Trader

Ok, so you've switched the column from Number to VARCHAR(5). Now you need to update the zipcode field to be left-padded. The SQL to do that would be:

好的，您已将列从 Number 切换为 VARCHAR(5)。现在您需要更新要左填充的邮政编码字段。执行此操作的 SQL 将是：

UPDATE MyTable
SET ZipCode = LPAD( ZipCode, 5, '0' );

This will pad all values in the ZipCode column to 5 characters, adding '0's on the left.

这会将 ZipCode 列中的所有值填充为 5 个字符，并在左侧添加“0”。

Of course, now that you've got all of your old data fixed, you need to make sure that your any new data is also zero-padded. There are several schools of thought on the correct way to do that:

当然，既然您已经修复了所有旧数据，您需要确保所有新数据也是零填充的。有几种关于正确方法的思想流派：

• Handle it in the application's business logic. Advantages: database-independent solution, doesn't involve learning more about the database. Disadvantages: needs to be handled everywhere that writes to the database, in all applications.

• Handle it with a stored procedure. Advantages: Stored procedures enforce business rules for all clients. Disadvantages: Stored procedures are more complicated than simple INSERT/UPDATE statements, and not as portable across databases. A bare INSERT/UPDATE can still insert non-zero-padded data.

• Handle it with a trigger. Advantages: Will work for Stored Procedures and bare INSERT/UPDATE statements. Disadvantages: Least portable solution. Slowest solution. Triggers can be hard to get right.

• 在应用程序的业务逻辑中处理它。优点：独立于数据库的解决方案，不涉及对数据库的更多了解。缺点：需要在所有应用程序中写入数据库的任何地方进行处理。

• 使用存储过程处理它。优点：存储过程为所有客户端强制执行业务规则。缺点：存储过程比简单的 INSERT/UPDATE 语句更复杂，并且不能跨数据库移植。一个空的 INSERT/UPDATE 仍然可以插入非零填充的数据。

• 用触发器处理它。优点：适用于存储过程和裸 INSERT/UPDATE 语句。缺点：最不便携的解决方案。最慢的解决方案。触发器可能很难正确。

In this case, I would handle it at the application level (if at all), and not the database level. After all, not all countries use a 5-digit Zipcode (not even the US -- our zipcodes are actually Zip+4+2: nnnnn-nnnn-nn) and some allow letters as well as digits. Better NOT to try and force a data format and to accept the occasional data error, than to prevent someone from entering the correct value, even though it's format isn't quite what you expected.

在这种情况下，我会在应用程序级别（如果有的话）而不是数据库级别处理它。毕竟，并非所有国家/地区都使用 5 位邮政编码（甚至美国也不例外——我们的邮政编码实际上是 Zip+4+2：nnnnn-nnnn-nn），并且有些国家/地区允许使用字母和数字。最好不要尝试强制使用数据格式并接受偶尔出现的数据错误，而不是阻止某人输入正确的值，即使它的格式并不完全符合您的预期。

回答by lemming622

I know this is well after the OP. One way you can go with that keeps the table storing the zipcode data as an unsigned INT but displayed with zeros is as follows.

我知道这是在 OP 之后。您可以采用的一种方法是使表格将邮政编码数据存储为无符号 INT 但以零显示，如下所示。

select LPAD(cast(zipcode_int as char), 5, '0') as zipcode from table;

select LPAD(cast(zipcode_int as char), 5, '0') as zipcode from table;

While this preserves the original data as INT and can save some space in storage you will be having the server perform the INT to CHAR conversion for you. This can be thrown into a view and the person who needs this data can be directed there vs the table itself.

虽然这将原始数据保留为 INT 并可以节省一些存储空间，但您将让服务器为您执行 INT 到 CHAR 的转换。这可以被扔到一个视图中，需要这个数据的人可以被引导到那里而不是表本身。

回答by Saurabh Chandra Patel

you should use UNSIGNED ZEROFILLin your table structure.

你应该UNSIGNED ZEROFILL在你的表结构中使用。

回答by Peter

It would still make sense to create your zip code field as a zerofilled unsigned integer field.

将您的邮政编码字段创建为一个零填充的无符号整数字段仍然是有意义的。

CREATE TABLE xxx ( zipcode INT(5) ZEROFILL UNSIGNED, ... )

CREATE TABLE xxx ( zipcode INT(5) ZEROFILL UNSIGNED, ... )

That way mysql takes care of the padding for you.

这样 mysql 会为您处理填充。

回答by Martin Sansone - MiOEE

CHAR(5)

or

或者

MEDIUMINT (5) UNSIGNED ZEROFILL

The first takes 5 bytes per zip code.

第一个每个邮政编码需要 5 个字节。

The second takes only 3 bytes per zip code. The ZEROFILL option is necessary for zip codes with leading zeros.

第二个每个邮政编码只需要 3 个字节。ZEROFILL 选项对于带前导零的邮政编码是必需的。

回答by Agent Mahone

LPAD works with VARCHAR2 as it does not put spaces for left over bytes. LPAD changes leftover/null bytes to zeros on LHS SO datatype should be VARCHAR2

LPAD 与 VARCHAR2 一起使用，因为它不会为剩余字节放置空格。LPAD 将 LHS 上的剩余/空字节更改为零 SO 数据类型应为 VARCHAR2