MySQL Integer Types

Integer Types and Ranges

TINYINT

• Unsigned Range 0 - 255
• Signed Range -128 - 127
• Storage Space: 1 byte

SMALLINT

• Unsigned Range 0 - 65535
• Signed Range -32768 - 32767
• Storage Space: 2 bytes

MEDIUMINT

• Unsigned Range 0 - 16777215
• Signed Range -8388608 - 8388607
• Storage Space: 3 bytes

INT

• Unsigned Range 0 - 4294967295
• Signed Range -2147483648 - 2147483647
• Storage Space: 4 bytes

BIGINT

• Unsigned Range 0 - 18446744073709551615
• Signed Range -9223372036854775808 - 9223372036854775807
• Storage Space: 8 bytes

Length is Almost Pointless (For These Types)

In most table definitions I see, people specifying the length for an integer, for example:

`id` int(3) NOT NULL AUTO_INCREMENT,

The (3) part does not prevent the column from taking the value 1000 which has 4 digits. Also, because it is not specified as UNSIGNED, it will allow a negative value which is probably not desired. The maximum and minimum values the column can take are only defined by the type, not the length. In fact the length only really matters when the column also has the zerofill attribute, in which case 0's are prefixed to any value below that length when being displayed. This can be best demonstrated by the following code:

CREATE TABLE `mytable` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `int1` int(10) NOT NULL,
    `int2` int(3) NOT NULL,
    `zerofill1` int(10) ZEROFILL NOT NULL,
    `zerofill2` int(3) ZEROFILL NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `mytable` 
(`int1`, `int2`, `zerofill1`, `zerofill2`) 
VALUES
(10000, 10000, 10000, 10000),
(100, 100, 100, 100);

select * from mytable;

The output of which is below:

+----+-------+-------+------------+-----------+
| id | int1  | int2  | zerofill1  | zerofill2 |
+----+-------+-------+------------+-----------+
|  1 | 10000 | 10000 | 0000010000 |     10000 |
|  2 |   100 |   100 | 0000000100 |       100 |
+----+-------+-------+------------+-----------+

As you can see, having a length of 3 didn't prevent a value of 10000 being used or displayed for the zerofill2 column which has zerofill and a length of just 3. Nor did it reduce the amount of padding/spacing for the column. The output shows a width of 10 digits in zerofill2 even though length is specified as just 3. This is because the INT type can take a value with 10 digits. Compare this with column int1 which has a much larger length of 10 but only has a width of just 5 characters because that is the largest number it has to display for this select query.

Conclusion

Please don't specify the length on integer types unless you want to prefix any number with fewer digits than that with 0s, and pay more attention to using the correct type and correct signage instead of giving everything just an int value.

References

• MySQL Docs - Integer Types
• Stack Overflow - What is the size of column of int(11) in mysql in bytes?