Row size too large (> 8126)解決辦法

在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/，如需轉載，請註明出處，否則將追究法律責任。