MySQL插入单行数据较大时报Row size too large错误 解决方案

mysql 版本 5.5

向目的MySQL写入数据时,单行数据较大,遇到插入失败,报错信息如下:
Error ‘Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.‘ on query. ......

 

 

解决:

1. 先查看

show GLOBAL VARIABLES LIKE ‘%file_format%‘;  
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_file_format       | Antelope |
| innodb_file_format_check | ON       |
| innodb_file_format_max   | Antelope |
+--------------------------+----------+

mysql> show variables like ‘%per_table%‘;
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set (0.03 sec)


2. 修改参数: my.cf中在[mysqld] 加入

innodb_file_format = Barracuda
innodb_file_per_table = 1 

    检查修改后的结果:

mysql> show variables like ‘%per_table%‘;
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)


mysql> show GLOBAL VARIABLES LIKE ‘%file_format%‘;
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
+--------------------------+-----------+
3 rows in set (0.00 sec)

3. 修改表

Alter table <table_name> engine=innodb ROW_FORMAT=DYNAMIC;


MySQL插入单行数据较大时报Row size too large错误 解决方案

上一篇:数据库原理课堂笔记20/5/9


下一篇:万盛酒店餐饮管理系统(SpringBoot,SSM,MySQL )