Integer Types

Integers are whole numbers. MySQL offers five sizes. Choose the smallest that is safe.

Pick by what the column holds

1
2
3
4
5
6
7
8

CREATE TABLE ratings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    stars TINYINT UNSIGNED NOT NULL,
    year_published SMALLINT UNSIGNED NOT NULL,
    votes INT UNSIGNED NOT NULL DEFAULT 0
);
INSERT INTO ratings (stars, year_published) VALUES (5, 2026);
SELECT * FROM ratings;

1
2
3
4
5
6

+----+-------+----------------+-------+
| id | stars | year_published | votes |
+----+-------+----------------+-------+
|  1 |     5 |           2026 |     0 |
+----+-------+----------------+-------+
1 row in set

• stars (1 to 5) fits in a TINYINT.
• A year fits in SMALLINT (or the YEAR type).
• votes can reach millions, so INT UNSIGNED.
• The primary key of a table that may grow very large is BIGINT UNSIGNED.

UNSIGNED

UNSIGNED removes negative numbers and doubles the positive range. Use it for IDs, counts and quantities that can never be negative. It is MySQL-specific.

1

INSERT INTO ratings (stars, year_published) VALUES (-1, 2026);

1

ERROR 1264 (22003): Out of range value for column 'stars' at row 1

Out of range values

In strict mode (the default), a number that does not fit is rejected:

1

INSERT INTO ratings (stars, year_published) VALUES (300, 2026);

1

ERROR 1264 (22003): Out of range value for column 'stars' at row 1

Without strict mode MySQL would silently store 255 and issue only a warning (SQL Mode).

Careful with UNSIGNED arithmetic

A subtraction that would go below zero is an error, not a negative number:

1

SELECT CAST(3 AS UNSIGNED) - 5 AS result;

1

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(3 as unsigned) - 5)'

A common cause: WHERE stock - sold < 0 on unsigned columns. Cast to signed first, or compare in a different way (WHERE stock < sold).

The display width is deprecated

You will see INT(11) in old code. The number in brackets is a display width that has no effect on the range or the storage. It is deprecated, and MySQL 8 warns about it (except for TINYINT(1)).

1
2

CREATE TABLE old_style (n INT(11));
SHOW WARNINGS;

1
2
3
4
5
6

+---------+------+------------------------------------------------------------------------------+
| Level   | Code | Message                                                                      |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1681 | Integer display width is deprecated and will be removed in a future release. |
+---------+------+------------------------------------------------------------------------------+
1 row in set

Write INT, not INT(11).

How big should a primary key be

An INT UNSIGNED primary key allows 4.29 billion rows. That is enough for nearly every table. The cost of BIGINT is twice the space for the key, and for the same key repeated in every secondary index and every foreign key column that points to it. Start with INT UNSIGNED, and use BIGINT UNSIGNED when you can foresee more than a billion rows, such as logs, events and analytics.