mysql创建表时报8126_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>

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

http://blog.itpub.net/blog/post/2722872/