在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>
也可以通过更改表的存储引擎和存储方式来临时解决,最好还是调整表的设计,满足数据库要求的限制条件。
本文由 @飞天狗[Vip] 发布于 职涯宝 ,未经作者许可,禁止转载,欢迎您分享文章