News

Welcome to End Point’s blog

Ongoing observations by End Point people

MySQL Integer Size Attributes

MySQL has those curious size attributes you can apply to integer data types. For example, when creating a table, you might see:

mysql> CREATE TABLE foo (
    -> field_ti tinyint(1),
    -> field_si smallint(2),
    -> field_int int(4),
    -> field_bi bigint(5)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc foo;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| field_ti  | tinyint(1)  | YES  |     | NULL    |       |
| field_si  | smallint(2) | YES  |     | NULL    |       |
| field_int | int(4)      | YES  |     | NULL    |       |
| field_bi  | bigint(5)   | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

mysql>

I had always assumed those size attributes were limiters, MySQL's way of providing some sort of constraint on the integers allowed in the field. While doing some recent work for a MySQL client, I attempted to enforce the range of a tinyint according to that assumption. In reality, I only wanted a sign field, and would have liked to have applied a "CHECK field IN (-1,1)", but without check constraints I figured at least keeping obviously incorrect data out would be better than nothing.

I wanted to see what MySQL's behavior would be on data entry that failed the limiters. I was hoping for an error, but expecting truncation. What I discovered was neither.

mysql> INSERT INTO foo (field_ti) VALUES (-1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT field_ti FROM foo;
+----------+
| field_ti |
+----------+
|       -1 |
+----------+
1 row in set (0.00 sec)

mysql> INSERT INTO foo (field_ti) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT field_ti FROM foo;
+----------+
| field_ti |
+----------+
|       -1 |
|        1 |
+----------+
2 rows in set (0.00 sec)

mysql> INSERT INTO foo (field_ti) VALUES (10);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT field_ti FROM foo;
+----------+
| field_ti |
+----------+
|       -1 |
|        1 |
|       10 |
+----------+
3 rows in set (0.00 sec)

mysql> INSERT INTO foo (field_ti) VALUES (100);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT field_ti FROM foo;
+----------+
| field_ti |
+----------+
|       -1 |
|        1 |
|       10 |
|      100 |
+----------+
4 rows in set (0.00 sec)

mysql>

Two possible conclusions followed immediately: either the limiter feature was horribly broken, or those apparent sizes didn't represent a limiter feature. A full review of MySQL's Numeric Types documentation provided the answer:

MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits. This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.) The display width does not constrain the range of values that can be stored in the column.

And, so, the lesson is repeated: Beware assumptions.

2 comments:

Daniƫl van Eeden said...

How did you solve your issue in the end? I think a trigger could be used as work-around.

mysql [localhost] {msandbox} (test) > CREATE TABLE test001 (`int` tinyint);
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > delimiter //
mysql [localhost] {msandbox} (test) > CREATE TRIGGER force_values BEFORE INSERT ON test001 FOR EACH ROW BEGIN IF (NEW.int > 1) OR (NEW.int < -1) THEN SIGNAL SQLSTATE '03000'; END IF; END; //
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > delimiter ;
mysql [localhost] {msandbox} (test) > INSERT INTO test001 VALUES(1); SELECT * FROM test001;
Query OK, 1 row affected (0.00 sec)

+------+
| int |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO test001 VALUES(0); SELECT * FROM test001;
Query OK, 1 row affected (0.00 sec)

+------+
| int |
+------+
| 1 |
| 0 |
+------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO test001 VALUES(-1); SELECT * FROM test001;
Query OK, 1 row affected (0.00 sec)

+------+
| int |
+------+
| 1 |
| 0 |
| -1 |
+------+
3 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO test001 VALUES(2); SELECT * FROM test001;
ERROR 1644 (03000): Unhandled user-defined exception condition
+------+
| int |
+------+
| 1 |
| 0 |
| -1 |
+------+
3 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO test001 VALUES(-2); SELECT * FROM test001;
ERROR 1644 (03000): Unhandled user-defined exception condition
+------+
| int |
+------+
| 1 |
| 0 |
| -1 |
+------+
3 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) >

GoLo said...

Trigger is a great option. For extra security, another trigger for BEFORE UPDATE should be added.

If you only want values 1 and -1 you can use ABS() on the trigger:

IF ABS(NEW.int) != 1 THEN SIGNAL SQLSTATE '03000';

Also, mysql can read the number on the int(4) if you add the ZEROFILL option:

field_int int(4) ZEROFILL

In this case, if you insert 1 mysql will display 0001, and if you insert 10000, mysql displays 10000