mysql 限制表的记录数 mysql表列数的限制

转载

mob64ca140a1f7c 2023-08-07 10:46:53

文章标签 mysql 限制表的记录数 mysql 存储引擎 sed 文章分类 MySQL 数据库

文章目录

列数限制
行大小限制

行大小限制示例

官方文档地址：

8.4.7 Limits on Table Column Count and Row Size

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

列数限制

MySQL 有每个表最多4096列的硬性限制，但是对于一个给定的表，有效的最大列数可能更少。一个表中的最大列数取决于几个因素：

表的最大的行大小限制了列的数量（也可能会限制到列的大小），因为一个表中的所有列的总长度不能超过这个大小。

（译者注：最大的行大小是确定的，每个列都会占用一部分大小，所以总列数肯定会被最大的行大小限制的）

单个列的存储需求限制了符合给定的最大行大小的列数。某些数据类型的存储要求取决于存储引擎、存储格式和字符集等因素。

（译者注：最大的行大小是确定的，单个列的长度大了，则总列数肯定会变小的）

存储引擎可能会施加额外的限制来限制表的列数。例如，InnoDB每个表限制为1017列，参见 15.22 InnoDB 限制。有关其他存储引擎的信息
功能键部分被实现为隐藏的虚拟生成的存储列，因此表索引中的每个功能键部分都将计入表的总列数限制。

行大小限制

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

MySQL 表的内部表示有一个最大的行大小限制为65,535字节，即使存储引擎能够支持更大的行。BLOB和TEXT列仅为行大小限制贡献9到12个字节，因为它们的内容与行其余部分分开存储。
InnoDB表的最大的行大小，取决于本地存储在数据库页中的数据，对于4KB、8KB、16KB和32KB的innodb_page_size设置，其最大的行大小略小于半页。例如，对于默认的16KBInnoDB页大小，最大的行大小略小于8KB。对于64KB页，最大的行大小略小于16KB。参见 15.22 InnoDB 限制。

如果一个行包含的变长列超过了InnoDB最大的行大小，InnoDB会选择变长列作为外部页外存储，直到行符合InnoDB的行大小限制。在页外存储的变长列的本地存储的数据量因行格式不同而不同。要了解更多信息，请参见 15.10 InnoDB 行格式。
不同的存储格式使用不同数量的页头和页尾数据，这将影响行可用的存储量。

关于InnoDB行格式的信息，请参见 15.10 InnoDB 行格式。
关于MyISAM存储格式的信息，请参见 16.2.3 MyISAM 表存储格式。

行大小限制示例

（1）MySQL 的最大行大小限制为65,535字节，如下InnoDB和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=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

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表操作成功，因为修改一个列为TEXT避免了 MySQL65,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)

（2）可变长度列的存储包括长度字节，将计入行大小。例如，VARCHAR(255) CHARACTER SET utf8mb3列需要两个字节来存储值的长度，因此每个值最多可以占用767字节。（utf8是utf8mb3的别名）(255 * 3 + 2 = 767)。

创建表t1的语句成功，因为列需要32,765 + 2字节和32,766 + 2字节，这属于最大的行大小65,535字节：（32,765 + 2 + 32,766 + 2 = 65535）

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的语句失败的原因是，尽管列的长度在最大长度65535字节内，但还需要两个额外的字节来记录长度，这导致行大小超过了65535字节：

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)

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

创建表t3的语句失败，因为MyISAM除了需要变长列长度字节的空间外，还需要NULL列的空间，导致行大小超过了65535个字节：（32765 + 2 + 1 + 32766 + 2 + 1 = 65537）

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 NULL列存储的信息，请参见 15.10 InnoDB 行格式。

（4）对于4KB、8KB、16KB和32KB的innodb_page_size设置，InnoDB将行大小（对于本地存储在数据库页中的数据）限制为略低于半个数据库页，对于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.