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