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个(包括分区表);可以很明显看出这个表的设计就是根据业务的需求开始垂直扩展,其实可以拆分出一个逻辑表,逻辑数据很容易持续扩展,而不是在字段层面来不断扩展。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2017-11-03

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏沃趣科技

内存分配统计视图 | 全方位认识 sys 系统库

在上一篇《按 file 分组统计视图 | 全方位认识 sys 系统库》中,我们介绍了sys 系统库中按 file 分组统计的视图,本期的内容将为大家介绍内存事件...

31800
来自专栏菜鸟致敬

提供免费MySQL数据库学习

最近买了一个服务器,放着也没有什么作用,于是搭建了MySQL 提供大家学习数据使用吧,想知道数据的用户信息请在后台回复MySQL 如果大家有其他想搭建的数据库环...

67890
来自专栏极客慕白的成长之路

MySQL的实战系列:大字段如何优化

除特别注明外,本站所有文章均为慕白博客原创,转载请注明出处来自https://geekmubai.com/programming/747.html

61630
来自专栏乐沙弥的世界

使用 DBMS_PROFILER 定位 PL/SQL 瓶颈代码

      对于SQL的优化,可以借助于SQL tuning advisor实现自动SQL优化与调整。而对于PL/SQL代码而言,既有SQL代码,又有PL/SQ...

8210
来自专栏日常学python

情人节福利,手把手教你学会mysql语句操作

这是我的第八篇原创文章 上篇文章简单说了下数据库,并教了怎样安装mysql工具和可视化工具,不知道你现在安装好了没?那今天我们继续说mysql,今天说下mysq...

392140
来自专栏Danny的专栏

【MyBatis框架点滴】——mybatis插入数据返回主键(mysql、oracle)

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/huyuyang6688/article/...

13720
来自专栏程序猿

Mssql高级注入笔记

暴库特殊技巧::%5c='\' 或者把/和\ 修改%5提交 and 0<>(select count(*) from master.dbo.sysdatabas...

38770
来自专栏乐沙弥的世界

MySQL数据类型 -- 日期时间型

版权声明:本文为博主原创文章,欢迎扩散,扩散请务必注明出处。 https://blog.csdn.net/robinson_0612/art...

18020
来自专栏用户2442861的专栏

关于sql中索引的优缺点(面试常考)

   原文       http://blog.csdn.net/dinglang_2009/article/details/5951428

23010
来自专栏琯琯博客

MySQL 查询优化

257110

扫码关注云+社区

领取腾讯云代金券