SQL integer types

SQL integer types are a family of fixed-width integer types differing in how many bytes each occupies and therefore in the range of values they can hold. In MySQL:

Type	Bytes	Signed range	Unsigned range
TINYINT	1	$-128$ to $127$	$0$ to $255$
SMALLINT	2	$-32,768$ to $32,767$	$0$ to $65,535$
MEDIUMINT	3	$-8,388,608$ to $8,388,607$	$0$ to $16,777,215$
INT	4	$-2.1\times 10^9$ to $2.1\times 10^9$	$0$ to $4.3\times 10^9$
BIGINT	8	$\pm 9.2\times 10^{18}$	$0$ to $1.8\times 10^{19}$

Picking the right width matters for storage efficiency and, on very large tables, for performance — narrower types let more rows fit in a memory page, which makes scans faster. For a 10-million-row table, the choice between INT (4 bytes) and BIGINT (8 bytes) is 40 MB of difference for that column alone, plus whatever index space it occupies.

The general principle: pick the smallest type that comfortably accommodates the values the column will hold, plus future growth.

• TINYINT — boolean-like flags, percentages 0-100, small enumerations, ages.
• SMALLINT — counts up to ~30,000, IDs in small tables, years.
• INT — the workhorse. Most integer IDs, counts up to billions, financial amounts in cents up to ~$21 million.
• BIGINT — Unix timestamps in milliseconds, IDs in very large tables, financial amounts in cents above ~$21 million.

UNSIGNED doubles the positive range by sacrificing negative values. It’s appropriate when negative values are meaningless (counts, sizes, IDs that are never negative). Note that UNSIGNED is a MySQL extension — PostgreSQL has no unsigned integer types at all; SQL Server and SQLite likewise lack them. For portable code, either use the next-larger signed type (BIGINT instead of INT UNSIGNED) or add a CHECK (col >= 0) constraint.

MEDIUMINT is also MySQL-specific. PostgreSQL has only SMALLINT (2 bytes), INTEGER (4 bytes), and BIGINT (8 bytes). SQL Server adds TINYINT (1 byte, unsigned 0-255 only). SQLite’s INTEGER is variable-width up to 8 bytes regardless of how the column is declared.

For exact decimals (financial data), DECIMAL is the right type, not an integer. For approximate floating-point, FLOAT or DOUBLE. The integer family is for whole-number quantities only.

For primary keys, the standard idiom is INT NOT NULL AUTO_INCREMENT, sometimes BIGINT for tables expected to grow past a few billion rows.