Integer Types

Integer Types

Integer Types, UNSIGNED, and AUTO_INCREMENT Ranges

The integer types differ only in size and range, and UNSIGNED trades the negatives for double the maximum:

Integer types and their ranges
Type	Bytes	Signed range	UNSIGNED maximum
TINYINT	1	-128 to 127	255
SMALLINT	2	-32,768 to 32,767	65,535
MEDIUMINT	3	±8.4 million	16,777,215
INT	4	±2.15 billion	4,294,967,295
BIGINT	8	±9.22 × 10^18	1.84 × 10^19

The 11 in INT(11) was only a display width for ZEROFILL padding, and both are deprecated:

Deprecated display width, overflow, unsigned arithmetic and an exhausted counterSQL

CREATE TABLE int_demo (tiny TINYINT, utiny TINYINT UNSIGNED, w INT(11));
INSERT INTO int_demo (tiny, utiny) VALUES (128, -1);
SELECT CAST(1 AS UNSIGNED) - 2;
CREATE TABLE seq (id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY);
INSERT INTO seq VALUES (254), (NULL);
INSERT INTO seq VALUES (NULL);

Output

Warning (Code 1681): Integer display width is deprecated and will be removed in a future
release.
ERROR 1264 (22003): Out of range value for column 'tiny' at row 1
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(1 as unsigned) - 2)'
ERROR 1062 (23000): Duplicate entry '255' for key 'seq.PRIMARY'

Strict mode rejects the overflow. With sql_mode = '', inserting (300, -5) succeeds with two warnings and silently stores 127 and 0, so never run production without strict mode. Arithmetic with an UNSIGNED operand is unsigned, so 1 - 2 fails. An exhausted AUTO_INCREMENT stays at the maximum and the next insert collides with it; the view sys.schema_auto_increment_columns now reports an auto_increment_ratio of 1.0000 for seq.

MENU