MySQL - What exactly does smallint(2) permit? [duplicate]

Question

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?

Thanks!

Answer 1

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.

Answer 2

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 |
+------+------+

Answer 3

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.