Mysql中varchar類型一些需要註意的地方

Posted on by

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
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)

以上就是Mysql中varchar類型一些需要註意的地方的詳細內容，更多關於Mysql varchar類型的資料請關註WalkonNet其它相關文章！

推薦閱讀：