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.
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?
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With