CODEWITHSUNDEEP
mysqlintvarchar
Tapan Banker
Tapan Banker

Reputation: 583

What is the best column type for a United States ZIP code?

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

Answers (5)

Sercan Samet Savran
Sercan Samet Savran

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

Lawakush Kurmi
Lawakush Kurmi

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

Taryn
Taryn

Reputation: 247880

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?

Upvotes: 36

fisharebest
fisharebest

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

G-Nugget
G-Nugget

Reputation: 8846

I usually use MEDIUMINT(5) ZEROFILL for 5 digit zip codes. This preserves any leading 0s 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

Related Questions