MySQL下varchar类型最大长度是多少

在MySQL下varchar类型最大长度是多少呢,我们百度查一下会发现varchar长度为varchar数据类型的长度支持到了65535,也就是说可以存放65532个字节的数据了,那到底是不是这样的呢,我们来看看.

1、限制规则

字段的限制在字段定义的时候有以下规则：

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

b)  编码长度限制:字符类型若为gbk,每个字符最多占2个字节,最大长度不能超过32766;

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

c) 行长度限制:导致实际应用中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。

2、计算例子,举两个例说明一下实际长度的计算.

a)若一个表只有一个varchar类型,如定义为:

create table t4(c varchar(N)) charset=gbk;

则此处N的最大值为(65535-1-2)/2= 32766,减1的原因是实际行存储从第二个字节开始’;减2的原因是varchar头部的2个字节表示长度;除2的原因是字符编码是gbk.

b) 若一个表定义为:

create table t4(c int, c2 char(30), c3 varchar(N)) charset=utf8;

则此处N的最大值为 (65535-1-2-4-30*3)/3=21812,减1和减2与上例相同;减4的原因是int类型的c占4个字节;减30*3的原因是char(30)占用90个字节，编码是utf8.

如果被varchar超过上述的b规则,被强转成text类型,则每个字段占用定义长度为11字节,当然这已经不是“varchar”了.

上网看了些资料,又在本地做了些实验,原来vachar的最大长度真的是不定的,根据是否有非空字段来决定,在本地做了下实验,innodb+latin的环境.

1. -- success  
2. drop table if exists test;  
3. create table test(name varchar(65533) not null)engine=innodb DEFAULT CHARSET=latin1  
4. -- too large  
5. drop table if exists test; 

create table test(name varchar(65533))engine=innodb DEFAULT CHARSET=latin1

对于第二种情况,允许空字段的时候是不能加到65533的长度的,最大只能到65532,到底应该是引文的那种说法.

网上也有人做了类似的实验,参考:http://stackoverflow.com/questions/8295131/best-practise-for-sql-varchar-column-length

1. name varchar(100) not null will be 1 byte (length) + up to 100 chars (latin1) 
2. name varchar(500) not null will be 2 bytes (length) + up to 500 chars (latin1)  
3. name varchar(65533) not null will be 2 bytes (length) + up to 65533 chars (latin1)  
4. name varchar(65532) will be 2 bytes (length) + up to 65532 chars (latin1) + 1 null byte   //phpfensi.com

总结一下,原来mysql的vachar字段的类型虽然最大长度是65535,但是并不是能存这么多数据,最大可以到65533,不允许非空字段的时候,当允许非空字段的时候只能到65532.

2.CHAR(M), VARCHAR(M)不同之处

CHAR(M)定义的列的长度为固定的，M取值可以为0～255之间，当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。

CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间,不足的自动用空格填充。

VARCHAR(M)定义的列的长度为可变长字符串，M取值可以为0~65535之间，(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节）。VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。

VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。varchar存储变长数据，但存储效率没有CHAR高。如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1。为什么"+1"呢？这一个字节用于保存实际使用了多大的长度。从空间上考虑，用varchar合适；从效率上考虑，用char合适，关键是根据实际情况找到权衡点。

如果被裁掉的字符不是空格，则会产生一条警告。如果裁剪非空格字符，则会造成错误(而不是警告)并通过使用严格SQL模式禁用值的插入。

3. VARCHAR和TEXT、BlOB类型的区别 

VARCHAR，BLOB和TEXT类型是变长类型，对于其存储需求取决于列值的实际长度(在前面的表格中用L表示)，而不是取决于类型的最大可能尺寸。例如，一个VARCHAR(10)列能保存最大长度为10个字符的一个字符串，实际的存储需要是字符串的长度 ，加上1个字节以记录字符串的长度。对于字符串'abcd'，L是4而存储要求是5个字节。

BLOB和TEXT类型需要1，2，3或4个字节来记录列值的长度，这取决于类型的最大可能长度。VARCHAR需要定义大小，有65535字节的最大限制；TEXT则不需要。如果你把一个超过列类型最大长度的值赋给一个BLOB或TEXT列，值被截断以适合它。

一个BLOB是一个能保存可变数量的数据的二进制的大对象。4个BLOB类型TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB仅仅在他们能保存值的最大长度方面有所不同。

BLOB 可以储存图片,TEXT不行，TEXT只能储存纯文本文件。4个TEXT类型TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT对应于4个BLOB类型，并且有同样的最大长度和存储需求。在BLOB和TEXT类型之间的唯一差别是对BLOB值的排序和比较以大小写敏感方式执行，而对TEXT值是大小写不敏感的。换句话说，一个TEXT是一个大小写不敏感的BLOB。

4.总结char，varchar，text区别 

长度的区别,char范围是0～255，varchar最长是64k,但是注意这里的64k是整个row的长度,要考虑到其它的column,还有如果存在not null的时候也会占用一位,对不同的字符集,有效长度还不一样,比如utf8的,最多21845,还要除去别的column,但是varchar在一般情况下存储都够用了,如果遇到了大文本,考虑使用text,最大能到4G.

效率来说基本是char>varchar>text,但是如果使用的是Innodb引擎的话,推荐使用varchar代替char.

char和varchar可以有默认值,text不能指定默认值.

数据库选择合适的数据类型存储还是很有必要的,对性能有一定影响,这里在零碎记录两笔,对于int类型的,如果不需要存取负值,最好加上unsigned,对于经常出现在where语句中的字段,考虑加索引,整形的尤其适合加索引.