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.
表数据在磁盘上的存储需求取决于几个因素。不同的存储引擎表示数据类型，存储原始数据的方式也不同。表数据可能被压缩，无论是对列还是整个行，这使得计算表或列的存储需求变得复杂。
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 api使用了适用于所有存储引擎的一致数据结构。
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.
本节包括MySQL支持的每种数据类型的存储要求的指导方针和信息，包括使用固定大小的数据类型表示的存储引擎的内部格式和大小。信息按类别或存储引擎列出。
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 16, Alternative Storage Engines, and Section 8.4.7, “Limits on Table Column Count and Row Size”.
即使存储引擎能够支持更大的行，表的内部表示的最大行大小也是65,535字节。这个数字不包括BLOB或TEXT列，它们只占这个大小的9到12个字节。对于BLOB和TEXT数据，信息在内部存储在与行缓冲区不同的内存区域中。不同的存储引擎根据它们用于处理相应类型的方法，以不同的方式处理这些数据的分配和存储。
InnoDB Table Storage Requirements
See Section 15.10, “InnoDB Row Formats” for information about storage requirements for InnoDB tables.
关于InnoDB表的存储要求，请参见15.10节“InnoDB行格式”。
NDB Table Storage Requirements
Important
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.
NDB表使用4字节对齐;所有NDB数据存储都是4字节的倍数。因此，在NDB表中，通常需要15字节的列值需要16字节。例如，在NDB表中，TINYINT、SMALLINT、MEDIUMINT和INTEGER (INT)列类型由于对齐因素，每条记录都需要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.
每个BIT(M)列占用M位的存储空间。尽管单个BIT列不是4字节对齐的，但NDB每行保留4个字节(32位)用于BIT列所需的前1-32位，然后为33-64位保留另外4个字节，以此类推。
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.)
虽然NULL本身不需要任何存储空间，但如果表定义包含任何允许NULL的列，NDB将为每行保留4个字节，最多为32个NULL列。(如果一个NDB Cluster表定义了超过32个NULL列，最多64个NULL列，那么每行保留8个字节。)
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存储引擎的表都需要一个主键;如果没有定义主键，NDB会创建一个“隐藏”主键。这个隐藏的主键每条表记录消耗31-35字节。
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 23.5.28, “ndb_size.pl — NDBCLUSTER Size Requirement Estimator” for more information.
您可以使用ndb_size.pl Perl脚本来估计NDB存储需求。它连接到当前的MySQL(不是NDB集群)数据库，并创建一个报告，说明如果使用NDB存储引擎，该数据库将需要多少空间。
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( | 4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53 |
| FLOAT | 4 bytes |
DOUBLE [PRECISION], REAL | 8 bytes |
DECIMAL(, NUMERIC( | Varies; see following discussion |
BIT( | 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.
DECIMAL(和NUMERIC)列的值使用二进制格式表示，该格式将9个十进制(以10为基数)数字打包成4个字节。每个值的整数部分和小数部分的存储是分别确定的。每个九位数的倍数需要四个字节，而“剩余”数字需要四个字节的某些部分。下表给出了多余数字所需的存储空间。
| 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.
对于TIME、DATETIME和TIMESTAMP列，在MySQL 5.6.4之前创建的表所需的存储空间与在5.6.4之后创建的表不同。这是由于5.6.4中的一个更改，允许这些类型有一个小数部分，这需要从0到3字节。
| 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.
从MySQL 5.6.4开始，YEAR和DATE的存储保持不变。但是，TIME、DATETIME和TIMESTAMP的表示方式不同。DATETIME的打包效率更高，非小数部分需要5个字节而不是8个字节，所有三个部分都有一个小数部分，需要0到3个字节，这取决于存储值的小数秒精度。
| 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.
例如TIME(0)、TIME(2)、TIME(4)和TIME(6)分别使用3、4、5和6个字节。TIME和TIME(0)是等价的，需要相同的存储空间。
For details about internal representation of temporal values, see MySQL Internals: Important Algorithms and Structures.
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.
在下表中，M以字符表示非二进制字符串类型的列长度，以字节表示二进制字符串类型的列长度。L表示给定字符串值的实际字节长度。
| Data Type | Storage Required |
|---|---|
CHAR( | 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 bytes, 0 <= 255 |
VARCHAR(, VARBINARY( | 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(' | 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum) |
SET(' | 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum) |
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.
变长字符串类型使用长度前缀加数据存储。长度前缀要求根据数据类型从1到4个字节，前缀的值是L(字符串的字节长度)。例如，存储MEDIUMTEXT值需要L个字节来存储值，加上3个字节来存储值的长度。
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 utf8 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 10.9, “Unicode Support”.
要计算用于存储特定CHAR、VARCHAR或TEXT列值的字节数，必须考虑该列使用的字符集以及该值是否包含多字节字符。特别是，在使用utf8 Unicode字符集时，必须记住并非所有字符都使用相同的字节数。Utf8mb3和utf8mb4字符集可能分别需要每个字符最多3个和4个字节。
VARCHAR, VARBINARY, and the BLOB and TEXT types are variable-length types. For each, the storage requirements depend on these factors:
VARCHAR、VARBINARY、BLOB和TEXT类型都是变长类型。对于每一个，存储要求取决于以下因素:
-
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
-
用于列的字符集，因为有些字符集包含多字节字符
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(255)列可以容纳最大长度为255个字符的字符串。假设列使用latin1字符集(每个字符一个字节)，实际需要的存储是字符串的长度(L)，加上记录字符串长度的一个字节。对于字符串'abcd'， L是4，存储要求是5个字节。如果同一列声明为使用ucs2双字节字符集，则存储需求是10字节:'abcd'的长度是8个字节，列需要两个字节来存储长度，因为最大长度大于255(最多510字节)。
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 8.4.7, “Limits on Table Column Count and Row Size”.
VARCHAR或VARBINARY列中可以存储的有效最大字节数受最大行大小65,535字节的限制，这是所有列共享的。对于存储多字节字符的VARCHAR列，有效最大字符数更少。例如，utf8mb4字符每个字符最多需要4个字节，因此使用utf8mb4字符集的VARCHAR列可以声明为最大16,383个字符。
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.
InnoDB将长度大于等于768字节的固定长度字段编码为变长字段，可以在页外存储。例如，如果字符集的最大字节长度大于3，那么CHAR(255)列可以超过768字节，utf8mb4也是如此。
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).
NDB存储引擎支持可变宽度列。这意味着NDB Cluster表中的VARCHAR列需要与任何其他存储引擎相同的存储容量，但这些值是4字节对齐的。因此，使用latin1字符集存储在VARCHAR(50)列中的字符串'abcd'需要8个字节(而不是MyISAM表中相同列值的5个字节)。
TEXT and BLOB columns are implemented differently in NDB; each row in a TEXT column is made up of two separate parts. One of these is of fixed size (256 bytes), 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 table. The rows in this second table are always 2000 bytes long. 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).
TEXT和BLOB列在NDB中实现的方式不同;TEXT列中的每一行都由两个独立的部分组成。其中一个是固定大小的(256字节)，实际上存储在原始表中。另一个包含任何超过256字节的数据，存储在隐藏表中。第二个表中的行总是2000字节长。这意味着如果size <= 256(其中size表示行的大小)，TEXT列的大小为256;否则，大小为256 + size + (2000 × (size−256)% 2000)。
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 11.3.5, “The ENUM Type”.
枚举对象的大小由不同枚举值的数量决定。一个字节用于最多255个可能值的枚举。两个字节用于具有256到65,535个可能值的枚举。参见11.3.5节，“ENUM类型”。
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 SET can have a maximum of 64 members. See Section 11.3.6, “The SET Type”.N+7)/8
SET对象的大小由不同的SET成员的数量决定。如果设置的大小为N，则占用(N+7)/8字节，四舍五入可达1,2,3,4或8字节。一个SET最多可以有64个成员。参见11.3.6节“SET类型”。
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.
MySQL使用4个字节存储几何值，以表示SRID，然后是值的WKB表示。函数的作用是:返回值存储所需的字节空间。
For descriptions of WKB and internal storage formats for spatial values, see Section 11.4.3, “Supported Spatial Data Formats”.
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.
一般来说，JSON列的存储需求与LONGBLOB或LONGTEXT列大致相同;也就是说，JSON文档所消耗的空间与存储在这些类型之一的列中的文档字符串表示的空间大致相同。但是，存储在JSON文档中的各个值的二进制编码(包括查找所需的元数据和字典)带来了开销。例如，存储在JSON文档中的字符串需要4到10字节的额外存储，这取决于字符串的长度和存储它的对象或数组的大小。
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对存储在JSON列中的任何JSON文档的大小都有限制，不能大于max_allowed_packet的值。
扫一扫
1605
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
