MySQL 8.4 Release Notes
表在磁盘上的数据存储需求取决于多个因素。不同的存储引擎对数据类型的表示和原始数据的存储方式不同。表数据可能会被压缩，可以针对单个列或整行进行压缩，这使得计算表或列的存储需求变得复杂。The storage requirements for table data on disk depend on several factors. Different storage engines represent data types and store raw data differently. Table data might be compressed, either for a column or an entire row, complicating the calculation of storage requirements for a table or column.
尽管磁盘上的存储布局有所不同，与表行进行通信和交换信息的内部 MySQL API 使用的是统一的数据结构，适用于所有存储引擎。Despite differences in storage layout on disk, the internal MySQL APIs that communicate and exchange information about table rows use a consistent data structure that applies across all storage engines.
本节包括关于 MySQL 支持的每种数据类型的存储需求的指南和信息，包括使用固定大小表示数据类型的存储引擎的内部格式和大小。信息按类别或存储引擎列出。This section includes guidelines and information for the storage requirements for each data type supported by MySQL, including the internal format and size for storage engines that use a fixed-size representation for data types. Information is listed by category or storage engine.
表的内部表示的最大行大小为 65,535 字节，即使存储引擎能够支持更大的行。该数字不包括BLOB或TEXT列，这些列只占该大小的 9 到 12 字节。对于BLOB和TEXT数据，信息在内部存储于与行缓冲区不同的内存区域。不同的存储引擎根据处理相应类型的方法以不同的方式分配和存储这些数据。更多信息，请参见第 18 章，替代存储引擎，以及第 10.4.7 节，“表列数和行大小的限制”。The internal representation of a table has a maximum row size of
65,535 bytes, even if the storage engine is capable of supporting
larger rows. This figure excludes
BLOB or
TEXT columns, which contribute only
9 to 12 bytes toward this size. For
BLOB and
TEXT data, the information is
stored internally in a different area of memory than the row
buffer. Different storage engines handle the allocation and
storage of this data in different ways, according to the method
they use for handling the corresponding types. For more
information, see Chapter 18, Alternative Storage Engines, and
Section 10.4.7, “Limits on Table Column Count and Row Size”.
有关 InnoDB 表存储要求的信息，请参见 第 17.10 节，“InnoDB 行格式”。See Section 17.10, “InnoDB Row Formats” for information about
storage requirements for InnoDB tables.
Important
NDB 表使用4 字节对齐；所有 NDB 数据存储都是以 4 字节的倍数进行的。因此，通常需要 15 字节的列值在 NDB 表中需要 16 字节。例如，在 NDB 表中，TINYINT、SMALLINT、MEDIUMINT 和 INTEGER (INT) 列类型由于对齐因素，每条记录各需要 4 字节存储。NDB tables use
4-byte alignment; all
NDB data storage is done in
multiples of 4 bytes. Thus, a column value that would
typically take 15 bytes requires 16 bytes in an
NDB table. For example, in
NDB tables, the
TINYINT,
SMALLINT,
MEDIUMINT, and
INTEGER
(INT) column types each require
4 bytes storage per record due to the alignment factor.
每个 BIT( 列占用 M)M 位的存储空间。虽然单个 BIT 列 并非 4 字节对齐，NDB 为每行 BIT 列所需的前 1-32 位保留 4 字节（32 位），然后为第 33-64 位保留另外 4 字节，依此类推。Each BIT(
column takes M)M bits of storage
space. Although an individual
BIT column is
not 4-byte aligned,
NDB reserves 4 bytes (32 bits)
per row for the first 1-32 bits needed for
BIT columns, then another 4 bytes for bits
33-64, and so on.
虽然一个 NULL 本身不需要任何存储空间，但如果表定义包含任何允许 NULL 的列，NDB 会为每行保留 4 字节，最多可达 32 个 NULL 列。（如果 NDB Cluster 表定义中有超过 32 个 NULL 列但不超过 64 个 NULL 列，则每行保留 8 字节。）While a NULL itself does not require any
storage space, NDB reserves 4
bytes per row if the table definition contains any columns
allowing NULL, up to 32
NULL columns. (If an NDB Cluster table is
defined with more than 32 NULL columns up
to 64 NULL columns, then 8 bytes per row
are reserved.)
每个使用 NDB 存储引擎的表都需要一个主键；如果你没有定义主键，NDB 将会创建一个“隐藏”的主键。这个隐藏的主键每条表记录将占用 31-35 字节。Every table using the NDB storage
engine requires a primary key; if you do not define a primary
key, a “hidden” primary key is created by
NDB. This hidden primary key
consumes 31-35 bytes per table record.
您可以使用 ndb_size.pl Perl 脚本来估算 NDB 的存储需求。它会连接到当前的 MySQL（而非 NDB Cluster）数据库，并生成报告，说明如果该数据库使用 NDB 存储引擎，将需要多少空间。有关详细信息，请参见 第 25.5.29 节，“ndb_size.pl — NDBCLUSTER 存储需求估算器”。You can use the ndb_size.pl Perl script to
estimate NDB storage requirements.
It connects to a current MySQL (not NDB Cluster) database and
creates a report on how much space that database would require
if it used the NDB storage engine.
See Section 25.5.29, “ndb_size.pl — NDBCLUSTER Size Requirement Estimator” for
more information.
| 数据类型Data Type | 所需存储Storage Required |
|---|---|
TINYINTTINYINT |
1 字节1 byte |
SMALLINTSMALLINT |
2 字节2 bytes |
MEDIUMINTMEDIUMINT |
3 字节3 bytes |
INT, INTEGERINT,
INTEGER |
4 字节4 bytes |
BIGINTBIGINT |
8 字节8 bytes |
FLOAT( |
如果 0 <= p <= 24，则为 4 字节，如果 25 <= p <= 53，则为 8 字节4 bytes if 0 <= p <= 24, 8 bytes if 25
<= p <= 53 |
FLOATFLOAT |
4 字节4 bytes |
DOUBLE [PRECISION]，REALDOUBLE [PRECISION],
REAL |
8 字节8 bytes |
DECIMAL(,
NUMERIC( |
不同；请参阅以下讨论Varies; see following discussion |
BIT( |
大约 (M 7)/8 字节approximately (M+7)/8 bytes |
DECIMAL（和 NUMERIC）列的值使用二进制格式表示，将九个十进制（基数 10）数字打包到四个字节中。每个值的整数部分和小数部分的存储分别确定。每九个数字需要四个字节，“剩余”的数字需要四个字节的一部分。额外数字所需的存储由下表给出。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 |
对于 TIME、DATETIME 和 TIMESTAMP 列，MySQL 5.6.4 之前创建的表所需的存储与从 5.6.4 起创建的表不同。这是因为 5.6.4 中的更改允许这些类型具有小数部分，该小数部分需要 0 到 3 个字节。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 | MySQL 5.6.4 之前所需存储Storage Required Before MySQL 5.6.4 | 自 MySQL 5.6.4 起所需存储Storage Required as of MySQL 5.6.4 |
|---|---|---|
YEARYEAR |
1 字节1 byte | 1 字节1 byte |
DATEDATE |
3 字节3 bytes | 3 字节3 bytes |
TIMETIME |
3 字节3 bytes | 3 字节 小数秒存储3 bytes + fractional seconds storage |
DATETIMEDATETIME |
8 字节8 bytes | 5 字节 小数秒存储5 bytes + fractional seconds storage |
TIMESTAMPTIMESTAMP |
4 字节4 bytes | 4 字节 小数秒存储4 bytes + fractional seconds storage |
从 MySQL 5.6.4 开始，YEAR 和 DATE 的存储保持不变。然而，TIME、DATETIME 和 TIMESTAMP 的表示方式有所不同。DATETIME 的存储更高效，非分数部分只需 5 个字节而不是 8 个字节，并且这三种类型都有分数部分，其所需字节数从 0 到 3 个不等，具体取决于存储值的秒分数精度。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 字节0 bytes |
| 1, 2 | 1 字节1 byte |
| 3, 4 | 2 字节2 bytes |
| 5, 6 | 3 字节3 bytes |
例如，TIME(0)、TIME(2)、TIME(4) 和 TIME(6) 分别使用 3、4、5 和 6 个字节。TIME 和 TIME(0) 是等效的，并且需要相同的存储空间。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.
有关时间值内部表示的详细信息，请参阅 MySQL 内部机制：重要算法和结构。For details about internal representation of temporal values, see MySQL Internals: Important Algorithms and Structures.
在下表中，M 表示非二进制字符串类型的声明列长度（以字符为单位）以及二进制字符串类型的声明列长度（以字节为单位）。L 表示给定字符串值的实际字节长度。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( |
紧凑型 InnoDB 行格式家族优化了可变长度字符集的存储。请参阅 COMPACT 行格式存储特性。否则，M × w 字节，<=
M <= 255，其中 w 是该字符集中的最长字符所需的字节数。The compact family of InnoDB row formats optimize storage for
variable-length character sets. See
COMPACT Row Format Storage Characteristics.
Otherwise, M ×
w bytes, <=
255, where
w is the number of bytes
required for the maximum-length character in the character
set. |
BINARY( |
M 字节，0 <=
M <= 255M bytes, 0 <=
255 |
VARCHAR(M), VARBINARY(M)VARCHAR(,
VARBINARY( |
L 如果列值需要 0 − 255 字节则为 1 字节，L 如果值可能需要超过 255 字节则为 2 字节L + 1 bytes if column values require 0
− 255 bytes, L + 2 bytes
if values may require more than 255 bytes |
TINYBLOB，TINYTEXTTINYBLOB,
TINYTEXT |
L 1 字节，其中 L < 28L + 1 bytes, where
L <
28 |
BLOBTEXTBLOB, TEXT |
L 2 字节，其中 L < 216L + 2 bytes, where
L <
216 |
MEDIUMBLOB, MEDIUMTEXTMEDIUMBLOB,
MEDIUMTEXT |
L 3 字节，其中 L < 224L + 3 bytes, where
L <
224 |
LONGBLOB, LONGTEXTLONGBLOB,
LONGTEXT |
L 4 字节，其中 L < 232L + 4 bytes, where
L <
232 |
ENUM('value1','value2',...)ENUM(' |
1 或 2 字节，取决于枚举值的数量（最多 65,535 个值）1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum) |
SET('value1','value2',...)SET(' |
1、2、3、4 或 8 字节，取决于集合成员的数量（最多 64 个成员）1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum) |
可变长度字符串类型使用长度前缀加数据的方式存储。长度前缀根据数据类型需要一到四个字节，前缀的值是L（字符串的字节长度）。例如，存储一个MEDIUMTEXT值需要L字节来存储值，再加上三个字节来存储值的长度。Variable-length string types are stored using a length prefix
plus data. The length prefix requires from one to four bytes
depending on the data type, and the value of the prefix is
L (the byte length of the string).
For example, storage for a
MEDIUMTEXT value requires
L bytes to store the value plus three
bytes to store the length of the value.
要计算存储特定 CHAR、VARCHAR 或 TEXT 列值所使用的字节数，必须考虑该列使用的字符集以及值是否包含多字节字符。特别是，使用 UTF-8 Unicode 字符集时，必须注意并非所有字符使用的字节数相同。utf8mb3 和 utf8mb4 字符集每个字符分别可能需要最多三个和四个字节。有关 utf8mb3 或 utf8mb4 字符不同类别所使用存储的详细情况，请参见 第 12.9 节，“Unicode 支持”。To calculate the number of bytes used to store a particular
CHAR,
VARCHAR, or
TEXT column value, you must take
into account the character set used for that column and whether
the value contains multibyte characters. In particular, when
using a UTF-8 Unicode character set, you must keep in mind that
not all characters use the same number of bytes.
utf8mb3 and utf8mb4
character sets can require up to three and four bytes per
character, respectively. For a breakdown of the storage used for
different categories of utf8mb3 or
utf8mb4 characters, see
Section 12.9, “Unicode Support”.
VARCHAR、VARBINARY，以及 BLOB 和 TEXT 类型都是可变长度类型。对于每种类型，存储需求取决于以下因素：VARCHAR,
VARBINARY, and the
BLOB and
TEXT types are variable-length
types. For each, the storage requirements depend on these
factors:
列值的实际长度The actual length of the column value
列的最大可能长度The column's maximum possible length
列使用的字符集，因为某些字符集包含多字节字符The character set used for the column, because some character sets contain multibyte characters
例如，一个 VARCHAR(255) 列可以存储最大长度为 255 个字符的字符串。假设该列使用 latin1 字符集（每个字符占用一个字节），实际所需的存储空间为字符串的长度 (L)，加上一个字节用于记录字符串长度。对于字符串 'abcd'，L 为 4，存储需求为五个字节。如果相同的列改为使用 ucs2 双字节字符集，则存储需求为 10 个字节：'abcd' 的长度为八个字节，列还需要两个字节来存储长度，因为最大长度大于 255（最多可达 510 个字节）。For example, a VARCHAR(255) column can hold a
string with a maximum length of 255 characters. Assuming that
the column uses the latin1 character set (one
byte per character), the actual storage required is the length
of the string (L), plus one byte to
record the length of the string. For the string
'abcd', L is 4 and
the storage requirement is five bytes. If the same column is
instead declared to use the ucs2 double-byte
character set, the storage requirement is 10 bytes: The length
of 'abcd' is eight bytes and the column
requires two bytes to store lengths because the maximum length
is greater than 255 (up to 510 bytes).
在 VARCHAR 或 VARBINARY 列中可以存储的有效最大 字节 数受 65,535 字节的最大行大小限制，该大小在所有列之间共享。对于存储多字节字符的 VARCHAR 列，有效最大 字符 数较少。例如，utf8mb4 字符每个字符最多可能需要四个字节，因此使用 utf8mb4 字符集的 VARCHAR 列可以声明最多 16,383 个字符。参见 第 10.4.7 节，“表列数和行大小的限制”。The effective maximum number of bytes that
can be stored in a VARCHAR or
VARBINARY column is subject to
the maximum row size of 65,535 bytes, which is shared among all
columns. For a VARCHAR column
that stores multibyte characters, the effective maximum number
of characters is less. For example,
utf8mb4 characters can require up to four
bytes per character, so a VARCHAR
column that uses the utf8mb4 character set
can be declared to be a maximum of 16,383 characters. See
Section 10.4.7, “Limits on Table Column Count and Row Size”.
InnoDB 将固定长度字段中大于等于 768 字节的字段编码为可变长度字段，这些字段可以存储在页外。例如，如果字符集的最大字节长度大于 3（如 utf8mb4），则 CHAR(255) 列可以超过 768 字节。InnoDB encodes fixed-length fields greater
than or equal to 768 bytes in length as variable-length fields,
which can be stored off-page. For example, a
CHAR(255) column can exceed 768 bytes if the
maximum byte length of the character set is greater than 3, as
it is with utf8mb4.
NDB 存储引擎支持可变宽度列。这意味着 NDB Cluster 表中的 VARCHAR 列所需的存储量与其他存储引擎相同，但此类值是按 4 字节对齐存储的。因此，使用 latin1 字符集在 VARCHAR(50) 列中存储字符串 'abcd' 需要 8 字节（而在 MyISAM 表中相同列值则只需要 5 字节）。The NDB storage engine supports
variable-width columns. This means that a
VARCHAR column in an NDB Cluster
table requires the same amount of storage as would any other
storage engine, with the exception that such values are 4-byte
aligned. Thus, the string 'abcd' stored in a
VARCHAR(50) column using the
latin1 character set requires 8 bytes (rather
than 5 bytes for the same column value in a
MyISAM table).
TEXT、BLOB 和 JSON 列在 NDB 存储引擎中的实现方式不同，其中该列中的每一行由两个独立部分组成。其中一部分是固定大小（TEXT 和 BLOB 为 256 字节，JSON 为 4000 字节），实际上存储在原始表中。另一部分包括超过 256 字节的任何数据，存储在一个隐藏的 blob 部分表中。第二个表中行的大小由列的确切类型决定，如下表所示：TEXT,
BLOB, and
JSON columns are implemented
differently in the NDB storage
engine, wherein each row in the column is made up of two
separate parts. One of these is of fixed size (256 bytes for
TEXT and BLOB, 4000 bytes
for JSON), and is actually stored in the
original table. The other consists of any data in excess of 256
bytes, which is stored in a hidden blob parts table. The size of
the rows in this second table are determined by the exact type
of the column, as shown in the following table:
| 类型Type | 数据块部分大小Blob Part Size |
|---|---|
BLOBTEXTBLOB, TEXT |
2000 |
MEDIUMBLOB, MEDIUMTEXTMEDIUMBLOB,
MEDIUMTEXT |
4000 |
LONGBLOB, LONGTEXTLONGBLOB,
LONGTEXT |
13948 |
JSONJSON |
8100 |
这意味着如果 size <= 256（其中 size 表示行的大小），TEXT 列的大小为 256；否则，大小为 256 size (2000 × (size − 256) % 2000)。This means that the size of a
TEXT column is 256 if
size <= 256 (where
size represents the size of the row);
otherwise, the size is 256 +
size + (2000 ×
(size − 256) % 2000).
NDB 不会为 TINYBLOB 或 TINYTEXT 列的值单独存储 blob 部分。No blob parts are stored separately by NDB
for TINYBLOB or TINYTEXT
column values.
您可以在创建或修改父表时，通过在列注释中使用 NDB_COLUMN 将 NDB blob 列的 blob 部分大小增加到最大 13948。NDB 还支持使用列注释中的 NDB_TABLE 为 TEXT、BLOB 或 JSON 列设置内联大小。更多信息请参见 NDB_COLUMN 选项。You can increase the size of an NDB blob
column's blob part to the maximum of 13948 using
NDB_COLUMN in a column comment when creating
or altering the parent table. NDB also
supports setting the inline size for a TEXT,
BLOB, or JSON column,
using NDB_TABLE in a column comment. See
NDB_COLUMN Options, for
more information.
ENUM 对象的大小由不同枚举值的数量决定。对于最多 255 个可能值的枚举，使用一个字节。对于有 256 到 65,535 个可能值的枚举，使用两个字节。参见 第 13.3.5 节，“ENUM 类型”。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 having between 256 and
65,535 possible values. See Section 13.3.5, “The ENUM Type”.
SET 对象的大小取决于不同集合成员的数量。如果集合大小为 N，则该对象占用 (N+7)/8 字节，向上取整至 1、2、3、4 或 8 字节。一个 SET 最多可以有 64 个成员。参见 第 13.3.6 节，“集合类型”。The size of a SET object is
determined by the number of different set members. If the set
size is N, the object occupies
( bytes,
rounded up to 1, 2, 3, 4, or 8 bytes. A
N+7)/8SET can have a maximum of 64
members. See Section 13.3.6, “The SET Type”.
MySQL 使用 4 个字节来指示 SRID，然后是该值的 WKB 表示形式来存储几何值。LENGTH() 函数返回存储该值所需的字节空间。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.
有关 WKB 和空间值内部存储格式的说明，请参见 第 13.4.3 节，“支持的空间数据格式”。For descriptions of WKB and internal storage formats for spatial values, see Section 13.4.3, “Supported Spatial Data Formats”.
一般来说，JSON 列的存储需求大致与 LONGBLOB 或 LONGTEXT 列相同；也就是说，JSON 文档占用的空间大致与将该文档作为字符串存储在这些类型的列中所占用的空间相同。然而，由二进制编码引入的开销，包括用于查找 JSON 文档中存储的各个值所需的元数据和字典，是存在的。例如，存储在 JSON 文档中的字符串需要额外的 4 到 10 字节存储空间，这取决于字符串的长度以及其所在对象或数组的大小。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.
此外，MySQL 对存储在 JSON 列中的任何 JSON 文档的大小施加了限制，它不能大于 max_allowed_packet 的值。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.