【轉】Mysql中varchar存放中文與英文所佔位元組異同

Pyerlife發表於2016-10-15
MySql

本文轉自Ruby china, 原文地址：https://ruby-china.org/topics/24920

 

一、關於UTF-8

UTF-8 Unicode Transformation Format-8bit。是用以解決國際上字元的一種多位元組編碼。

它對英文使用8位（即一個位元組) ，中文使用24位（三個位元組）來編碼。

UTF-8包含全世界所有國家需要用到的字元，是國際編碼，通用性強。

UTF-8編碼的文字可以在各國支援UTF8字符集額的瀏覽器上顯示。
如果是UTF8編碼，則在外國人的英文IE也能顯示中文，他們無需下載IE的中文語言支援包。

二、關於GBK

GBK 是國家標準GB2312基礎上擴容後相容GB2312的標準。

GBK的文字編碼是用雙位元組來表示的，即不論中、英文字元均使用雙位元組來表示，為了區分中文，將其最高位都設定成1。

GBK包含全部中文字元，是國家編碼，通用性比UTF8差，不過UTF8佔用的資料庫比GBK大。

三、關於utf8mb4

MySql 5.5 之前，UTF8 編碼只支援1-3個位元組，只支援BMP這部分的unicode編碼區，BMP是從哪到哪？
戳這裡 基本就是 0000 ~ FFFF 這一區。

從MySQL 5.5 開始，可支援4個位元組UTF編碼utf8mb4，一個字元最多能有4位元組，所以能支援更多的字符集。


utf8mb4 is a superset of utf8

tf8mb4相容utf8，且比utf8能表示更多的字元。

至於什麼時候用，看你做的什麼專案了。。。
在做移動應用時，會遇到IOS使用者在文字的區域輸入emoji表情，如果不做一定處理，就會導致插入資料庫異常。

四、漢字長度與編碼有關

MySql 5.0 以上的版本：

1、一個漢字佔多少長度與編碼有關：

  • UTF-8：一個漢字 = 3個位元組，英文是一個位元組
  • GBK： 一個漢字 = 2個位元組，英文是一個位元組

2、varchar(n) 表示n個字元，無論漢字和英文，MySql都能存入 n 個字元，僅實際位元組長度有所區別。

3、MySQL檢查長度，可用SQL語言


SELECT LENGTH(fieldname) FROM tablename 

五、實際測試

1、首先使用utf8 建立 str_test 表。

    CREATE TABLE `str_test` (
        `name_chn` varchar(20) NOT NULL,
        `name_en`  varchar(20) NOT NULL
    ) ENGINE=InnoDB AUTO_INCREMENT=62974 DEFAULT CHARSET=utf8

然後插入值

    mysql> insert into  str_test values ('我愛Ruby', 'I Love Ruby!');
    Query OK, 1 row affected (0.02 sec)

開啟irb

    >> "我愛Ruby".size
    => 6
    >> "I Love Ruby!".size
    => 12
    >>

從MySQL中查詢出來的結果，對比

    mysql> select * from str_test;
    +------------+--------------+
    | name_chn   | name_en      |
    +------------+--------------+
    | 我愛Ruby   | I Love Ruby! |
    +------------+--------------+
    1 row in set (0.02 sec)


    mysql> select length(name_chn) from str_test;
    +------------------+
    | length(name_chn) |
    +------------------+
    |               10 |
    +------------------+
    1 row in set (0.01 sec)

3[一個漢字三位元組] * 2 + 1[一個英文一位元組] * 4 = 10

    mysql> select length(name_en) from str_test;
    +-----------------+
    | length(name_en) |
    +-----------------+
    |              12 |
    +-----------------+
    1 row in set (0.00 sec)

10[一個英文一位元組] * 1 + 2[空格一位元組] * whitespace = 12

2、使用 GBK 做測試

建立表

        CREATE TABLE `str_test` (
        `name_chn` varchar(20) NOT NULL,
        `name_en`  varchar(20) NOT NULL
    ) ENGINE=InnoDB AUTO_INCREMENT=62974 DEFAULT CHARSET=gbk

插入資料，並且測試

    mysql> insert into  str_test values ('我愛Ruby', 'I Love Ruby!');
    Query OK, 1 row affected (0.00 sec)

    mysql> select * from str_test;
    +------------+--------------+
    | name_chn   | name_en      |
    +------------+--------------+
    | 我愛Ruby   | I Love Ruby! |
    +------------+--------------+
    1 row in set (0.01 sec)

GBK 中文是兩個位元組，英文是一個位元組。

    mysql> select length(name_chn) from str_test;
    +------------------+
    | length(name_chn) |
    +------------------+
    |                8 |
    +------------------+
    1 row in set (0.00 sec)

2[中文兩個位元組] * 2 + 4[英文一個位元組] * 1 = 8

    mysql> select length(name_en) from str_test;
    +-----------------+
    | length(name_en) |
    +-----------------+
    |              12 |
    +-----------------+
    1 row in set (0.00 sec)

10[英文一個位元組] * 1 + 2[空格一個位元組] * whitespace = 12

