MySQL创建表失败的问题

今天有一个朋友问我一个MySQL的建表问题，问题的现象是创建表失败，根据他的反馈，问题比较奇怪，

CREATE TABLE XXX

..此处省略260多个字段

`xxxxIsAllowIn` varchar(4) COLLATE utf8_bin DEFAULT NULL COMMENT 'xx是否准入(是，否)',

`xxxxIsAllowIn` varchar(30) COLLATE utf8_bin DEFAULT NULL COMMENT '理财-准入',

PRIMARY KEY (`SERIALNO`),

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='???????”3èˉ·?????ˉ';

是的，你没有看错，还有乱码，根据朋友反馈的现象是在生产环境可以创建成功，但是测试环境创建失败。

报错信息为：

ERROR 1118 (42000): 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.

我把文本拷贝到本地，想复现，结果因为乱码直接执行失败，对于这种情况，还是同事帮我做了下问题过滤，采用如下的方式即可把注释删除。

cat a.sql |sed 's/COMMENT'.*'/,/g'

所以省事了不少，我就来继续分析这个问题。一般来说这个错误看起来是单行的数据超出限制了，因为MySQL里面每行的数据有一个65535的限制，想必是这个原因吧。

但是朋友反馈是没有超出这个限制的，根据里面的字符类型做计算，发现确实没有达到65535.

所以这个问题就微妙起来，我们来说说几种解决方式。

解决方式1：

修改存储引擎，设置为myisam

...

KEY `idx_customerName` (`CUSTOMERNAME`)

) ENGINE=myisam DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

"c.sql" 276L, 16070C written

mysql> source c.sql

Query OK, 0 rows affected (0.07 sec)

MyISAM有3种行存储格式：fixed/dynamic/compressed,InnoDB在这个基础上增加了Barracuda的格式。

5.7中的默认参数设置如下：

mysql> show variables like '%format';

+---------------------------+-------------------+

| Variable_name | Value |

+---------------------------+-------------------+

| binlog_format | ROW |

| date_format | %Y-%m-%d |

| datetime_format | %Y-%m-%d %H:%i:%s |

| default_week_format | 0 |

| innodb_default_row_format | dynamic |

| innodb_file_format | Barracuda |

| time_format | %H:%i:%s |

+---------------------------+-------------------+

7 rows in set (0.00 sec)

所以现在的问题差异就在于MyISAM和InnoDB。

共享表空间的格式为Antelope,在5.5中默认就是这个格式。

解决方式2;

这个问题我做了一些测试。对比了字符集，row_format的设置。

) ENGINE=innodb row_format=dynamic DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

"c.sql" 276L, 16090C written

mysql> source c.sql

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB p

) ENGINE=innodb row_format=compact DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

"c.sql" 276L, 16090C written

mysql> source c.sql

ERROR 1118 (42000): 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.

) ENGINE=innodb DEFAULT CHARSET=latin1;

"c.sql" 276L, 16056C written

mysql> source c.sql

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.

得到的一个初步结论就是先设置innodb_strict_mode为off,默认5.7是开启的,当然从MySQL5.5版本开始，可以开启InnoDB严格检查模式，如果采用了页数据压缩功能后，建议是开启该功能。在创建表，更改表和创建索引时，如果写法有错误，不会有警告信息，而是直接抛出错误，这样就可直接将问题扼杀在摇篮里。

当然这个里的这个问题现象确实比较纠结。

解决方法3：

从表结构设计入手，尽可能拆分这个表的逻辑，把它拆分为多个表。一个表的字段数尽可能不要太多。数据库、表数量尽可能少；数据库一般不超过50个，每个数据库下，数据表数量一般不超过500个(包括分区表)；可以很明显看出这个表的设计就是根据业务的需求开始垂直扩展，其实可以拆分出一个逻辑表，逻辑数据很容易持续扩展，而不是在字段层面来不断扩展。

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

转载于:http://blog.itpub.net/23718752/viewspace-2146961/