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

2024-06-04 8070阅读

目录

限制维度

列数量限制

表的最大行大小

单个列的存储要求

存储引擎的附加限制

功能键部分

行容量限制

MySQL表的内部实现

InnoDB表的最大行大小

超出InnoDB最大行大小的处理

不同存储格式的影响

限制示例

行大小限制示例

InnoDB下

MyISAM下

InnoDB变长情况示例

MyISAM变长情况示例

行大小限制示例

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.

相关阅读：

1、揭秘，远程扫描VPS端口的实用技巧与操作指南

2、苹果系统VPS使用指南，轻松上手，网速飞跃提升！

3、Mac用户必备，简洁SSH连接VPS操作指南

4、揭秘VPS中转奥秘，数据传输加速技巧全解析！

5、VPS数据库误删紧急应对与恢复手册，实用指南助你迅速恢复数据

    高速稳定云服务器25元起
    免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们，邮箱：ciyunidc@ciyunshuju.com。本站只作为美观性配图使用,无任何非法侵犯第三方意图,一切解释权归图片著作权方,本站不承担任何责任。如有恶意碰瓷者,必当奉陪到底严惩不贷!

    相关阅读

    目录[+]