>데이터 베이스 >MySQL 튜토리얼 >更新text字段时出现 Row size too large 报错_MySQL

更新text字段时出现 Row size too large 报错_MySQL

WBOY
WBOY원래의
2016-06-01 13:35:49854검색

bitsCN.com

起因:

团购开发报告说更新时出错。 更新SQL如下:
UPDATE table_name d SET d.column_name='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' WHERE d.ID=100976;

 

报错信息如下:
Error Code : 1118Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs
 疑惑:更新字段只涉及 column_name字段,且该字段是TEXT类型。 个人之前理解是:TEXT的内容在 Dynamic的table format下是存在off-page中的,不会占用row size的计算。 Barracuda 对应row_format ( dynamic, compress) ,其中dynamic下text的所有内容都是off-page存放的 (点击查看)Antelope 对应row_format (compact, redundant),其中compact下的text是存786B在row中,超过部分存在off-page而服务器配置是 innodb_file_format = Barracuda照理说所有table用的都是 dynamic 结构。 但是! 原因如下,摘自文档:To preserve compatibility with those prior versions, tables created with the InnoDB Plugin use the prefix format, unless one of ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED is specified (or implied) on the CREATE TABLE command.也就是说,建表时不显示指定 row_format = dynamic ,即使 innodb_file_format = Barracuda 表的row-format还是 compact  所以总结为一句话就是:如果某个表的text字段很多建议建表时加上 row_format = dynamic 当然,回过头来MySQL的报错也是有误导性的,bug库中也对confirm了这个bug(点击查看),并在5.1.61中优化了报错提示。bitsCN.com
성명:
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.