1. 个人技术分享首页

Mysql中varchar类型一些需要注意的地方

varchar的存储规则

4.0版本以下,varchar(20),指的是20字节,如果存放UTF8汉字时,只能存6个(每个汉字3字节)。
5.0版本以上,varchar(20),指的是20字符,无论存放的是数字、字母还是UTF8汉字(每个汉字3字节),都可以存放20个,最大大小是65532字节。
varchar 字段是将实际内容单独存储在聚簇索引之外,内容开头用1到2个字节表示实际长度。
官方是这么说的:

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.

varchar和char 的区别

区别一,定长和变长

char 表示定长,长度固定,varchar表示变长,即长度可变。当所插入的字符串超出它们的长度时,视情况来处理,如果是严格模式,则会拒绝插入并提示错误信息,如果是宽松模式,则会截取然后插入。如果插入的字符串长度小于定义长度时,则会以不同的方式来处理,如char(10),表示存储的是10个字符,无论你插入的是多少,都是10个,如果少于10个,则用空格填满。而varchar(10),小于10个的话,则插入多少个字符就存多少个。
varchar怎么知道所存储字符串的长度呢?实际上,对于varchar字段来说,需要使用一个(如果字符串长度小于255)或两个字节(长度大于255)来存储字符串的长度。但是因为他需要有一个prefix来表示他具体bytes数是多少(因为varchar是变长的,没有这个长度值他不知道如何读取数据)。

区别之二,存储的容量不同

对 char 来说,最多能存放的字符个数 255,和编码无关。
而 varchar 呢,最多能存放 65532 个字符。VARCHAR 的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是 65,532字节

varchar的编码长度限制

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

行长度限制

导致实际应用中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.

varchar的控制位

MySQL 中的Varchar字符类型还保留了1个字节来留其它控制信息。

示例

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

如:create table tb_name1(a varchar(N)) default charset=utf8,则N最大值=(65535-1-2)/3=21844。
减1的原因是实际行存储从第二个字节开始。
减2的原因是varchar头部的2个字节表示长度。
除3的原因是字符编码是utf8。
sql测试:

create table tb_name1(a varchar(21844)) default charset=utf8;
Query OK, 0 rows affected (0.38 sec)

drop table tb_name1;
Query OK, 0 rows affected (0.00 sec)

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 

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

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

sql测试:

create table tb_name2(a int, b char(20), c varchar(21822)) default charset=utf8;
Query OK, 0 rows affected (0.28 sec)

drop table tb_name2;
Query OK, 0 rows affected (0.20 sec)

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-202-502)/2=32693
第一个减1的原因是实际行存储从第二个字节开始。
第二个减1表示第二个varchar(50)头部一个1个字节表示长度(小于255)。
减2的原因是varchar头部的2个字节表示长度。
减202的原因是char(20)占用40个字节,编码是gbk。
减502的原因是varchar(50)占用100个字节,编码是gbk。

SQL测试:

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

文章来源于互联网:Mysql中varchar类型一些需要注意的地方

原创文章,作者:admin,如若转载,请注明出处:https://www.aliyunsolution.com/2987.html