ITPub博客

首页 > 数据库 > MySQL > mysql中varchar类型最大长度测试

mysql中varchar类型最大长度测试

原创 MySQL 作者:lsq_008 时间:2018-04-20 14:33:39 0 删除 编辑
1.先看字符集为latin1时,每个字符应该是占据一个byte
mysql> create table test(a varchar(65535)) engine=innodb charset=latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

mysql> create table test(a varchar(65533)) engine=innodb charset=latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

mysql> create table test(a varchar(65532)) engine=innodb charset=latin1;
Query OK, 0 rows affected (0.01 sec)

2.再看字符集为GBK时,每个字符应该占据2个byte
mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test(a varchar(32767)) engine=innodb charset=gbk;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

mysql> create table test(a varchar(32766)) engine=innodb charset=gbk;
Query OK, 0 rows affected (0.00 sec)

3.当字符集为UTF8时,每个字符占据了3个byte
mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test(a varchar(65535)) engine=innodb charset=utf8;
ERROR 1074 (42000): Column length too big for column 'a' (max = 21845); use BLOB or TEXT instead

mysql> create table test(a varchar(21845)) engine=innodb charset=utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

mysql> create table test(a varchar(21844)) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.00 sec)


最后,如果表中所有varchar类型的列长度总和超过了65535,创建表时也会报错
mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

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. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs




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

请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    324
  • 访问量
    1231494