InnoDB表行长度超限问题的处理和思考

问题描述：

今天在在做MySQL数据库字符集升级测试，采用数据导出、修改表结构文件字符集为utf8mb4、再导入数据的方式，在导入表结构时遇到以下错误：

ERROR 1118 (42000) at line 38436: 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限制的65535字节，建议把表中的某些列改为TEXT或BLOB。经检查，这张业务表中定义了多个varchar2(4000)列，在utf8字符集时，长度在65535字节内，但是当我们修改表的字符集为utf8mb4时，一行总的数据长度超过了65535字节，所以报以上错误。

一、创建实验环境

1.配置信息:

CPU:2核8C
内存:16G
操作系统：RHEL7.4
磁盘：200G SATA XFS
MySQL:5.7.30

2.创建测试表t1,并插入测试数据

create table t1(
    id int unsigned NOT NULL AUTO_INCREMENT,
    col1 varchar(4000),
    col2 varchar(4000),
    col3 varchar(4000),
    col4 varchar(4000),
    col5 varchar(4000),
    PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into t1(col1,col2,col3,col4,col5) select ... from ...;

3.修改测试表字符集为utf8mb4

root@mysql3307.sock 23:18:58[wydb]> ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4;
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

这时错误重现了，根据提示，我们分别测试把字段修改为TEXT和BLOB的影响。

二、问题解决

1.修改字段类型为TEXT或BLOB

alter table t1 modify col1 text;

-- 表结构如下：
Create Table: CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col1` text,
  `col2` varchar(4000) DEFAULT NULL,
  `col3` varchar(4000) DEFAULT NULL,
  `col4` varchar(4000) DEFAULT NULL,
  `col5` varchar(4000) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1024 DEFAULT CHARSET=utf8

2.再次修改表字符集

root@mysql3307.sock 23:31:53[wydb]> ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4;
Create Table: CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col1` mediumtext,
  `col2` varchar(4000) DEFAULT NULL,
  `col3` varchar(4000) DEFAULT NULL,
  `col4` varchar(4000) DEFAULT NULL,
  `col5` varchar(4000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `col1` (`col1`(10))
) ENGINE=InnoDB AUTO_INCREMENT=1024 DEFAULT CHARSET=utf8mb4

-- 表字符集修改成功，检查表中列的字符集也修改成功
root@mysql3307.sock 23:35:04[wydb]> select COLUMN_NAME,CHARACTER_SET_NAME from information_schema.COLUMNS where table_name='t1';
+-------------+--------------------+
| COLUMN_NAME | CHARACTER_SET_NAME |
+-------------+--------------------+
| id          | NULL               |
| col1        | utf8mb4            |
| col2        | utf8mb4            |
| col3        | utf8mb4            |
| col4        | utf8mb4            |
| col5        | utf8mb4            |
+-------------+--------------------+
6 rows in set (0.01 sec)

三、修改字段类型对索引影响

通过修改字段类型，解决行长度报错后，对列上已经创建的索引是否有影响，我们通过实验给出答案。

1.根据列的选择性，创建前缀索引

> select count(distinct col1)/count(*) from t1;
+-------------------------------+
| count(distinct col1)/count(*) |
+-------------------------------+
|                        0.9298 |
+-------------------------------+
1 row in set (0.01 sec)

> select count(distinct left(col1,10))/count(*) from t1;
+----------------------------------------+
| count(distinct left(col1,10))/count(*) |
+----------------------------------------+
|                                 0.9038 |
+----------------------------------------+
1 row in set (0.00 sec)
-- 当前缀索引为10个字符时，已经很接近0.9298

> alter table t1 add key (col1(10));

2.修改字段类型前SQL执行计划

root@mysql3307.sock 23:10:19[wydb]> desc select * from t1 where col1 like '品牌%';
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | range | col1          | col1 | 33      | NULL |    3 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

从执行计划上看到，本次查询使用了我们创建的前缀索引，key_len的值也没问题。key_len：10 * 3 + 2(变长) + 1(可为null) = 33

3.修改字段,类型:TEXT,字符集:utf8mb4

再次查看执行计划

root@mysql3307.sock 23:36:10[wydb]> desc select * from t1 where col1 like '品牌%';
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | range | col1          | col1 | 43      | NULL |    3 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

可见，索引使用正常，由于字符集改为utf8mb4，key_len变成了43
key_len：10 * 4 + 2(变长) + 1(可为null) = 43

4.修改字段,类型:BLOB

再次查看执行计划

root@mysql3307.sock 23:39:53[wydb]> alter table t1 modify col1 blob;
Query OK, 655 rows affected (0.05 sec)
Records: 655  Duplicates: 0  Warnings: 0

root@mysql3307.sock 23:40:05[wydb]> desc select * from t1 where col1 like '品牌%';
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | range | col1          | col1 | 13      | NULL |    3 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

这时，虽然索引仍能用到，但key_len变成了13
key_len：10 + 2(变长) + 1(可为null) = 13
因为BLOB存储的是二进制数据，没有字符集概念，索引col1(10)中10为字节数，如果存储中文字符，注意对齐，否则可能乱码。

四、修改字段类型对应用程序的影响

1.以python程序为例，进行验证，编写如下验证程序

# coding:utf-8
import pymysql


if __name__ == '__main__':
    conn = pymysql.connect('10.1.135.129', 'wy', 'orange', 'wydb', 3307)
    cur = conn.cursor()
    sql = "select id,col1,col2,col3,col4,col5 from t1 where col1 like '品牌%'"
    cur.execute(sql)
    rows = cur.fetchall()

    for row in rows:
        print(row)
        print(row[1].decode('utf8'))

2.修改列类型前，python程序查询结果

(208, '品牌', '', 'aima', '', None)
(4, '品牌型号', '', '不锈钢', '', None)
(5, '品牌型号', '', 'HK-12345', '', None)

3.修改列类型为TEXT，python程序查询结果

(208, '品牌', '', 'aima', '', None)
(4, '品牌型号', '', '不锈钢', '', None)
(5, '品牌型号', '', 'HK-12345', '', None)

程序不用任何改动，返回结果正常。

3.修改列类型为BLOB，python程序查询结果

(208, b'\xe5\x93\x81\xe7\x89\x8c', '', 'aima', '', None)
(4, b'\xe5\x93\x81\xe7\x89\x8c\xe5\x9e\x8b\xe5\x8f\xb7', '', '不锈钢', '', None)
(5, b'\xe5\x93\x81\xe7\x89\x8c\xe5\x9e\x8b\xe5\x8f\xb7', '', 'HK-12345', '', None)

可见，中文字符返回的是bytes,应用程序需要转码，把bytes转为utf8，python中使用decode('utf8')函数实现。

(208, b'\xe5\x93\x81\xe7\x89\x8c', '', 'aima', '', None)
品牌
(4, b'\xe5\x93\x81\xe7\x89\x8c\xe5\x9e\x8b\xe5\x8f\xb7', '', '不锈钢', '', None)
品牌型号
(5, b'\xe5\x93\x81\xe7\x89\x8c\xe5\x9e\x8b\xe5\x8f\xb7', '', 'HK-12345', '', None)
品牌型号

五、注意事项

非严格模式时，如果存储超过BLOB或TEXT字段最大长度的值，值被截断，如果被截掉的是非空格字符，会产生一条警告；在严格模式下，直接报错；

BLOB或TEXT字段的列被检索时，不删除尾部空格；

为BLOB或TEXT字段创建索引时，必须指定长度；

BLOB或TEXT字段不能设置默认值；

当排序时只使用该列的前max_sort_length个字节，max_sort_length的默认值是1024。

当你想要使超过max_sort_length的字节有意义，对含长值的BLOB或TEXT列使用GROUP BY或ORDER BY的另一种方式是将列值转换为固定长度的对象。标准方法是使用SUBSTRING函数。

BLOB或TEXT对象的最大大小由其类型确定，但在客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定。你可以通过更改max_allowed_packet变量的值更改消息缓存区的大小，但必须同时修改服务器和客户端程序。
参考文档：MySQL中TEXT与BLOB字段类型的区别

六、结论

  当我们遇到ERROR 1118 (42000)时，将varchar2修改为TEXT，代价最小，索引使用正常，应用程序无需修改。但是TEXT中仍然存储的是字符，受字符集和校对规则影响；
  BLOB字段中存储的是二进制数据，不受字符集影响，可存储任意类型数据，但是应用程序存取数据时需要转换编码，创建索引时指定的长度是字节数；
  具体使用哪种类型，应根据实际业务作合理的选择。

总结

以上是生活随笔为你收集整理的mediumtext 长度_InnoDB表行长度超限问题的处理和思考的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。