MySQL基礎教程：關於varchar(N)

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個位元組，emojiEmoji 佔用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”了