8.4.7-列的数量限制和行大小限制

本章节讨论每张表中列的数量的限制和每条记录大小的限制。

  • 列数限制
  • 行大小限制

列数限制 #

mysql限制每张表最多有4096列，但是实际上给定表的列数可能会比这个值小。具体列数上限取决于几个因素：

  • 表中每条记录所占空间的最大值限制了列数的大小。因为所有列的总长度不能超过这个值。见 行大小限制。
  • 单列的存储需求限制了在给定行数据大小上限的情况下的列的数量。一些数据类型的的存储需求取决于多种因素，比如存储引擎，存储格式，字符集等。详情请查阅章节 11.7，“Data Type Storage Requirements”。
  • 存储引擎可能会对列的数量添加额外的限制。例如，InnoDB限制每张表最多1017列。请查阅章节 15.22，“InnoDB 限制”。想了解其它存储引擎，请查阅章节 16，“其它存储引擎”。
  • 被函数处理的字段（见章节 13.1.15，“CREATE INDEX Statement”）被当成一个隐藏的虚拟列处理，所以这些字段也在列数限制之内。

行大小限制。 #

一张表的行记录大小上限取决于几个因素：

  • mysql内部对每条记录大小的限制是65535字节。存储引擎支持更大的行。BLOG和TEXT字段在每行数据大小限制中仅占用9-12个字节，因为数据内容被存储在其它地方。
  • 一张innodb表中，一条记录的最大空间上限，比每个数据页的大小（4KB,8KB,16KB,32KB）的二分之一略小一些。如果innodb引擎，一个page 的大小是16KB，那一个记录占用空间的上限就比8KB略小一些。如果一页的大小是64KB，那一行记录占用空间的上限就比16KB略小一些。 如果一条记录有变长字段，且每条记录占用空间最大值超过了InnoDB中每条记录占用空间上限，innodb就会将变长字段存储到其它存储中，直到每页的大小符合限制。如果有变长字段，那row格式不同，每一列占用空间的大小也不同。详情请查阅章节15.10，“InnoDB Row Format”。
  • 不同的存储格式，页头和页尾占用的空间不同，进而会影响留给数据存储用的空间大小。
  • 想了解InnoDB行记录格式信息，请查阅章节15.10，“InnoDB Row Formats”。
  • 想了解MyISAM存储格式，请查阅章节16.2.3,“MYISAM Table Storage Formats”。

行大小限制的例子 #

  • 下面的例子展示了MySQL的65535字节最大行大小上限。这里没有考虑存储引擎的限制，进款存储引擎可能会支持更大的行。
mysql> create table t (a varchar(10000),b varchar(10000),c varchar(10000),d varchar(10000),e varchar(10000),
f varchar(10000),g varchar(60000)) engine=innodb character set latin1;

执行报错： ERROR 1118 (42000):Row size too large.The maxium row size for the used table type,not counting BLOBs, is 65535.This includes storage over-head,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 over-head,check the manual. You have to change some columns to TEXT or BLOBs.

在下面的MyISAM例子中，将一列的类型改为TEXT避免了65535字节的行大小限制，操作会成功。因为BLOB和TEXT类型只在行大小中占用9-12字节。

msyql>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类型避免了超过65535字节的限制，而且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)

  • 变长字段的存储空间包括存储长度占用的字节数，这杯计算在行大小限制中。例如，一个varchar(255) character set utf8mb3 列占用两个字节来存储大小，所以每个值最多占用767字节。

下面的创建表t1的语句会成功，因为相关列占用的空间是32765+2字节和32766+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字节，但是加上2个字节的存储长度占用的空间后，总量就会超出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

将字段长度减少为65533或者更小后，就会执行成功:

mysql>create table t_v2
    (c1 varchar(65533) not null)
    engine = InnoDB character set latin1;

执行结果: Query OK: 0 rows affected

下面的语句在创建t3时会失败，因为MyISAM在存储NULL字段时需要额外的空间，导致行总长度会超过65535字节：

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 Row Formats”。

  • 在数据页大小为4KB,8KB,16KB,32KB时，InnoDB引擎限制每条记录的大小比页大小的一般略小，在数据页大小为64KB时，每条记录比16KB略小。 下面的语语句在创建表t4时会失败，因为行大小超过了16KB的限制。
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 character set 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.