如何理解MySQL中的varchar(N)

152次閱讀

共計 5832 個字符，預(yù)計需要花費(fèi) 15 分鐘才能閱讀完成。

本篇文章給大家分享的是有關(guān)如何理解 MySQL 中的 varchar(N)，丸趣 TV 小編覺得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著丸趣 TV 小編一起來看看吧。

一前言
varchar(N) N 代表什么意思，能存放多少個中文字符？屬于老生常談的問題了，今天又被一個開發(fā)同事問我關(guān)于這個問題，索性寫一篇文章來具體介紹一下。

二理論知識
先說明一下 MySQL 歷來版本對 varchar 的定義:
4.0 版本以下，varchar(50)，指的是 50 字節(jié)，如果存放 UTF8 漢字時，只能存 16 個（每個中文 3 字節(jié)）
5.0 版本以上，varchar(50)，指的是 50 字符，無論存放的是數(shù)字、字母還是 UTF8 中文（每個中文 3 字節(jié)），都可以存放 50 個
存儲限制
需要額外占用字節(jié)存放字符的長度: 小于 255 為 1 個字節(jié)，大于 255 則要 2 個字節(jié)
編碼限制
gbk : 每個字符最多占用 2 個字節(jié)
utf8: 每個字符最多占用 3 個字節(jié)
utf8mb4 每個字符最多占用 4 個字節(jié), 中文占 3 個字節(jié)，emoji 表情符號占用 4 個字節(jié)
長度限制
MySQL 定義行的長度不能超過 65535，該數(shù)值限制了列的數(shù)目，比如 char(128) utf8 字符集, 最多有 65535/(128*3)=170 個漢字。

