Numeric Type Storage Requirements#

Data Type	Storage Required
`TINYINT`	1 byte
`SMALLINT`	2 bytes
`MEDIUMINT`	3 bytes
`INT`, `INTEGER`	4 bytes
`BIGINT`	8 bytes
FLOAT(``*p*``)	4 bytes if 0 <= `*p` <= 24, 8 bytes if 25 <= `p*` <= 53
`FLOAT`	4 bytes
`DOUBLE [PRECISION]`, `REAL`	8 bytes
DECIMAL(``*M``,``D``), NUMERIC(``M``,``D*``)	Varies; see following discussion
BIT(``*M*``)	approximately (`*M*`+7)/8 bytes

Values for DECIMAL (and NUMERIC) columns are represented using a binary format that packs nine decimal (base 10) digits into four bytes. Storage for the integer and fractional parts of each value are determined separately. Each multiple of nine digits requires four bytes, and the “leftover” digits require some fraction of four bytes. The storage required for excess digits is given by the following table.

Leftover Digits	Number of Bytes
0	0
1	1
2	1
3	2
4	2
5	3
6	3
7	4
8	4

Date and Time Type Storage Requirements#

For TIME, DATETIME, and TIMESTAMP columns, the storage required for tables created before MySQL 5.6.4 differs from tables created from 5.6.4 on. This is due to a change in 5.6.4 that permits these types to have a fractional part, which requires from 0 to 3 bytes.

Data Type	Storage Required Before MySQL 5.6.4	Storage Required as of MySQL 5.6.4
`YEAR`	1 byte	1 byte
`DATE`	3 bytes	3 bytes
`TIME`	3 bytes	3 bytes + fractional seconds storage
`DATETIME`	8 bytes	5 bytes + fractional seconds storage
`TIMESTAMP`	4 bytes	4 bytes + fractional seconds storage

As of MySQL 5.6.4, storage for YEAR and DATE remains unchanged. However, TIME, DATETIME, and TIMESTAMP are represented differently. DATETIME is packed more efficiently, requiring 5 rather than 8 bytes for the nonfractional part, and all three parts have a fractional part that requires from 0 to 3 bytes, depending on the fractional seconds precision of stored values.

Fractional Seconds Precision	Storage Required
0	0 bytes
1, 2	1 byte
3, 4	2 bytes
5, 6	3 bytes

For example, TIME(0), TIME(2), TIME(4), and TIME(6) use 3, 4, 5, and 6 bytes, respectively. TIME and TIME(0) are equivalent and require the same storage.

String Type Storage Requirements#

In the following table, ***M*** represents the declared column length in characters for nonbinary string types and bytes for binary string types. ***L*** represents the actual length in bytes of a given string value.

Data Type	Storage Required
CHAR(``*M*``)	The compact family of InnoDB row formats optimize storage for variable-length character sets. See COMPACT Row Format Storage Characteristics. Otherwise, `*M` × `w` bytes, <= ``M`` <= 255, where `w*` is the number of bytes required for the maximum-length character in the character set.
BINARY(``*M*``)	`*M` bytes, 0 <= ``M*`` <= 255
VARCHAR(``*M``), VARBINARY(``M*``)	`*L` + 1 bytes if column values require 0 − 255 bytes, `L*` + 2 bytes if values may require more than 255 bytes
`TINYBLOB`, `TINYTEXT`	`*L` + 1 bytes, where `L*` < 28
`BLOB`, `TEXT`	`*L` + 2 bytes, where `L*` < 216
`MEDIUMBLOB`, `MEDIUMTEXT`	`*L` + 3 bytes, where `L*` < 224
`LONGBLOB`, `LONGTEXT`	`*L` + 4 bytes, where `L*` < 232
ENUM('``*value1``','``value2*``',...)	1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
SET('``*value1``','``value2*``',...)	1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)

Spatial Type Storage Requirements#

MySQL stores geometry values using 4 bytes to indicate the SRID followed by the WKB representation of the value. The LENGTH() function returns the space in bytes required for value storage.

JSON Storage Requirements#

In general, the storage requirement for a JSON column is approximately the same as for a LONGBLOB or LONGTEXT column; that is, the space consumed by a JSON document is roughly the same as it would be for the document’s string representation stored in a column of one of these types. However, there is an overhead imposed by the binary encoding, including metadata and dictionaries needed for lookup, of the individual values stored in the JSON document. For example, a string stored in a JSON document requires 4 to 10 bytes additional storage, depending on the length of the string and the size of the object or array in which it is stored.

In addition, MySQL imposes a limit on the size of any JSON document stored in a JSON column such that it cannot be any larger than the value of max_allowed_packet.

📎 参考文章#

MySQL :: MySQL 8.0 Reference Manual :: 13.7 Data Type Storage Requirements

Data Type	Storage Required
CHAR(``*M*``)	The compact family of InnoDB row formats optimize storage for variable-length character sets. See COMPACT Row Format Storage Characteristics. Otherwise, `*M` × `w` bytes, <= ``M`` <= 255, where `w*` is the number of bytes required for the maximum-length character in the character set.
BINARY(``*M*``)	`*M` bytes, 0 <= ``M*`` <= 255
VARCHAR(``*M``), VARBINARY(``M*``)	`*L` + 1 bytes if column values require 0 − 255 bytes, `L*` + 2 bytes if values may require more than 255 bytes
`TINYBLOB`, `TINYTEXT`	`*L` + 1 bytes, where `L*` < 28
`BLOB`, `TEXT`	`*L` + 2 bytes, where `L*` < 216
`MEDIUMBLOB`, `MEDIUMTEXT`	`*L` + 3 bytes, where `L*` < 224
`LONGBLOB`, `LONGTEXT`	`*L` + 4 bytes, where `L*` < 232
ENUM('``*value1``','``value2*``',...)	1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
SET('``*value1``','``value2*``',...)	1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)

MySQL 数据类型字节长度