MySQL Integer Types and Storage Sizes
Choosing the correct data type in MySQL is crucial for optimizing database performance and storage efficiency. This article provides a direct overview of the storage requirements, byte sizes, and value ranges for all integer types available in MySQL, including TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT, helping you make informed decisions for your database schema design.
Summary of MySQL Integer Storage Requirements
MySQL offers five primary integer data types. Each type requires a fixed amount of storage space, regardless of whether the values stored are positive, negative, or zero.
| Integer Type | Storage Size (Bytes) | Minimum Value (Signed) | Maximum Value (Signed) | Minimum Value (Unsigned) | Maximum Value (Unsigned) |
|---|---|---|---|---|---|
| TINYINT | 1 Byte | -128 | 127 | 0 | 255 |
| SMALLINT | 2 Bytes | -32,768 | 32,767 | 0 | 65,535 |
| MEDIUMINT | 3 Bytes | -8,388,608 | 8,388,607 | 0 | 16,777,215 |
| INT / INTEGER | 4 Bytes | -2,147,483,648 | 2,147,483,647 | 0 | 4,294,967,295 |
| BIGINT | 8 Bytes | -9,223,372,036,854,775,808 | 9,223,372,036,854,775,807 | 0 | 18,446,744,073,709,551,615 |
Signed vs. Unsigned Integers
By default, MySQL integer types are “signed,” meaning they can store
both negative and positive numbers. However, if you apply the
UNSIGNED attribute to an integer column, you disallow
negative values.
While using UNSIGNED does not change the physical
storage size (a TINYINT still takes 1 byte), it shifts the
range of allowable values. By eliminating negative numbers, the maximum
positive limit is effectively doubled. For example, a signed
TINYINT ranges from -128 to 127, whereas an unsigned
TINYINT ranges from 0 to 255.
Common Misconception: Integer Display Width
In older versions of MySQL, you might frequently see definitions like
INT(11) or TINYINT(4). The number inside the
parentheses represents the “display width” and has absolutely no impact
on the storage size or the range of values that the column can hold. An
INT(1) and an INT(11) both require exactly 4
bytes of storage and can store the exact same range of numbers.
To prevent confusion, MySQL has deprecated the display width attribute for integer data types in newer versions (MySQL 8.0.17 and later).