三測試
環(huán)境 Server version: 5.6.26-74.0-log Percona Server
mysql create table t1
– (id int NOT NULL AUTO_INCREMENT primary key,
– name varchar(10)
– ) engine=innodb default charset=utf8mb4;
Query OK, 0 rows affected (0.01 sec)
mysql create table t2
– (id int NOT NULL AUTO_INCREMENT primary key,
– name varchar(10)
– ) engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql create table t3
– (id int NOT NULL AUTO_INCREMENT primary key,
– name varchar(10)
– ) engine=innodb default charset=gbk;
Query OK, 0 rows affected (0.01 sec)
utf8mb4 字符集
mysql insert into t1(name) values(abcdfeghi
Query OK, 1 row affected (0.00 sec)
mysql insert into t1(name) values(abcdfeghij
Query OK, 1 row affected (0.00 sec)
mysql insert into t1(name) values(abcdfeghijk
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql insert into t1(name) values(一二三四五六七八九十
Query OK, 1 row affected (0.00 sec)
mysql insert into t1(name) values(一二三四五六七八九十一
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql show warnings;
+———+——+——————————————-+
| Level | Code | Message |
+———+——+——————————————-+
| Warning | 1265 | Data truncated for column name at row 1 |
+———+——+——————————————-+
1 row in set (0.00 sec)
mysql insert into t1(name) values(0123456789
Query OK, 1 row affected (0.00 sec)
mysql insert into t1(name) values(01234567890
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql select id,name,length(name),char_length(name) from t1;
+—-+——————————–+————–+——————-+
| id | name | length(name) | char_length(name) |
+—-+——————————–+————–+——————-+
| 1 | abcdfeghi | 9 | 9 |
| 2 | abcdfeghij | 10 | 10 |
| 3 | abcdfeghij | 10 | 10 |
| 4 | 一二三四五六七八九十 | 30 | 10 |
| 5 | 一二三四五六七八九十 | 30 | 10 |
| 6 | 0123456789 | 10 | 10 |
| 7 | 0123456789 | 10 | 10 |
+—-+——————————–+————–+——————-+
7 rows in set (0.00 sec)
utf8 字符集
mysql insert into t2(name) values(abcdfeghi
Query OK, 1 row affected (0.00 sec)
mysql insert into t2(name) values(abcdfeghij
Query OK, 1 row affected (0.00 sec)
mysql insert into t2(name) values(abcdfeghijk
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql insert into t2(name) values(一二三四五六七八九十
Query OK, 1 row affected (0.00 sec)
mysql insert into t2(name) values(一二三四五六七八九十一
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql insert into t2(name) values(0123456789
Query OK, 1 row affected (0.00 sec)
mysql insert into t2(name) values(01234567890
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql select id,name,length(name),char_length(name) from t2;
+—-+——————————–+————–+——————-+
| id | name | length(name) | char_length(name) |
+—-+——————————–+————–+——————-+
| 1 | abcdfeghi | 9 | 9 |
| 2 | abcdfeghij | 10 | 10 |
| 3 | abcdfeghij | 10 | 10 |
| 4 | 一二三四五六七八九十 | 30 | 10 |
| 5 | 一二三四五六七八九十 | 30 | 10 |
| 6 | 0123456789 | 10 | 10 |
| 7 | 0123456789 | 10 | 10 |
+—-+——————————–+————–+——————-+
7 rows in set (0.00 sec)
gbk 字符集
mysql insert into t3(name) values(abcdfeghi
Query OK, 1 row affected (0.00 sec)
mysql insert into t3(name) values(abcdfeghij
Query OK, 1 row affected (0.00 sec)
mysql insert into t3(name) values(abcdfeghijk
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql insert into t3(name) values(一二三四五六七八九十
Query OK, 1 row affected (0.01 sec)
mysql insert into t3(name) values(一二三四五六七八九十一
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql insert into t3(name) values(0123456789
Query OK, 1 row affected (0.00 sec)
mysql insert into t3(name) values(01234567890
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql select id,name,length(name),char_length(name) from t3;
+—-+——————————–+————–+——————-+
| id | name | length(name) | char_length(name) |
+—-+——————————–+————–+——————-+
| 1 | abcdfeghi | 9 | 9 |
| 2 | abcdfeghij | 10 | 10 |
| 3 | abcdfeghij | 10 | 10 |
| 4 | 一二三四五六七八九十 | 20 | 10 |
| 5 | 一二三四五六七八九十 | 20 | 10 |
| 6 | 一二三四五六七八九十 | 20 | 10 |
| 7 | 0123456789 | 10 | 10 |
| 8 | 0123456789 | 10 | 10 |
+—-+——————————–+————–+——————-+
8 rows in set (0.00 sec)
從上面的測試可以看出, 目前版本中 varchar(N) 定義的長度的單位是字符,length(str) 表示 str 占用的字節(jié)數(shù)，char_length(str)表示 str 占用的字符數(shù)。
不論什么字符集，對于數(shù)字和英文字母都是只占用 1 個字符，也占用一個字節(jié)。而中文漢字因字符集不同而不同。

四總結(jié)
回過頭來回答文章開頭的問題 varchar(N) 可以存放多少個中文漢字。答案是在 5.0 以后的版本中 varchar(N) 可以存放 N 個漢字。
拋開字符集，如果一行數(shù)據(jù)全部為 varchar 類型，其最大長度為 65535 個字節(jié)。
行長度計算公式如下：
row length = 1
+ (sum of column lengths)
+ (number of NULL columns + delete_flag + 7)/8
+ (number of variable-length columns)
對于 MyISAM，需要額外 1 個位來記錄值是否為 NULL；對于 InnoDB，沒有區(qū)別
對于 row_format 為 fixed，delete_flag 為 1；對于 row_format=dynamic,delete_flag 為 0

根據(jù)這個公式，我們便能夠解答開頭 N 的最大值：(65535-1-2)/3
減 1 是因?yàn)閷?shí)際存儲從第 2 個字節(jié)開始
減 2 則因?yàn)橐诹斜黹L度存儲實(shí)際字符長度
除 3 是因?yàn)?utf8 編碼限制
再來一道：
create table t4(c int, c2 char(30), c3 varchar(N)) charset=utf8;
N 的最大值：(65535-1-2-4-30*3)/3
則此處 N 的最大值為 (65535-1-2-4-30*3)/3=21812
減 1 和減 2 與上例相同;
減 4 的原因是 int 類型的 c 占 4 個字節(jié);
減 30* 3 的原因是 char(30)占用 90 個字節(jié)，編碼是 utf8。
如果被 varchar 超過上述的 b 規(guī)則，被強(qiáng)轉(zhuǎn)成 text 類型，則每個字段占用定義長度為 11 字節(jié)，當(dāng)然這已經(jīng)不是“varchar”了。

以上就是如何理解 MySQL 中的 varchar(N)，丸趣 TV 小編相信有部分知識點(diǎn)可能是我們?nèi)粘９ぷ鲿姷交蛴玫降?。希望你能通過這篇文章學(xué)到更多知識。更多詳情敬請關(guān)注丸趣 TV 行業(yè)資訊頻道。

正文完