六、關於varchar 最多能存多少值

  • mysql的記錄行長度是有限制的，不是無限長的，這個長度是64K，即65535個位元組，對所有的表都是一樣的。

  • MySQL對於變長型別的欄位會有1-2個位元組來儲存字元長度。

  • 當字元數小於等於255時，MySQL只用1個位元組來記錄，因為2的8次方減1只能存到255。

  • 當字元數多餘255時，就得用2個位元組來存長度了。

  • 在utf-8狀態下的varchar，最大隻能到 (65535 - 2) / 3 = 21844 餘 1。

  • 在gbk狀態下的varchar, 最大隻能到 (65535 - 2) / 2 = 32766 餘 1

使用 utf-8 建立

        mysql>     CREATE TABLE `str_test` (
        ->         `id`  tinyint(1)  NOT NULL,
        ->         `name_chn` varchar(21845) NOT NULL
        ->     ) ENGINE=InnoDB AUTO_INCREMENT=62974 DEFAULT CHARSET=utf8
        -> ;
        ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
        mysql>     CREATE TABLE `str_test` (
        ->         `id`  tinyint(1)  NOT NULL,
        ->         `name_chn` varchar(21844) NOT NULL
        ->     ) ENGINE=InnoDB AUTO_INCREMENT=62974 DEFAULT CHARSET=utf8
        ->
        ->
        -> ;
        Query OK, 0 rows affected (0.06 sec)

使用gbk建立

當儲存長度為 32768 失敗~

        mysql>     CREATE TABLE `str_test` (
        ->         `id`  tinyint(1)  NOT NULL,
        ->         `name_chn` varchar(32768) NOT NULL
        ->     ) ENGINE=InnoDB AUTO_INCREMENT=62974 DEFAULT CHARSET=gbk
        -> ;
    ERROR 1074 (42000): Column length too big for column 'name_chn' (max = 32767); use BLOB or TEXT instead

當儲存長度為 32767 失敗~

    mysql>     CREATE TABLE `str_test` (                                                                                                 ->         `id`  tinyint(1)  NOT NULL,
        ->         `name_chn` varchar(32767) NOT NULL
        ->     ) ENGINE=InnoDB AUTO_INCREMENT=62974 DEFAULT CHARSET=gbk
        -> ;
    ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

當儲存長度為 32766 成功~

    mysql>     CREATE TABLE `str_test` (
        ->         `id`  tinyint(1)  NOT NULL,
        ->         `name_chn` varchar(32766) NOT NULL
        ->     ) ENGINE=InnoDB AUTO_INCREMENT=62974 DEFAULT CHARSET=gbk
        -> ;
    Query OK, 0 rows affected (0.03 sec)

smallint 用兩個位元組儲存，所以

2[smallint] + 32766 * 2[varchar儲存長度] + 2[2個位元組來存長度] > 65535

所以失敗~

     mysql>     CREATE TABLE `str_test` (
         ->         `id`  smallint(1)  NOT NULL,
         ->         `name_chn` varchar(32766) NOT NULL
         ->     ) ENGINE=InnoDB AUTO_INCREMENT=62974 DEFAULT CHARSET=gbk
         -> ;
     ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

#####七、數值型別所佔的位元組

型別所佔位元組
int 4 位元組
smallint 2 位元組
tinyint 1 位元組
decimal 變長

官方關於decimal 的描述如下

Values for DECIMAL (and NUMERIC) columns are represented using a binary format that packs nine decimal (base 10) digits into four bytes.

Storage for the integer and fractional parts of each value are determined separately.

Each multiple of nine digits requires four bytes, and the “leftover” digits require some fraction of four bytes.

The storage required for excess digits is given by the following table.

翻譯為中文

使用二進位制格式將9個十進位制(基於10)數壓縮為4個位元組來表示DECIMAL列值。

每個值的整數和分數部分的儲存分別確定。

每個9位數的倍數需要4個位元組，並且“剩餘的”位需要4個位元組的一部分。

下表給出了超出位數的儲存需求：

Leftover DigitsNumber Of Bytes
0 0
1 1
2 1
3 2
4 2
5 3
6 3
7 4
8 4

那：decimal(10,2)佔幾個位元組？

1、首先 10 指的是整數與小數部分的總長度， 2指的是小數部分的長度。那麼整數部分就只有 10 - 2 = 8 位

2、因為整數與小數的儲存市各自獨立確定的，所以他們各自所佔用空間的綜合就是所佔的總空間了。

3、對錶可知，整數部分8位佔了4個位元組，小數部分2位佔了1個位元組，所以decimal(10,2)總共佔了 4 + 1 = 5 個位元組。

4、decimal(6,2) 整數部分(6 - 2 = 4) 位佔2位元組，小數部分2位佔1位元組，總共佔3位元組。

八、總結

varchar 欄位是將實際內容單獨儲存在聚簇索引之外，內容開頭用1到2個位元組表示實際長度（長度超過255時需要2個位元組），因此最大長度不能超過65535。

  • UTF-8：一個漢字 = 3個位元組，英文是一個位元組
  • GBK： 一個漢字 = 2個位元組，英文是一個位元組

在utf-8狀態下，漢字最多可以存 21844個字串, 英文也為 21844個字串。

在gbk狀態下，漢字最多可以存 32766個字串，英文也為 32766個字串。

相關文章