Answer a question

I want to store Zip Code (within United States) in MySQL database. Saving space is a priority. which is better option using VARCHAR - limited to maximum length of 6 digit or using INT or using MEDIUM Int . The Zip code will not be used for any calculation. The Zip code will be used to insert (Once), Updated (if required) and Retrieved - Once (but it can be more than once) .

Which is better option to use here VARCHAR or INT or MEDIUM IN MYSQL ? Please suggest anything else ?

Answers

There are a few problems with storing a zip code as a numeric value.

1. Zip Codes have extensions, meaning they can be 12345-6789. You cannot store a dash in a numeric datatype.
2. There are many zip codes that start with a zero, if you store it as an int you are going to lose the leading zero.
3. You do not add/subtract, etc zip codes or use numeric functions with them.

I would store a zip code as a varchar(5) or varchar(10).

As a side note, I am not sure why you would select varchar(6), do you have a reason for selecting an unusual length when standard zip codes are 5 or 10 with the extension?