CODEWITHSUNDEEP
sqlmysqldatabase-designdata-modelingzipcode
gsueagle2008
gsueagle2008

Reputation: 4663

Mysql Datatype for US Zip (Postal Codes)

I am writing a web application, that is US specific, so the format that other countries use for postal codes are not important. I have a list of us zip codes that i am trying to load into a database table that includes the

the zip code is the primary key as it is what i will be querying against. i started using a medium int 5 but that truncates the zip codes that have leading zeros.

i considered using a char5 but am concerned about the performance hit of indexing against a char variable.

so my question is what is the best mysql datatype to store zip codes as?

Note: i have seen it in several other questions related to zip codes. I am only interested in US 5 digit zip codes. So there is no need to take other countries postal code formats into consideration.

Upvotes: 17

Views: 31369

Answers (3)

Erich
Erich

Reputation: 3972

char(5) is the correct way to go. String indexing is quite fast, particularly when it is such a small data set.

You are correct in that you should never use an integer for a zip code, since it isn't truly numeric data.

Edit to add: Check out this for good reasons why you don't use numbers for non-numerically important data: Is it a good idea to use an integer column for storing US ZIP codes in a database?

Upvotes: 30

David
David

Reputation: 900

If he makes it Char(6), then he can handle Canadian postal codes as well.

When you consider that there is a maximum of 100,000 5-digit Zip Code and how little space it would take up even if you made the entire table memory-resident, there's no reason not to.

Upvotes: 1

KM.
KM.

Reputation: 103587

go with your medium INT(5) ZEROFILL, it should add the leading zeros for you. No need to impact the index and performance on a formatting issue.

Upvotes: 2

Related Questions