首先我们要知道,存储的最大长度没一个固定的数值,根据不同的表结构设计有所不同,一般有以下几个限制规则:

1、存储限制
varchar 字段是将实际内容单独存储在聚簇索引之外,内容开头用1到2个字节表示实际长度(长度超过255时需要2个字节),因此最大长度不能超过65535,官方是这么说的:

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.

In contrast to CHAR, VARCHAR values are stored as a one-byte or two-byte length prefix plus data. The length prefix indicates the number of bytes in the value.

A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

2、编码长度限制
字符类型若为 gbk,则个字符最多占2个字节,最大长度不能超过32766; 字符类型若为utf8,则每个字符最多占3个字节,最大长度不能超过21845。 若定义的时候超过上述限制,则varchar字段会被强行转为text类型,并产生warning。

3、行长度限制
导致实际应用中varchar长度限制的是一个行定义的长度。 MySQL要求一个行的定义长度不能超过65535。若定义的表长度超过这个值,则提示 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。
这就是说,比如创建一个表,表结构中有两个varhcar类型字段,那么这两个字段的总长度不能超过65535...

官方说明如下:
Every table has a maximum row size of 65,535 bytes.
This maximum applies to all storage engines, but a given engine might have additional constraints that result in a lower effective maximum row size.

4、控制位大小
MySQL 中的Varchar字符类型还保留了1个字节来留其它控制信息.

明白以上四个存储规则以后,下面通过实例来说明!!!

实例一:若一张表中只有一个字段VARCHAR(N)类型,utf8编码,则N最大值为多少?

如:create table tb_name1(a varchar(N)) default charset=utf8;


则:N最大值=(65535-1-2)/3=21844
  • 减1的原因是实际行存储从第二个字节开始(这个必须减,你懂得.....不懂可以去撞墙^o^....);
  • 减2的原因是varchar头部的2个字节表示长度;
  • 除3的原因是字符编码是utf8.

回到SQL下测试:
MariaDB [opdba]> create table tb_name1(a varchar(21844)) default charset=utf8;
Query OK, 0 rows affected (0.38 sec)

MariaDB [opdba]> drop table tb_name1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [opdba]> create table tb_name1(a varchar(21845)) default charset=utf8;
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


实例二:若一张表中有一个字段VARCHAR(N)类型,并且有其它的字段类型,utf8编码,则N的最大值为多少?

如:create table tb_name2(a int, b char(20), c varchar(N)) default charset=utf8;


则:N最大值=(65535-1-2-4-20*3)/3=21822
  • 减1的原因是实际行存储从第二个字节开始;
  • 减2的原因是varchar头部的2个字节表示长度;  
  • 减4的原因是a字段的int类型占4个字节;
  • 减20*3的原因是char(20)占用60个字节,编码是utf8。

回到sql下测试下:
MariaDB [opdba]> create table tb_name2(a int, b char(20), c varchar(21822)) default charset=utf8;
Query OK, 0 rows affected (0.28 sec)

MariaDB [opdba]> drop table tb_name2;
Query OK, 0 rows affected (0.20 sec)

MariaDB [opdba]> create table tb_name2(a int, b char(20), c varchar(21823)) default charset=utf8;
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


实例三:若一张表中有多字段VARCHAR(N)类型,并且有其它的字段类型,gbk编码,则N的最大值为多少?

如:create table tb_name3(a int, b char(20), c varchar(50), d varchar(N)) default charset=gbk;


则:N最大值=(65535-1-1-2-4-20*2-50*2)/2=32693
  • 第一个减1的原因是实际行存储从第二个字节开始;
  • 第二个减1表示第二个varchar(50)头部一个1个字节表示长度(小于255);
  • 减2的原因是varchar头部的2个字节表示长度;
  • 减20*2的原因是char(20)占用40个字节,编码是gbk;
  • 减50*2的原因是varchar(50)占用100个字节,编码是gbk;

回到SQL测试:
MariaDB [opdba]> create table tb_name3(a int, b char(20), c varchar(50), d varchar(32694)) default charset=gbk;

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


MariaDB [opdba]> create table tb_name3(a int, b char(20), c varchar(50), d varchar(32693)) default charset=gbk;

Query OK, 0 rows affected (0.18 sec)


所以在开发者或运维DBA设计表的时候要注意这个问题~~~,如果还是不明白的就是你的脑壳有问题~!~呵呵~~~~~...欢迎来电交流!


本系列文章同步到:http://www.opdba.com/?p=303