【MySQL】表列数和行大小限制详解

EulerBlind

已于 2024-01-23 15:02:49 修改

阅读量3.5k

点赞数 20

分类专栏： MySQL 数据库文章标签： mysql 数据库后端

于 2024-01-23 11:53:09 首次发布

本文链接：https://blog.csdn.net/qq_20623849/article/details/135768067

版权

MySQL 同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

数据库

4 篇文章 0 订阅

订阅专栏

本文详细阐述了MySQL中关于列数和单个行大小的限制，涉及InnoDB和MyISAM存储引擎，以及如何处理超出限制的情况，包括变长列存储、存储格式影响和示例操作。

摘要由CSDN通过智能技术生成

MySQL是一种常用的关系型数据库管理系统，广泛用于Web应用程序的开发和数据存储。在使用MySQL的过程中，了解表格列数和行大小的限制是至关重要的，因为它们直接影响到数据库的设计和性能。本文将深入探讨MySQL中列数和单个行大小的限制。

限制维度

列数量限制

MySQL对每个表设置了硬限制，即每个表最多有4096列，但对于给定的表，实际最大列数可能会更少。确切的列限制取决于多个因素：

表的最大行大小

表的最大行大小限制了列数（可能还包括列的大小），因为所有列的总长度不能超过这个大小。有关更多信息，请参阅行大小限制。

单个列的存储要求

单个列的存储要求会限制适应给定最大行大小的列数。不同数据类型的存储要求取决于存储引擎、存储格式和字符集等因素。

存储引擎的附加限制

存储引擎可能会施加额外的限制，进一步限制表的列数。例如，InnoDB对每个表有一个1017列的限制。

功能键部分

功能键部分（“CREATE INDEX Statement”）被实现为隐藏的虚拟生成的存储列，因此表索引中的每个功能键部分都计入表的总列限制。

行容量限制

给定表的行容量大小由多个因素决定

MySQL表的内部实现

MySQL表的内部实现对行的最大大小设定了一个限制，即使存储引擎能够支持更大的行。BLOB和TEXT列仅对行大小限制贡献了9到12字节，因为它们的内容存储在行的其余部分之外。

InnoDB表的最大行大小

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

超出InnoDB最大行大小的处理

如果包含变长列的行超过了InnoDB最大行大小，InnoDB会选择将变长列存储在页面外，直到行适应InnoDB行大小限制。存储在本地的存储在页面外的可变长度列的数据量因行格式而异。

不同存储格式的影响

不同的存储格式使用不同数量的页面头部和尾部数据，这会影响可用于行的存储量

InnoDB行格式
MyISAM存储格式

限制示例

行大小限制示例

MySQL的最大行大小限制为65,535字节，以下是对InnoDB和MyISAM示例的演示。尽管存储引擎可能支持更大的行，但这个限制是强制执行的，与存储引擎无关。

InnoDB下

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

在InnoDB表中，将列更改为TEXT成功进行的原因是，这样的变更避免了MySQL的65,535字节行大小限制，并且InnoDB对可变长度列的页外存储也规避了InnoDB行大小限制。

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g TEXT(6000)) ENGINE=InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

MyISAM下

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=MyISAM CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

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

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g TEXT(6000)) ENGINE=MyISAM CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

InnoDB变长情况示例

可变长度列的存储包括长度字节，这些字节计入行大小。例如，一个VARCHAR(255) CHARACTER SET utf8mb3列占用两个字节来存储值的长度，因此每个值最多可以占用767字节。

以下是创建表t1的语句，成功的原因是这些列需要32,765 + 2字节和32,766 + 2字节，总大小在65,535字节的最大行大小范围内：

mysql> CREATE TABLE t1
       (c1 VARCHAR(32765) NOT NULL, c2 VARCHAR(32766) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

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

mysql> CREATE TABLE t2
       (c1 VARCHAR(65535) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

减小列的长度至65,533或更低可以使创建表的操作成功。例如：

mysql> CREATE TABLE t2
       (c1 VARCHAR(65533) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.01 sec)

MyISAM变长情况示例

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

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

mysql> CREATE TABLE t3
       (c1 VARCHAR(32765) NULL, c2 VARCHAR(32766) NULL)
       ENGINE = MyISAM CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

行大小限制示例

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

以下是创建表t4的语句，由于定义的列超过了16KB InnoDB页面的行大小限制，因此操作失败：

mysql> CREATE TABLE t4 (
       c1 CHAR(255),c2 CHAR(255),c3 CHAR(255),
       c4 CHAR(255),c5 CHAR(255),c6 CHAR(255),
       c7 CHAR(255),c8 CHAR(255),c9 CHAR(255),
       c10 CHAR(255),c11 CHAR(255),c12 CHAR(255),
       c13 CHAR(255),c14 CHAR(255),c15 CHAR(255),
       c16 CHAR(255),c17 CHAR(255),c18 CHAR(255),
       c19 CHAR(255),c20 CHAR(255),c21 CHAR(255),
       c22 CHAR(255),c23 CHAR(255),c24 CHAR(255),
       c25 CHAR(255),c26 CHAR(255),c27 CHAR(255),
       c28 CHAR(255),c29 CHAR(255),c30 CHAR(255),
       c31 CHAR(255),c32 CHAR(255),c33 CHAR(255)
       ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET latin1;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help.
In current row format, BLOB prefix of 0 bytes is stored inline.