16

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 ?

Pekka
  • 442,112
  • 142
  • 972
  • 1,088
Tapan Banker
  • 587
  • 1
  • 7
  • 10
  • possible duplicate of [Is it a good idea to use an integer column for storing US ZIP codes in a database?](http://stackoverflow.com/questions/893454/is-it-a-good-idea-to-use-an-integer-column-for-storing-us-zip-codes-in-a-databas) – Taryn Apr 05 '13 at 21:25

5 Answers5

36

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?

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • I like the answer, but how do you deal with some people adding the regular 5 digits, while others give the full 10 digits? Do you settle for `varchar(10)`, then just add `-0000`? – Mitchell Apr 17 '22 at 04:33
9

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.

G-Nugget
  • 8,666
  • 1
  • 24
  • 31
6

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.

fisharebest
  • 1,300
  • 10
  • 16
  • 1
    US zip codes can contain 9 digits and one hyphen, not just 5 digits. If you assume the length of zip codes, you must be prepared to change the column type in the future if zip codes change. – Rory O'Kane Feb 17 '19 at 19:25
1

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.

Salomon Zhang
  • 1,553
  • 3
  • 23
  • 41
Lawakush Kurmi
  • 2,726
  • 1
  • 16
  • 29
0

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.

Sercan Samet Savran
  • 755
  • 1
  • 9
  • 20