0

I am designing a table which stores 5 digit US zipcodes without extensions. It looks like the leading contenders are CHAR(5) and MEDIUMINT (5) UNSIGNED ZEROFILL. Reference:

I plan on going with CHAR(5) for reasons described by Is it a good idea to use an integer column for storing US ZIP codes in a database?. EDIT-As such, using ZEROFILL is not an answer, so please do not ask me to use ZEROFILL or close this question and refer to a previous answer which says to use ZEROFILL.

Next, I want to ensure that (5) characters are always stored, and if less are provided, then either the query produces an error, or it is left padded with zeros (and not right-padded with spaces). My intent is to prevent zipcode "3101" from ever existing in the database, and ensure that it must be "03101". Note that I am not asking how to make a PHP application left pad the zipcodes with zeros.

Community
  • 1
  • 1
user1032531
  • 24,767
  • 68
  • 217
  • 387
  • Why you prefer char over mediumint while it does, what you request, faster and uses less storage ? – Utku Yıldırım Sep 26 '13 at 13:30
  • @JohnConde. Would this not require an integer field? Most disagree with using one. – user1032531 Sep 26 '13 at 13:30
  • [This question](http://stackoverflow.com/questions/5191494/padding-the-beginning-of-a-mysql-int-field-with-zeroes) that you linked to provides the answer. Use [ZEROFILL](http://hashmysql.org/wiki/Zerofill). – John Conde Sep 26 '13 at 13:31
  • 1
    @UtkuYıldırım. Reasons why I prefer char() over int: http://stackoverflow.com/questions/893454/is-it-a-good-idea-to-use-an-integer-column-for-storing-us-zip-codes-in-a-databas – user1032531 Sep 26 '13 at 13:32
  • Another duplicate - http://stackoverflow.com/questions/3200754/mysql-how-to-front-pad-zip-code-with-0 – Jason McCreary Sep 26 '13 at 13:43
  • @JasonMcCreary. This post does not answer the question. It tells how to make a PHP application left pad which I specifically said I am not trying to do. – user1032531 Sep 26 '13 at 13:46
  • No, if you read, it also tells you to use `LPAD(zip, 5, '0')` - which is your only option in MySQL if you want to use a `CHAR()` field. – Jason McCreary Sep 26 '13 at 14:30
  • @JasonMcCreary. I read it and `LPAD()` is for selecting data, and does not answer my question. I believe there are other options such as using a trigger, however, I do not know how good they are. – user1032531 Sep 26 '13 at 16:07
  • `LPAD()` is a MySQL function and not restricted to the `SELECT` query. – Jason McCreary Sep 26 '13 at 17:02
  • @JasonMcCreary Yes, it is a MySQL function, but it does absolutely nothing to "Forcing MySQL CHAR() column to require all digits or auto left pad with zeros" – user1032531 Sep 27 '13 at 00:23
  • @JasonMcCreary Let me restate that... It doesn't force MySQL CHAR() columns to require all digits at the database structure level. I suppose if every insert/update used it (i.e. `INSERT INTO myTable(id) VALUES(LPAD("123",5,0))`), it would autopad the inserts, but I feel this is poor database design to rely on the queries and not the database structure. I think the right answer is "it can't be done", or "use triggers", or something else which I haven't thought of. – user1032531 Sep 27 '13 at 00:33

3 Answers3

2

You can use the LPAD function to left pad a value with zeroes.

LPAD (zip_code, 5, '0')
Tom
  • 6,593
  • 3
  • 21
  • 42
0

If you add the zerofill attribute to the field and set the lenth to 5 it will pad left with a zero as stated in the link you provided.

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `test` int(5) unsigned zerofill DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Just be sure to validate the zip before entering it.

Also sometimes zipcodes have a 4 digit suffix. Might want to make it a char(10) if you expect you might have some zipcodes in this format. Or a zip_suffix column.

todd
  • 322
  • 1
  • 4
  • 9
0

First of all, you'd better use a SMALLINT than a CHAR if you plan to save digits only. INT and its derivatives are made for that : - ti will take less space - and it will go fatser to read/write (even if the difference is tiny)

As to be sure your that you always have 5 characters, you have to use ZEROFILL. You should have a look at this thread : What is the benefit of zerofill in MySQL?

Community
  • 1
  • 1
Buzut
  • 4,875
  • 4
  • 47
  • 54