Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

This question already has an answer here:

Sorry for the really simple question, but I can't figure it out.

When I set up my schema, I set up my primary key to use a field of type smallint(2). The thought behind it was that the field should only ever consist of 2-digit numbers, 1-99.

We've broken the upper limit of that and now I'm seeing 3-digit numbers being added to this field. I'm now speculating that smallint(2) and varchar(2) do not function the same, but since I don't understand what's going on with this table I'm getting a little concerned.

What values does a field such as smallint(2) allow?


share|improve this question

marked as duplicate by ypercubeᵀᴹ, Mark Storey-Smith, Paul White, Marian, RolandoMySQLDBA Mar 26 '13 at 1:00

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

It allows the same values as any other SMALLINT: – Michael - sqlbot Mar 23 '13 at 5:18… - same question, other way around (tinyint(3)). – Mat Mar 23 '13 at 6:35
I'm not entirely convinced this should be closed as a duplicate. The docs for smallint don't offer any clue about what smalling(2) might mean, and there's only one relatively similar question in my sidebar. – Mike Sherrill 'Cat Recall' Mar 23 '13 at 15:09
up vote 2 down vote accepted

As explained in the MySQL documentation here, the (n) suffix for numeric types is the (default) display width for numbers in that column. In your case the (2) sets the default display width to 2 digits. It does not limit numbers in that column to two digits (as you have discovered), it's just a formatting hint for display purposes.

share|improve this answer

It's used just for display AND when zerofill is enabled, it will basically add left zeros if you data has less digits than specified. in your case, it will add 0's if your number has less then 2 characters length.

mysql> DESCRIBE zero_fill;
| Field | Type                         | Null | Key | Default | Extra |
| a     | tinyint(2)                   | YES  |     | NULL    |       |
| b     | tinyint(2) unsigned zerofill | YES  |     | NULL    |       |
mysql> INSERT INTO zero_fill SET a=1, b=1;
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO zero_fill SET a=11, b=11;
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO zero_fill SET a=111, b=111;
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM zero_fill;
| a    | b    |
|    1 |  001 |
|   11 |   11 |
|  111 |  111 |
share|improve this answer

As others have pointed out, smallint(2) doesn't do what you think it does.

In MySQL, the best way to guarantee two-digit numbers is to build a table, insert all the valid two-digit numbers, and set a foreign key reference to it.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.