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

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

MySQL在表的列数和行大小方面有一些限制，本文将对这些限制进行详细解释。

列数限制

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

1. 表的最大行大小限制了列的数量（和可能的大小），因为所有列的总长度不能超过这个大小。

2. 单个列的存储要求限制了可以容纳在给定最大行大小内的列数。某些数据类型的存储要求取决于诸如存储引擎、存储格式和字符集等因素。

3. 存储引擎可能会施加额外的限制，限制表列数。例如，InnoDB对每个表有1017列的限制。而MyISAM引擎则允许大约5000列。

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

行大小限制

给定表的最大行大小由几个因素决定：

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

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

3. 如果包含可变长度列的行超过InnoDB最大行大小，则InnoDB会选择将可变长度列存储到外部页面，直到行符合InnoDB行大小限制。存储在外部的可变长度列的数据量因行格式而异。

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

举例说明

示例1：行大小超限

考虑以下尝试在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;

此命令将因为行大小超过MySQL的最大限制而失败，报错信息如下：

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

示例2：改变列类型以适应行大小限制

将一些列改为TEXT类型，可以避免65,535字节行大小限制，因为BLOB和TEXT列的存储是分开的，每个只占用9到12字节。示例如下：

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;

此表创建成功，显示如何通过调整列类型来符合行大小限制。

示例3：存储可变长度数据的优化

对于InnoDB表，可变长度数据超过最大行大小时，InnoDB会将部分数据存储到外部页面。例如：

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;

由于TEXT列的外部存储，这个表创建也是成功的，表明InnoDB对可变长度列有特别的存储策略，可以有效利用空间。

示例4：列长和可存储数据的关系

创建包含最大长度VARCHAR列的表：

CREATE TABLE t2 (
  c1 VARCHAR(65533) NOT NULL
) ENGINE = InnoDB CHARACTER SET latin1;

当尝试创建包含VARCHAR(65535)的列时，会因为超出行大小而失败，因为VARCHAR需要额外的字节来存储长度信息。调整列长度至65533或更少，使得表创建成功。

示例5：CHAR列和行大小限制

尝试在InnoDB表中创建包含多个CHAR(255)列的表：

CREATE TABLE t4 (
  c1 CHAR(255), c2 CHAR(255), c3 CHAR(255),
  ... (多达33个CHAR(255)列) ...
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET latin1;

此尝试将失败，因为CHAR(255)列使得总行大小超过了16KB的InnoDB页限制。这个例子表明，即使单个CHAR(255)只占用255字节，多列的累积也可能导致总行大小超限。

参考链接

• MySQL官方文档：MySQL Documentation
• 关于InnoDB行大小限制：Limits on Table Column Count and Row Size