int

Numerical Data Types in MySQL

In most MySQL GUI’s that offer the ability to alter the table definitions of a schema, if you add a column that is an INT, if defaults the syntax to be:

ALTER TABLE {table} ADD COLUMN {field} INT(11) DEFAULT NULL;

This is okay, but its really important to understand your datatypes.

What does the INT(11) really mean?

Basically, a common misperception is that the number in brackets is the length of the field. Probably because thats how the syntax works for VARCHAR(34) will allow a maximum length of 34 characters.

For integers, thats not strictly true. If you create an INT field, it will be an INT, no matter what you put in brackets. An INT field has the following maximum storage:

Unsigned - Min : 0,           Max : 4294967295
  Signed - Min : -2147483648  Max : 2147483648

So whats the (11) for you ask?

Basically, this is only used by the MySQL command line client, and this is the maximum length that it will display. The full value will be stored FYI.

So, the important issue is, you really should create your fields with the full understanding of what data may be stored within. There are more integer datatypes than just INT.

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT

The min and max stored data values are as follows

Unsigned (Min is always 0)

TINYINT    - Max : 255
SMALLINT   - Max : 65535
MEDIUMINT  - Max : 16777215
INT        - Max : 4294967295
BIGINT     - Max : 18446744073709551615

Signed

TINYINT    - Min : -127                 Max : 127
SMALLINT   - Min : -32768               Max : 32768
MEDIUMINT  - Min : -8388608             Max : 8388608
INT        - Min : -2147483648          Max : 2147483648
BIGINT     - Min : -9223372036854775808 Max : 9223372036854775808