在Mysql中使用varchar類(lèi)型需要注意哪些事項(xiàng)

在Mysql中使用varchar類(lèi)型需要注意哪些事項(xiàng)？很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面小編將為大家詳細(xì)講解，有這方面需求的人可以來(lái)學(xué)習(xí)下，希望你能有所收獲。

varchar的存儲(chǔ)規(guī)則

4.0版本以下，varchar(20)，指的是20字節(jié)，如果存放UTF8漢字時(shí)，只能存6個(gè)（每個(gè)漢字3字節(jié)）。
5.0版本以上，varchar(20)，指的是20字符，無(wú)論存放的是數(shù)字、字母還是UTF8漢字（每個(gè)漢字3字節(jié)），都可以存放20個(gè)，最大大小是65532字節(jié)。
varchar 字段是將實(shí)際內(nèi)容單獨(dú)存儲(chǔ)在聚簇索引之外，內(nèi)容開(kāi)頭用1到2個(gè)字節(jié)表示實(shí)際長(zhǎng)度。
官方是這么說(shuō)的：

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 的區(qū)別

區(qū)別一，定長(zhǎng)和變長(zhǎng)

char 表示定長(zhǎng)，長(zhǎng)度固定，varchar表示變長(zhǎng)，即長(zhǎng)度可變。當(dāng)所插入的字符串超出它們的長(zhǎng)度時(shí)，視情況來(lái)處理，如果是嚴(yán)格模式，則會(huì)拒絕插入并提示錯(cuò)誤信息，如果是寬松模式，則會(huì)截取然后插入。如果插入的字符串長(zhǎng)度小于定義長(zhǎng)度時(shí)，則會(huì)以不同的方式來(lái)處理，如char（10），表示存儲(chǔ)的是10個(gè)字符，無(wú)論你插入的是多少，都是10個(gè)，如果少于10個(gè)，則用空格填滿(mǎn)。而varchar（10），小于10個(gè)的話(huà)，則插入多少個(gè)字符就存多少個(gè)。
varchar怎么知道所存儲(chǔ)字符串的長(zhǎng)度呢？實(shí)際上，對(duì)于varchar字段來(lái)說(shuō)，需要使用一個(gè)（如果字符串長(zhǎng)度小于255）或兩個(gè)字節(jié)（長(zhǎng)度大于255）來(lái)存儲(chǔ)字符串的長(zhǎng)度。但是因?yàn)樗枰幸粋€(gè)prefix來(lái)表示他具體bytes數(shù)是多少（因?yàn)関archar是變長(zhǎng)的，沒(méi)有這個(gè)長(zhǎng)度值他不知道如何讀取數(shù)據(jù)）。

區(qū)別之二，存儲(chǔ)的容量不同

對(duì) char 來(lái)說(shuō)，最多能存放的字符個(gè)數(shù) 255，和編碼無(wú)關(guān)。
而 varchar 呢，最多能存放 65532 個(gè)字符。VARCHAR 的最大有效長(zhǎng)度由最大行大小和使用的字符集確定。整體最大長(zhǎng)度是 65,532字節(jié)

varchar的編碼長(zhǎng)度限制

字符類(lèi)型若為 gbk，則個(gè)字符最多占2個(gè)字節(jié)，最大長(zhǎng)度不能超過(guò)32766; 字符類(lèi)型若為utf8，則每個(gè)字符最多占3個(gè)字節(jié)，最大長(zhǎng)度不能超過(guò)21845。 若定義的時(shí)候超過(guò)上述限制，則varchar字段會(huì)被強(qiáng)行轉(zhuǎn)為text類(lèi)型，并產(chǎn)生warning。

行長(zhǎng)度限制

導(dǎo)致實(shí)際應(yīng)用中varchar長(zhǎng)度限制的是一個(gè)行定義的長(zhǎng)度。 MySQL要求一個(gè)行的定義長(zhǎng)度不能超過(guò)65535。若定義的表長(zhǎng)度超過(guò)這個(gè)值，則提示 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。
這就是說(shuō)，比如創(chuàng)建一個(gè)表，表結(jié)構(gòu)中有兩個(gè)varhcar類(lèi)型字段，那么這兩個(gè)字段的總長(zhǎng)度不能超過(guò)65535。
官方說(shuō)明如下：

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字符類(lèi)型還保留了1個(gè)字節(jié)來(lái)留其它控制信息。

示例

示例一：若一張表中只有一個(gè)字段VARCHAR(N)類(lèi)型，utf8編碼，則N最大值為多少？

如：create table tb_name1(a varchar(N)) default charset=utf8，則N最大值=(65535-1-2)/3=21844。
減1的原因是實(shí)際行存儲(chǔ)從第二個(gè)字節(jié)開(kāi)始。
減2的原因是varchar頭部的2個(gè)字節(jié)表示長(zhǎng)度。
除3的原因是字符編碼是utf8。
sql測(cè)試：

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

示例二：若一張表中有一個(gè)字段VARCHAR(N)類(lèi)型，并且有其它的字段類(lèi)型，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的原因是實(shí)際行存儲(chǔ)從第二個(gè)字節(jié)開(kāi)始。
減2的原因是varchar頭部的2個(gè)字節(jié)表示長(zhǎng)度。
減4的原因是a字段的int類(lèi)型占4個(gè)字節(jié)。
減203的原因是char(20)占用60個(gè)字節(jié)，編碼是utf8。

sql測(cè)試：

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)類(lèi)型，并且有其它的字段類(lèi)型，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
第一個(gè)減1的原因是實(shí)際行存儲(chǔ)從第二個(gè)字節(jié)開(kāi)始。
第二個(gè)減1表示第二個(gè)varchar(50)頭部一個(gè)1個(gè)字節(jié)表示長(zhǎng)度(小于255)。
減2的原因是varchar頭部的2個(gè)字節(jié)表示長(zhǎng)度。
減202的原因是char(20)占用40個(gè)字節(jié)，編碼是gbk。
減502的原因是varchar(50)占用100個(gè)字節(jié)，編碼是gbk。

SQL測(cè)試：

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)

看完上述內(nèi)容是否對(duì)您有幫助呢？如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝您對(duì)億速云的支持。