Reputation: 583
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 ?
Upvotes: 16
Views: 71550
Reputation: 945
Used to live in the Netherlands and know that also characters are possible. So if you have user in different countries, I'd advise You to set it as a varchar(10).
2525 CA, Netherlands <- This is showing on the exact point, where I used to live. They have some kind of a coordinate system with their zip codes, which shows the exact position in combination with the letters at the end.
Upvotes: 0
Reputation: 3006
I would suggest, use VARCHAR data type because in some countries zip codes are used as alphanumeric and in other places as an integer. So we cannot use an integer for global use. Also, zip code may start with zero like 001101
so in this case if we take data type integer then leading zero will be lost so we cannot pass actual zip code.
Upvotes: 1
Reputation: 247880
There are a few problems with storing a zip code as a numeric value.
12345-6789
. You cannot store a dash in a numeric datatype.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?
Upvotes: 36
Reputation: 1370
Zip codes are always 5 characters, hence you would need a CHAR datatype, rather than VARCHAR.
Your options are therefore
CHAR(5)
MEDIUMINT (5) UNSIGNED ZEROFILL
The first takes 5 bytes per zip code.
The second takes only 3 bytes per zip code. The ZEROFILL option is necessary for zip codes with leading zeros.
So, if space is your priority, use the MEDIUMINT.
Upvotes: 6
Reputation: 8846
I usually use MEDIUMINT(5) ZEROFILL
for 5 digit zip codes. This preserves any leading 0
s and it only uses 3 bytes where a VARCHAR(5)
would use 6. This assumes that you don't need the extended zip codes, which have a dash and 4 extra numbers. If you were to decide to use a textual type, I would use CHAR(5)
instead of VARCHAR(5)
since it is better if the data is always 5 characters long.
Upvotes: 9