MySQL Manual
6 MySQL Language Reference
6.2.6 Column Type Storage Requirements
The storage requirements for each of the column types supported by MySQL are listed by category.
Storage Requirements for Numeric Types
| Column type | Storage required |
TINYINT | 1 byte |
SMALLINT | 2 bytes |
MEDIUMINT | 3 bytes |
INT | 4 bytes |
INTEGER | 4 bytes |
BIGINT | 8 bytes |
FLOAT(X) | 4 if X <= 24 or 8 if 25 <= X <= 53 |
FLOAT | 4 bytes |
DOUBLE | 8 bytes |
DOUBLE PRECISION | 8 bytes |
REAL | 8 bytes |
DECIMAL(M,D) | M+2 bytes if D > 0, M+1 bytes if D = 0 (D+2, if M < D)
|
NUMERIC(M,D) | M+2 bytes if D > 0, M+1 bytes if D = 0 (D+2, if M < D)
|
Storage Requirements for Date and Time Types
| Column type | Storage required |
DATE | 3 bytes |
DATETIME | 8 bytes |
TIMESTAMP | 4 bytes |
TIME | 3 bytes |
YEAR | 1 byte |
Storage Requirements for String Types
| Column type | Storage required |
CHAR(M) | M bytes, 1 <= M <= 255
|
VARCHAR(M) | L+1 bytes, where L <= M and
1 <= M <= 255
|
TINYBLOB, TINYTEXT | L+1 bytes,
where L < 2^8
|
BLOB, TEXT | L+2 bytes,
where L < 2^16
|
MEDIUMBLOB, MEDIUMTEXT | L+3 bytes,
where L < 2^24
|
LONGBLOB, LONGTEXT | L+4 bytes,
where L < 2^32
|
ENUM('value1','value2',...) | 1 or 2 bytes, depending on the number of enumeration values (65535 values maximum) |
SET('value1','value2',...) | 1, 2, 3, 4 or 8 bytes, depending on the number of set members (64 members maximum) |
VARCHAR and the BLOB and TEXT types are variable-length
types, for which the storage requirements depend on the actual length of
column values (represented by L in the preceding table), rather than
on the type's maximum possible size. For example, a VARCHAR(10)
column can hold a string with a maximum length of 10 characters. The actual
storage required is the length of the string (L), plus 1 byte to
record the length of the string. For the string 'abcd', L is 4
and the storage requirement is 5 bytes.
The BLOB and TEXT types require 1, 2, 3, or 4 bytes to record
the length of the column value, depending on the maximum possible length of
the type. See section 6.2.3.2 The BLOB and TEXT Types.
If a table includes any variable-length column types, the record format will also be variable-length. Note that when a table is created, MySQL may, under certain conditions, change a column from a variable-length type to a fixed-length type, or vice-versa. See section 6.5.3.1 Silent Column Specification Changes.
The size of an ENUM object is determined by the number of
different enumeration values. One byte is used for enumerations with up
to 255 possible values. Two bytes are used for enumerations with up to
65535 values. See section 6.2.3.3 The ENUM Type.
The size of a SET object is determined by the number of different
set members. If the set size is N, the object occupies (N+7)/8
bytes, rounded up to 1, 2, 3, 4, or 8 bytes. A SET can have a maximum
of 64 members. See section 6.2.3.4 The SET Type.
The maximum size of a row in a MyISAM table is 65534 bytes. Each
BLOB and TEXT column accounts for only 5-9 bytes
toward this size.
