MySQL 5.7-8.4.7 Limits on Table Column Count and Row Size（对表列数和行大小的限制）

最新推荐文章于 2024-03-14 19:58:54 发布

KING BOMB

最新推荐文章于 2024-03-14 19:58:54 发布

阅读量501

点赞数

分类专栏： Mysql 文章标签： mysql 数据库 database

原文链接：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html

版权

Mysql 专栏收录该内容

142 篇文章 2 订阅

订阅专栏

This section describes limits on the number of columns in tables and the size of individual rows.

本节描述表中列数和单个行大小的限制。

Column Count Limits

列数限制

MySQL has hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact column limit depends on several factors:

MySQL对每个表有4096列的硬限制，但是对于一个给定的表，有效的最大值可能要小一些。确切的列数限制取决于以下几个因素:

The maximum row size for a table constrains the number (and possibly size) of columns because the total length of all columns cannot exceed this size. See Row Size Limits.
表的最大行大小限制了列的数量(可能还有大小)，因为所有列的总长度不能超过这个大小。
The storage requirements of individual columns constrain the number of columns that fit within a given maximum row size. Storage requirements for some data types depend on factors such as storage engine, storage format, and character set. See Section 11.7, “Data Type Storage Requirements”.
单个列的存储需求限制了适合给定最大行大小的列的数量。某些数据类型的存储需求取决于存储引擎、存储格式和字符集等因素。
Storage engines may impose additional restrictions that limit table column count. For example, InnoDB has a limit of 1017 columns per table. See Section 14.23, “InnoDB Limits”. For information about other storage engines, see Chapter 15, Alternative Storage Engines.
存储引擎可能会施加额外的限制来限制表的列数。例如，InnoDB限制每个表有1017列。
Each table has an .frm file that contains the table definition. The definition affects the content of this file in ways that may affect the number of columns permitted in the table. See Limits Imposed by .frm File Structure.
每个表都有一个.frm文件，其中包含表定义。该定义以可能影响表中允许的列数的方式影响该文件的内容。

Row Size Limits

行大小限制

The maximum row size for a given table is determined by several factors:

给定表的最大行大小由以下几个因素决定:

The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, even if the storage engine is capable of supporting larger rows. BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row.

MySQL表的内部表示的最大行大小限制是65,535字节，即使存储引擎能够支持更大的行。BLOB和TEXT列只对行大小限制贡献9到12个字节，因为它们的内容与行的其余部分分开存储。

The maximum row size for an InnoDB table, which applies to data stored locally within a database page, is slightly less than half a page for 4KB, 8KB, 16KB, and 32KB innodb_page_size settings. For example, the maximum row size is slightly less than 8KB for the default 16KB InnoDB page size. For 64KB pages, the maximum row size is slightly less than 16KB.

See Section 14.23, “InnoDB Limits”.

对于InnoDB表(适用于本地存储在数据库页中的数据)，对于4KB、8KB、16KB和32KB的innodb_page_size设置，最大行大小略小于半页。例如，对于默认的16KB InnoDB页面大小，最大行大小略小于8KB。对于64KB的页面，最大行大小略小于16KB。

If a row containing variable-length columns exceeds the InnoDB maximum row size, InnoDB selects variable-length columns for external off-page storage until the row fits within the InnoDB row size limit.

如果一个变长列的行超过了InnoDB的最大行大小，InnoDB就会选择变长列进行外部的页外存储，直到该行符合InnoDB的行大小限制。

The amount of data stored locally for variable-length columns that are stored off-page differs by row format.

对于在页外存储的变长列，本地存储的数据量因行格式不同而不同。

For more information, see Section 14.11, “InnoDB Row Formats”.

Different storage formats use different amounts of page header and trailer data, which affects the amount of storage available for rows.

不同的存储格式使用不同数量的页眉和页尾数据，这影响行可用的存储量。

For information about InnoDB row formats, see Section 14.11, “InnoDB Row Formats”.
For information about MyISAM storage formats, see Section 15.2.3, “MyISAM Table Storage Formats”.

Row Size Limit Examples

行大小限制示例

The MySQL maximum row size limit of 65,535 bytes is demonstrated in the following InnoDB and MyISAM examples. The limit is enforced regardless of storage engine, even though the storage engine may be capable of supporting larger rows.

MySQL的最大行大小限制为65,535字节，如下InnoDB和MyISAM示例所示。无论存储引擎如何，都会强制执行该限制，即使存储引擎可能能够支持更大的行。

In the following MyISAM example, changing a column to TEXT avoids the 65,535-byte row size limit and permits the operation to succeed because BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size.

在下面的MyISAM示例中，将列更改为TEXT避免了65,535字节的行大小限制，并允许操作成功，因为BLOB和TEXT列只对行大小贡献9到12个字节。

The operation succeeds for an InnoDB table because changing a column to TEXT avoids the MySQL 65,535-byte row size limit, and InnoDB off-page storage of variable-length columns avoids the InnoDB row size limit.

对于InnoDB表，这个操作是成功的，因为将一个列更改为TEXT可以避免MySQL的65,535字节的行大小限制，而InnoDB的变长列的页外存储可以避免InnoDB的行大小限制。

Storage for variable-length columns includes length bytes, which are counted toward the row size. For example, a VARCHAR(255) CHARACTER SET utf8mb3 column takes two bytes to store the length of the value, so each value can take up to 767 bytes.

可变长度列的存储包括长度字节，它被计算到行大小中。例如，VARCHAR(255) CHARACTER SET utf8mb3列需要两个字节来存储值的长度，因此每个值最多可以占用767字节。

The statement to create table t1 succeeds because the columns require 32,765 + 2 bytes and 32,766 + 2 bytes, which falls within the maximum row size of 65,535 bytes:

创建表t1的语句成功，因为列需要32,765 + 2字节和32,766 + 2字节，这符合65,535字节的最大行大小:

The statement to create table t2 fails because, although the column length is within the maximum length of 65,535 bytes, two additional bytes are required to record the length, which causes the row size to exceed 65,535 bytes:

创建表t2的语句失败，因为尽管列长度在65,535字节的最大长度内，但需要两个额外的字节来记录长度，这导致行大小超过65,535字节:

Reducing the column length to 65,533 or less permits the statement to succeed.

将列长度减少到65,533或更少将允许语句成功。

For MyISAM tables, NULL columns require additional space in the row to record whether their values are NULL. Each NULL column takes one bit extra, rounded up to the nearest byte.

对于MyISAM表，NULL列需要在行中额外的空间来记录它们的值是否为NULL。每个NULL列多取一位，四舍五入到最近的字节。

The statement to create table t3 fails because MyISAM requires space for NULL columns in addition to the space required for variable-length column length bytes, causing the row size to exceed 65,535 bytes:

创建表t3语句失败，因为MyISAM除了需要变长列长度字节的空间外，还需要空列空间，导致行大小超过65,535字节:

For information about InnoDB NULL column storage, see Section 14.11, “InnoDB Row Formats”.

InnoDB restricts row size (for data stored locally within the database page) to slightly less than half a database page for 4KB, 8KB, 16KB, and 32KB innodb_page_size settings, and to slightly less than 16KB for 64KB pages.

对于4KB、8KB、16KB和32KB innodb_page_size设置，InnoDB限制行大小(对于本地存储在数据库页内的数据)为略小于数据库页的一半，对于64KB的页面为略小于16KB。

The statement to create table t4 fails because the defined columns exceed the row size limit for a 16KB InnoDB page.