ITPub博客

首页 > 数据库 > MySQL > Row size too large (> 8126)解决办法

Row size too large (> 8126)解决办法

原创 MySQL 作者:始于脚下 时间:2020-09-21 15:10:46 0 删除 编辑

在MySQL5.7及以后版本中,在创建表、更改表等操作时,报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.错误。如下所示:

mysql> SHOW VARIABLES LIKE 'innodb_strict_mode';

+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| innodb_strict_mode | ON |
+--------------------+-------+
1 row in set (0.01 sec)

mysql> CREATE TABLE 'my_data' (
-> 'id' int(11) NOT NULL AUTO_INCREMENT,
-> 'mid' int(11) NOT NULL DEFAULT '1',
-> 'oid' bigint(20) NOT NULL,
...
-> 'txt' varchar(4) DEFAULT '-1',
-> PRIMARY KEY ('id'),
-> UNIQUE KEY 'count' ('app_count'),
-> UNIQUE KEY 'Email' ('txtEmail'),
-> KEY 'uid' ('uid') USING BTREE,
-> KEY 'idx_start_date' ('start_date')
-> ) ENGINE=InnoDB AUTO_INCREMENT=5258 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
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.

mysql> SHOW TABLES;
Empty set (0.00 sec)

可以通过临时设置innodb_strict_mode为OFF,然后使报错信息以警告的方式提示,而不是直接以ERROR的方式中断表的创建,如下所示。

mysql> SET SESSION innodb_strict_mode = OFF;
Query OK, 0 rows affected (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARA' at line 1
mysql> SHOW VARIABLES LIKE 'innodb_strict_mode';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| innodb_strict_mode | OFF |
+--------------------+-------+
1 row in set (0.01 sec)

mysql> mysql> CREATE TABLE 'my_data' (
-> 'id' int(11) NOT NULL AUTO_INCREMENT,
-> 'mid' int(11) NOT NULL DEFAULT '1',
-> 'oid' bigint(20) NOT NULL,
...
-> 'txt' varchar(4) DEFAULT '-1',
-> PRIMARY KEY ('id'),
-> UNIQUE KEY 'count' ('app_count'),
-> UNIQUE KEY 'Email' ('txtEmail'),
-> KEY 'uid' ('uid') USING BTREE,
-> KEY 'idx_start_date' ('start_date')
-> ) ENGINE=InnoDB AUTO_INCREMENT=5258 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
->
-> ;
Query OK, 0 rows affected, 1 warning (0.12 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 139 | 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. |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW TABLES;
+--------------------+
| Tables_in_test |
+--------------------+
| my_data |
+--------------------+
1 row in set (0.00 sec)

mysql>

也可以通过更改表的存储引擎和存储方式来临时解决,最好还是调整表的设计,满足数据库要求的限制条件。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31403259/viewspace-2722872/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
拥有多年电信行业Oracle数据库管理经验,拥有Oracle OCP、OCM证书,擅长各个场景下的数据库备份与恢复、性能分析管理、上线规划及部署实施、架构设计、自动化运维等。

注册时间:2016-10-18

  • 博文量
    72
  • 访问量
    110530