【MySQL】淺談 varchar(N)
一 前言
varchar(N) N代表什麼意思，能存放多少箇中文字元？屬於老生常談的問題了，今天又被一個開發同事問我關於這個問題，索性寫一篇文章來具體介紹一下。
二 理論知識
先說明一下 MySQL 歷來版本對 varchar 的定義:
4.0版本以下，varchar(50)，指的是50位元組，如果存放UTF8漢字時，只能存16個（每個中文3位元組）
5.0版本以上，varchar(50)，指的是50字元，無論存放的是數字、字母還是UTF8中文（每個中文3位元組），都可以存放50個
儲存限制
需要額外佔用位元組存放字元的長度:小於255為1個位元組，大於255則要2個位元組
編碼限制
gbk :每個字元最多佔用2個位元組
utf8:每個字元最多佔用3個位元組
utf8mb4 每個字元最多佔用4個位元組,中文佔3個位元組，emoji表情符號 佔用4個位元組
長度限制
MySQL定義行的長度不能超過65535，該數值限制了列的數目，比如char(128) utf8字符集,最多有65535/(128*3)=170個漢字。
三 測試
環境 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佔用的位元組數，char_length(str)表示str佔用的字元數。
不論什麼字符集，對於數字和英文字母都是隻佔用1個字元，也佔用一個位元組。而中文漢字因字符集不同而不同。
四 總結
回過頭來回答文章開頭的問題varchar(N)可以存放多少箇中文漢字。答案是在 5.0 以後的版本中 varchar(N) 可以存放N個漢字 。
拋開字符集 ，如果一行資料全部為 varchar 型別，其最大長度為 65535 個位元組。
行長度計算公式如下：
row length = 1
+ (sum of column lengths)
+ (number of NULL columns + delete_flag + 7)/8
+ (number of variable-length columns)
對於MyISAM，需要額外1個位來記錄值是否為NULL；對於InnoDB，沒有區別
對於row_format為fixed，delete_flag為1；對於row_format=dynamic,delete_flag為0
根據這個公式，我們便能夠解答開頭N的最大值：(65535-1-2)/3
減1是因為實際儲存從第2個位元組開始
減2則因為要在列表長度儲存實際字元長度
除3是因為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個位元組;
減30*3的原因是char(30)佔用90個位元組，編碼是utf8。
如果被varchar超過上述的b規則，被強轉成text型別，則每個欄位佔用定義長度為11位元組，當然這已經不是“varchar”了
五 參考
http://blog.csdn.net/u012048106/article/details/23173911
varchar(N) N代表什麼意思，能存放多少箇中文字元？屬於老生常談的問題了，今天又被一個開發同事問我關於這個問題，索性寫一篇文章來具體介紹一下。
二 理論知識
先說明一下 MySQL 歷來版本對 varchar 的定義:
4.0版本以下，varchar(50)，指的是50位元組，如果存放UTF8漢字時，只能存16個（每個中文3位元組）
5.0版本以上，varchar(50)，指的是50字元，無論存放的是數字、字母還是UTF8中文（每個中文3位元組），都可以存放50個
儲存限制
需要額外佔用位元組存放字元的長度:小於255為1個位元組，大於255則要2個位元組
編碼限制
gbk :每個字元最多佔用2個位元組
utf8:每個字元最多佔用3個位元組
utf8mb4 每個字元最多佔用4個位元組,中文佔3個位元組，emoji表情符號 佔用4個位元組
長度限制
MySQL定義行的長度不能超過65535，該數值限制了列的數目，比如char(128) utf8字符集,最多有65535/(128*3)=170個漢字。
三 測試
環境 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佔用的位元組數，char_length(str)表示str佔用的字元數。
不論什麼字符集，對於數字和英文字母都是隻佔用1個字元，也佔用一個位元組。而中文漢字因字符集不同而不同。
四 總結
回過頭來回答文章開頭的問題varchar(N)可以存放多少箇中文漢字。答案是在 5.0 以後的版本中 varchar(N) 可以存放N個漢字 。
拋開字符集 ，如果一行資料全部為 varchar 型別，其最大長度為 65535 個位元組。
行長度計算公式如下：
row length = 1
+ (sum of column lengths)
+ (number of NULL columns + delete_flag + 7)/8
+ (number of variable-length columns)
對於MyISAM，需要額外1個位來記錄值是否為NULL；對於InnoDB，沒有區別
對於row_format為fixed，delete_flag為1；對於row_format=dynamic,delete_flag為0
根據這個公式，我們便能夠解答開頭N的最大值：(65535-1-2)/3
減1是因為實際儲存從第2個位元組開始
減2則因為要在列表長度儲存實際字元長度
除3是因為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個位元組;
減30*3的原因是char(30)佔用90個位元組，編碼是utf8。
如果被varchar超過上述的b規則，被強轉成text型別，則每個欄位佔用定義長度為11位元組，當然這已經不是“varchar”了
五 參考
http://blog.csdn.net/u012048106/article/details/23173911
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-1979335/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【MySQL】淺談varchar(N)MySql
- MySQL中int、char、varchar的效能淺談MySql
- 面試官：MySQL 中 varchar(n) 中 n 最大取值為多少？面試MySql
- MySQL中資料型別（char(n)、varchar(n)、nchar(n)、nvarchar(n)的區別）MySql資料型別
- 談MySQL中char varchar區別MySql
- mysql的varchar(N)和int(N)的含義及其與char區別MySql
- char(n)和varchar2(n)區別
- VARCHAR2(N CHAR)與VARCHAR2(N)的區別[Oracle基礎]Oracle
- 淺談Mysql索引MySql索引
- 【MySQL】淺談MySQL優化MySql優化
- MySQL淺談（索引、鎖）MySql索引
- 淺談mysql innodb lockingMySql
- MySQL varchar詳解MySql
- mysql淺談--事務ACID特性MySql
- MySQL 中 VARCHAR 最大長度及 CHAR 和 VARCHAR 的區別MySql
- 【MySQL】淺談一致性讀MySql
- 淺談MySQL叢集高可用架構MySql架構
- 淺談 MySQL 的儲存引擎（表型別）MySql儲存引擎型別
- MySQL中CHAR和VARCHAR區別MySql
- mysql字元型別varchar（）比較MySql字元型別
- 淺淺談ReduxRedux
- 淺談mysql的兩階段提交協議MySql協議
- **Mysql5.7新特性之—– 淺談Sys庫**MySql
- 例項操作mysql varchar型別求和MySql型別
- 【MySQL】varbinary 真的比varchar 更合適？MySql
- 淺談MySQL日誌檔案｜手撕MySQL｜對線面試官MySql面試
- 淺淺淺談JavaScript作用域JavaScript
- 乾貨：阿里大牛淺談MySQL架構體系阿里MySql架構
- 淺談MySQL的B樹索引與索引優化MySql索引優化
- 淺談MySQL備份字符集的問題MySql
- 運維角度淺談MySQL資料庫優化運維MySql資料庫優化
- 運維角度淺談 MySQL 資料庫優化運維MySql資料庫優化
- [資料庫] 淺談mysql的serverId/serverUuid資料庫MySqlServerUI
- MySQL資料型別操作(char與varchar)MySql資料型別
- MySQL動態修改varchar長度的方法MySql
- N進位制漫談
- Celery淺談
- 淺談flutterFlutter