MYSQL行与列限制

最新推荐文章于 2024-07-03 03:20:18 发布

原创最新推荐文章于 2024-07-03 03:20:18 发布 · 4.3k 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql

losemyfuture的博客专栏收录该内容

25 篇文章

订阅专栏

本文解析了MySQL中数据表的列数量与行大小限制，包括各存储引擎如InnoDB和MyISAM的具体限制，以及BLOB、TEXT类型对行大小的影响。探讨了列数量、行大小与存储引擎参数的关系。

1、列数量限制

从mysql官网可知每个数据表限制列数量为4096，官网也说了一般比这个更小，取决于以下几个因素：

最大行大小会影响表的列数，因为总的列长度不能超过该大小
存储引擎、字符集、排列方式、存储格式也有一定的影响
功能性的key也会有影响，比如索引就会生成一个隐含的虚拟列，其他也是如此，对列数量也有一定限制

2、行大小限制

mysql内部机制允许每一个表有一个最大的行大小为65535字节，即使有些存储引擎支持比这个更大的行大小；BLOB和TEXT列一般只会有9~12个字节用于行大小，因为他的其他内容和该行不存储在同一个地方
*** InnoDb**存储引擎则不同，他一般只和innodb_page_size参数相关，一般会稍微小于该值的一半，比如，对于16KB页大小，最大行大小<=8KB这也是innodb的默认页大小
如果该行包含可变列而且超过最大行大小，那么innodb将会把那些超过不分放到外部页外存储

3、例子（来源于官网）

1、验证行大小65536

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.15    |
+-----------+
1 row in set (0.01 sec)

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;
1118 - 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;
1118 - 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、BLOB和TEXT

将上述例子其中一列修改为BLOB或者TEXT试试？

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.08 sec)

mysql> CREATE TABLE t1 (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.50 sec)

可以看出即使看起来超过最大行大小（65535）字节，依然能创建成功。对于MyISAM：BLOB/TEXT只会占用9~12字节，而对于Innodb则会使用外部页外存储

3、对于那些可变列存储需要话费额外的长度字节，这也会计算到行大小，例如 VARCHAR(255) CHARACTER SET utf8mb3将会使用2个字节标识该值长度，所以每个值将会达到767字节（255*3（utfmb3占用3字节存储）+2）

因此以下创建语句将会成功，因为他刚好达到65535字节（32765 + 2+32766 + 2）

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.45 sec)

同理下面t2表将会失败

mysql> CREATE TABLE t2
       (c1 VARCHAR(65535) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
1118 - 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 t2
       (c1 VARCHAR(65533) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.36 sec)

4、对于MyISAM存储引擎，NULL值列将会需要额外的空间作为判断该值是否为空，一般是一个bit,四舍五入最近的一个字节中

下面的t3将会创建失败，因为对于NULL列需要额外的空间，将会超过65536字节

mysql> CREATE TABLE t3
       (c1 VARCHAR(32765) NULL, c2 VARCHAR(32766) NULL)
       ENGINE = MyISAM CHARACTER SET latin1;
1118 - 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

5、InnoDB行大小取决于innodb_page_size（默认16KB），一般为该值的一般，但是64KB是，最大行大小<=16KB而不是<=32KB

mysql> select @@innodb_page_size/1024;
+-------------------------+
| @@innodb_page_size/1024 |
+-------------------------+
| 16.0000                 |
+-------------------------+
1 row in set (0.02 sec)

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;
1118 - 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.