MySQL 下因字段过多导致错误提示 “Row size too large ... not counting BLOBs, is 65535 ... TEXT or BLOBs” 的解决办法
因为标题的字数有限,所以在这里重新贴一下完整的错误提示,以便遇到此类问题的朋友能搜索到本文。
错误提示如下:
SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
我之所以会遇到这个错误提示,是因为最近的工作中涉及到一个来自第三方提供的数据源,为了存储它需要一个含有百余个字段的数据表。这里不讨论为什么要有这样的表结构存在,只分享对于这个首次遭遇的问题我是如何解决的。鉴于本文发布后很多朋友在留言中提问为什么不能分表,简单回答并罗列原因如下:
- 这100多个字段已经是从第三方数据源的400多个字段中筛选之后的了,先天不足如此,大家体谅。
- 这100多个字段在实际应用中基本上会一次性都要输出给客户端,所以在这种情况下分表的意义个人觉得不大,只会导致额外的跨表联合查询。
- 不能,也没机会采用 mongoDb 等其它替代方案,因为我只是一个打工仔,所谓“架构”上的事轮不到我发言。
综上所述,这100多个字段的数据表我还得好好的去伺候和打理,但还是感谢大家的热心指点,谢谢!
其实这个问题的起因在错误提示中已经说的很清楚了,之所以没看懂并不是因为英文的问题,而是对 MySql 下的相关限制不了解。这就好像一个不懂篮球规则的人被裁判告知“三秒违例”时,虽然能听懂这4个字,但却不明白自己为什么错了一样。
简单的说就是,在 MySql 的表中,所有字段的长度的总和不能超过65535个字节。至于所谓的“长度”,可以粗暴的理解为:
`addr` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '地址',
`cross_st` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '交叉路口',
`srchst_num` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT '门牌号码',
`st` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '街道名',
看到类似 varchar(255) 这种字段长度的定义了吗?说的“长度”就是它们。而且,255仅仅是指该字段允许存储255个字符,但实际应用时,一个字符要占3个字节的(UTF8编码下),这样算来,一个 varchar(255) 的字段就要占用 255 x 3 = 765个字节。回到我处的背景下,一个数据表中有百余个字段,这些字段又大多是文本内容,而我最初又在考虑冗余和偷懒的情况下将许多字段都定义的比较大,积累下来就触发了这个错误提示。
解决办法有两种:
办法1:谨慎合理型
在定义字段时,认真考虑和估算每个字段应有的长度和是否使用了恰当的类型。比如明明只为了储存性别的“男/女”,就别搞一个 varchar(255) 的字段来存储了。
办法2:粗暴野蛮型
在错误提示中提到了,虽然有65535这个限制,但是 TEXT 和 BLOBs 这2种类型的字段是不被计算在内的。所以,你大可以把所有需要存储文本内容的字段都设置为这2种类型。那叫一个霸气洒脱啊!
以上就是我的经验分享,因为自己也是一个老菜鸟,所以用词很不恰当也很粗鄙,仅供大家懵懂的看一下。而我在解决该问题时,参考了以下内容,请大家借鉴:
愿大家永远没机会去和一个有100多个字段的数据表打交道。
推荐文章: