[mysql] 行溢出数据

博客首页 » mysql 行溢出数据

发布于 21 Feb 2015 15:24
标签 blog
MySQL中行在Page中存储不下的时候,可以接着存放到Uncompressed BLOB Page中。这称为行溢出。

  • 行长度限制
    • set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
    • mysql> create table test(a varchar(65535)) charset=latin1 engine=innodb;

ERROR 1118 (42000): 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
* mysql> create table test(a varchar(65535)) charset=gbk engine=innodb;
ERROR 1074 (42000): Column length too big for column 'a' (max = 32767); use BLOB or TEXT instead
* mysql> create table test(a varchar(65535)) charset=utf8 engine=innodb;
ERROR 1074 (42000): Column length too big for column 'a' (max = 21845); use BLOB or TEXT instead
* mysql> create table test(a varchar(22000), b varchar(22000), c varchar(22000)) charset=latin1 engine=innodb;
* ERROR 1118 (42000): 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

  • 制作行溢出
    • create table test(a varchar(65532)) charset=latin1 engine=innodb;

insert into test select repeat('a', 65532);

  • 阈值为8098bytes,保证一个页中放两个row,保持B+树
  • 溢出放在Uncompressed BLOB Page

本页面的文字允许在知识共享 署名-相同方式共享 3.0协议和GNU自由文档许可证下修改和再使用,仅有一个特殊要求,请用链接方式注明文章引用出处及作者。请协助维护作者合法权益。


系列文章

文章列表

  • mysql 行溢出数据

这篇文章对你有帮助吗,投个票吧?

rating: 0+x

留下你的评论

Add a New Comment
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License