MySQL

  • MySQL表具有65,535字节的最大行大小限制，即使存储引擎能够支持更大的行也是如此。在MySQL的设定中，单行数据最大能存储65535byte的数据(注意是byte，而不是字符)。
  • 对于默认的16KB InnoDB页大小，最大行大小略小于8KB 。对于64KB页，最大行大小略小于16KB。如果包含可变长度列(例如：text)的InnoDB 行超过最大行大小，InnoDB选择可变长度列进行页外存储。

MySQL对表设计时候，肯定有小伙伴遭遇表字段一多，而设计又烂的话，会发现表无法创建，错误如下：

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

更改列为 TEXT可以避免MySQL 65,535字节的行大小限制，而InnoDB 变长列的页外存储可以避免 InnoDB行大小限制。

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

变长限制

可变列的长度要计算字符集

  • 设置允许 NULL 需要占用1个字节
  • 设置为变长（varchar）需要占用2个字节，char 由于定长不占

实际长度为 32765+2[变成加2字节] 和 32766+2 ，正好是65535，没有超出限制

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

创建表的语句t2失败是因为，尽管列长度在最大长度65,535字节之内，但仍需要两个额外的字节来记录该长度，这会导致行大小超过65,535字节

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

设置为允许 null

实际长度为 32765+2[变成加2字节]+1[允许null加1字节] 和 32766+2 ，超出65535限制了

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

字符集差异

设置字符集为 utf8 ，如下语句由于 21845 * 3 + 2【变长需要加2字节】= 65537 > 65535，因此创建失败

  1. mysql> CREATE  TABLE t1 (c1 VARCHAR(21845)  NOT  NULL)  ENGINE  = InnoDB CHARACTER  SET utf8;
  2. 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

由于设置为 21844，而字符集为 utf8 ，因此 21844 * 3 + 2 = 65534，因此能正常创建

  1. mysql> CREATE  TABLE t1 (c1 VARCHAR(21844)  NOT  NULL)  ENGINE  = InnoDB CHARACTER  SET utf8;
  2. Query OK, 0 rows affected (0.09 sec)

utf8mb4同理，不过是 * 4

单行8126限制

  • innodb 一个 page 至少需要存放2行，单行有 8126的限制
  • 若t5表如果写入全为255字节的数据同样会报8126错误 ```sql

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.

CREATE TABLE t5 ( c1 VARCHAR(255),c2 VARCHAR(255),c3 VARCHAR(255), c4 VARCHAR(255),c5 VARCHAR(255),c6 VARCHAR(255), c7 VARCHAR(255),c8 VARCHAR(255),c9 VARCHAR(255), c10 VARCHAR(255),c11 VARCHAR(255),c12 VARCHAR(255), c13 VARCHAR(255),c14 VARCHAR(255),c15 VARCHAR(255), c16 VARCHAR(255),c17 VARCHAR(255),c18 VARCHAR(255), c19 VARCHAR(255),c20 VARCHAR(255),c21 VARCHAR(255), c22 VARCHAR(255),c23 VARCHAR(255),c24 VARCHAR(255), c25 VARCHAR(255),c26 VARCHAR(255),c27 VARCHAR(255), c28 VARCHAR(255),c29 VARCHAR(255),c30 VARCHAR(255), c31 VARCHAR(255),c32 VARCHAR(255),c33 VARCHAR(255) ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET latin1; Query OK, 0 rows affected (0.03 sec) ```