MySQL 中 utf8 和 utf8mb4 的使用以及字符集相關

MySQL 中 utf8 編碼最長使用 3 字節,在 5.5.3 新增的 utf8mb4,才是兼容四字節的。Java 的 UTF-8 是支持 4 字節的,所以不需配置 mb4 。而 Java 驅動會自動檢測服務端的 character_set_server,為 utf8mb4,驅動在建立連接時設置 SET NAMES utf8mb4。utf8mb4_general_ci 在比較和排序的時候更快,utf8mb4_unicode_ci 更精確。

原文

概述

以前一般在 MySQL 開發了,就實際上轉向 Oracle 了,沒留意一些東西,碰到了就回頭過來看看。
有小夥伴在安裝 MySQL 後設置編碼為 utf8,我們以前都是會直接設置 utf8mb4,這背後又隱藏著什麼?這一切的背後,究竟是人性的扭曲還是道德的淪喪?

其實隻是因為 Unicode 委員會還做著 “65535 個字符足夠全世界用了”的美夢。 參考 我比較喜歡這句。
也就是指的 Unicode 最初的基本多文本平麵 (BMP)(U+0000 至 U+FFFF),BMP 已經包含了控製符、拉丁文,中、日、韓等絕大多數國際字符,但並不是所有,最常見的就算現在手機端常用的表情字符 Emoji(Emoji 是一種特殊的 Unicode 編碼,常見於 ios 和 android 手機上)一些不常用的漢字,如 “墅” ,這些需要四個字節才能編碼出來。後來的補充字符 (U+10000 至 U+10FFFF),則將 Unicode 擴充到了 (U+0000 至 U+10FFFF)。

注:QQ 裏麵的內置的表情不算,它是通過特殊映射到的一個 gif 圖片。一般輸入法自帶的就是。

MySQL 在 5.5.3 之後 (查看版本:select version ();) 增加了這個 utf8mb4 的編碼,mb4 就是 most bytes 4 的意思,支持的字節數最大為 4,即專門用來兼容四字節的 unicode。
而我們通常在 MySQL 中所說的 utf8 編碼,其實就是指的 utf8mb3 。utf8mb4 是 utf8mb3 的超集並完全兼容 utf8mb3,能夠用四個字節存儲更多的字符。

utf8mb3:Unicode 字符集的 UTF-8 編碼,每個字符使用 1 到 3 個字節。
utf8mb4:Unicode 字符集的 UTF-8 編碼,每個字符使用 1 到 4 個字節。

對於 CHAR 類型數據,utf8mb4 會多消耗一些空間,根據 Mysql 官方建議, 使用 VARCHAR 替代 CHAR 以有效節省空間。

當你的數據庫裏要求能夠存入這些表情或寬字符時,可以把字段定義為 utf8mb4,同時要注意連接字符集也要設置為 utf8mb4,否則在 嚴格模式 下會出現 Incorrect string value: /xF0/xA1/x8B/xBE/xE5/xA2… for column 'name' 這樣的錯誤,非嚴格模式下此後的數據會被截斷。

建立數據庫 / 表和進行數據庫操作時盡量顯式指出使用的字符集,而不是依賴於 MySQL 的默認設置,否則 MySQL 升級時可能帶來很大困擾。

如默認不區分大小寫,可添加 binary,強製進行按字節進行比較,以區分大小寫。 如建表時未添加binary屬性,可能影響索引失效 其他 binary 相關

MySQL 使用的 UTF-8 都沒有 BOM 值。
BOM(Byte Order Mark),即文本開頭為不可見的3個字節,EF BB BF

utf8 升級 utf8mb4

如果你的表定義和連接字符集都是 utf8,那麼直接在你的表上執行

1
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4;

則能夠該表上所有的列的 character 類型變成 utf8mb4,表定義的默認字符集也會修改。

點擊上麵目錄見修改數據庫、表、字段實例,或點這實例 。

注意事項

  • 使用 utf8mb4 之後,官方建議盡量用 varchar 代替 char。
  • SET NAMES utf8mb4; 讓連接的時候便可以插入四字節字符。(如果依然使用 utf8 連接,隻要不出現四字節字符則完全沒問題)。
  • SET character-set-server = utf8mb4; 修改服務端 character-set-server=utf8mb4,Java 驅動會自動檢測服務端 character_set_server 的配置,如果為 utf8mb4,驅動在建立連接的時候設置 SET NAMES utf8mb4
  • SET character-set = utf8mb4; 修改服務端 c++, php, python 等語言的設置。
  • 不能 ONLINE,也就是執行之後全表禁止修改,有關這方麵的討論見 [mysql 5.6 原生 Online DDL 解析](http://seanlook.com/2016/05/24/mysql-online-ddl- concept/);
  • 它可能會自動該表字段類型定義,如 VARCHAR 被轉成 MEDIUMTEXT,可以通過 MODIFY 指定類型為原類型
  • 不要隨便執行 ALTER TABLE tbl_name DEFAULT CHARACTER SET utf8mb4, 特別是當表原本不是 utf8 時,除非表是空的或者你確認表裏隻有拉丁字符 ,否則正常和亂的就混在一起了。
  • 最重要的是 ,你連接時使用的 latin1 字符集寫入了曆史數據,表定義是 latin1 或 utf8,不要期望通過 ALTER ... CONVERT ... 能夠讓你達到用 utf8 讀取曆史中文數據的目的,沒卵用,老老實實做邏輯 dump。
  • 索引鍵超長問題。Error 1071: Specified key was too long; max key length is 767 bytes。當使用 utf8mb4 編碼後,主鍵 id 的長度設置 255,太長,隻能設置小於 191 的。詳解
  • join 查詢時索引失效問題。索引失效發生在 utf8mb4 列 在條件左邊。

摘自

SET NAMES utf8mb4; 實際等同於
MySQL 字符集係統變量名簡述

其他

  • my.cnf 中的 default_character_set 設置隻影響 mysql 命令連接服務器時的連接字符集,不會對使用 libmysqlclient 庫的應用程序產生任何作用!
  • 對字段進行的 SQL 函數操作通常都是以內部操作字符集進行的,不受連接字符集設置的影響。
  • SQL 語句中的裸字符串會受到連接字符集或 introducer 設置的影響,對於比較之類的操作可能產生完全不同的結果,需要小心!

命令詳解

列出可用的字符集

1
SHOW CHARACTER SET;

或是查詢 INFORMATION_SCHEMA CHARACTER_SETS表

列出常見字符集

CharsetDescriptionDefault collationMaxlen
gb18030gb18030_chinese_ci4
gb2312gb2312_chinese_ci2
gbkgbk_chinese_ci2
utf16UTF-16 Unicodeutf16_general_ci4
utf16leUTF-16LE Unicodeutf16le_general_ci4
utf32UTF-32 Unicodeutf32_general_ci4
utf8utf8_general_ci3
utf8mb4UTF-8 Unicodeutf8mb4_0900_ai_ci4

是的,沒寫錯,utf8 已經不好意思在描述裏自稱 UTF-8 Unicode 了

列出字符集的排序規則 (校對集) 以及規則解釋

給定的字符集總是至少有一個排序規則,並且大多數字符集都有幾個排序規則。

1
SHOW COLLATION WHERE Charset = 'utf8mb4';

或查詢 INFORMATION_SCHEMA COLLATIONS表

utf8 的默認排序規則為 utf8_general_ci

列出 utf8mb4 的常見排序規則,默認為 utf8mb4_0900_ai_ci ,即是 UCA 9.0.0 版本的 Unicode,口音不敏感,不區分大小寫 。

CollationCharsetIdDefaultCompiledSortlenPad_attribute
utf8mb4_0900_ai_ciutf8mb4255YesYes0NO PAD
utf8mb4_general_ciutf8mb445Yes1PAD SPACE
utf8mb4_unicode_520_ciutf8mb4246Yes8PAD SPACE
utf8mb4_unicode_ciutf8mb4224Yes8PAD SPACE

Pad_attribute:MySQL 比較字符串時尾部空格是否忽略

NO PAD 排序規則將字符串末尾的空格處理為任何其他字符。
PAD SPACE 排序,尾部空格在比較中無關緊要;比較字符串而不考慮任何尾隨空格。

即是 PAD SPACE 會出現以下情況,MySQL 比較字符串時會忽略尾部空格。包括其他推薦的 utf8mb4_unicode_ci 和 utf8mb4_general_ci 以及所有 utf8,,這算是一個小坑,注意不要跳進來了。

1
SELECT 'a ' = 'a';
2
+------------+
3
| 'a ' = 'a' |
4
+------------+
5
|          1 |
6
+------------+

所以,使用 utf8mb4_unicode_ci 和 utf8mb4_general_ci 時,一定要做好去空格 trim 操作。

utf8mb4_general_ci 在比較和排序的時候更快。
utf8mb4_unicode_ci 是基於標準的 Unicode 來排序和比較,能夠在各種語言之間精確排序。比如 Unicode 把 ßŒ當成 ss 和 OE 來看;而 general 會把它們當成 se,再如 ÀÁÅåāă各自都與 A 相等。

MySQL 字符串比較時,_ci 不區分,_cs/_bin 區分大小寫

安裝時如果文件係統區分,如 Linux,則 lower_case_table_names 參數為 0,即區分大小寫。
如果文件係統不區分,如 Windows 或 MacOS 上,即不區分大小寫。
在 Windows 上,默認值為 1. 在 macOS 上,默認值為 2。

OSlower_case_table_names是否區分大小寫存儲數據庫、表、表別名
Linux、Unix0區分大小寫表名將按指定存儲
Windows1不區分大小寫表名將以小寫形式存儲在磁盤上
MacOS2不區分大小寫表名按照給定值存儲,但以小寫形式比較

MyISAM 引擎不支持–lower_case_table_names=0 在不區分大小寫的文件係統上啟動服務器
InnoDB 引擎,則應在所有平台上將此變量設置為 1 以強製名稱轉換為小寫字母。

後綴含義
_ai口音不敏感
_as口音敏感
_ci不區分大小寫
_cs區分大小寫
_ks假名敏感
_bin二進製,區分大小寫

Unicode 排序算法 (UCA) 版本

對於 Unicode 字符集,排序規則名稱可能包含一個版本號,以指示排序規則基於的 Unicode 排序算法(UCA)的版本。

  • utf8mb4_0900_ai_ci 基於 UCA 9.0.0

  • utf8mb4_unicode_520_ci 基於 UCA 5.2.0

  • utf8mb4_unicode_ci(沒有版本命名)基於 UCA 4.0.0

INFORMATION_SCHEMA 中的大小寫敏感

INFORMATION_SCHEMA 表格中的 字符串列具有 utf8_general_ci 大小寫不敏感的排序規則。當前在 Linux 上,lower_case_table_names 為 0,即區分大小寫。
查詢在 SCHEMATA.SCHEMA_NAME 列中 搜索 mysql 數據庫,和 MYSQL 數據庫,結果將不同。

想使用 MYSQL 查詢出 mysql

  1. 查詢時 COLLATE 指定排序規則
1
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME COLLATE utf8_general_ci = 'MYSQL';
2
+-------------+
3
| SCHEMA_NAME |
4
+-------------+
5
| mysql       |
6
+-------------+
  1. 使用 UPPER 或 LOWER
1
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE UPPER(SCHEMA_NAME) = 'MYSQL';
2
+-------------+
3
| SCHEMA_NAME |
4
+-------------+
5
| mysql       |
6
+-------------+

查詢 INFORMATION_SCHEMA 中搜索自身時,將匹配 utf8_general_ci 規則。

1
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'INFORMATION_SCHEMA';

binary 相關

binary 不同於_bin

LOWER () 和 UPPER () 對於二進製字符串是無效的 (包括 BINARY, VARBINARY, BLOB)
SELECT LOWER('aA'), UPPER('zZ');
不適用於
SET NAMES binary;
必須將字符串轉換為非二進製字符串
SELECT LOWER('aA'), LOWER(CONVERT('aA' USING latin1));

binary 可將 string 轉換為二進製 string。BINARY str 其實是 CAST (str AS BINARY) 的縮寫。
所以,對於 CHAR、VARCHAR 和 TEXT 類型,BINARY 屬性可以為列分配該列字符集的 校對規則。即
如默認不區分大小寫,可添加 binary,強製進行按字節進行比較,以區分大小寫。 如建表時未添加binary屬性,可能影響索引失效
這是我們通常在暫無能為力更改現有結構下的常規做法。

1
select * from some_table where binary str='abc';

我們可在建表建字段時,可在區分大小寫的字段上添加 binary 屬性。

對於二進製字符串,所有字符在比較中都很重要,包括尾隨空格。

1
SET NAMES binary;
2
SELECT 'a ' = 'a';
3
+------------+
4
| 'a ' = 'a' |
5
+------------+
6
|          0 |
7
+------------+

因為 binary 會按字節,區分大小寫,結尾使用 \0 填充到全部位數。
搜索時也是使用全部匹配,填充 \0 到位

1
CREATE TABLE t1 (
2
         a CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin,
3
         b BINARY(10)
4
       );
5
INSERT INTO t1 VALUES ('a','a');
6
SELECT HEX(a), HEX(b),b = 'a',b = 'a\0\0\0\0\0\0\0\0\0' FROM t1;
7
+--------+----------------------+---------+---------------------------+
8
| HEX(a) | HEX(b)               | b = 'a' | b = 'a\0\0\0\0\0\0\0\0\0' |
9
+--------+----------------------+---------+---------------------------+
10
| 61     | 61000000000000000000 |       0 |                         1 |
11
+--------+----------------------+---------+---------------------------+

b 已不等於’a’
所以,有時候,如果將索引列轉換為 BINARY, MySQL 可能不會使用索引。

varbinary 保存變長的字符串,後麵不會補 \0

查看服務器正使用的連接的字符集和排序規則

1
SHOW VARIABLES LIKE 'character_set%';
2
SHOW VARIABLES LIKE 'collation%';

MySQL 字符集係統變量名簡述

係統變量名簡述
character_set_system元數據 Metadata 使用,即 USER (), CURRENT_USER (), SESSION_USER (), SYSTEM_USER (), DATABASE (), and VERSION () 等 functions
character_set_server、collation_server服務器對應的內部操作使用
character_set_database、collation_database當前選中數據庫的默認
character_set_client客戶端來源數據
character_set_connection、collation_connection服務器在接收時
character_set_results服務器查詢結果集返回到客戶端

另外,查詢時指定字符集

SET NAMES utf8mb4 實際等同於

1
SET NAMES utf8mb4;

等同於

1
SET character_set_client = utf8mb4;
2
SET character_set_results = utf8mb4;
3
SET character_set_connection = utf8mb4;

MySQL 中的字符集轉換過程

  1. MySQL Server 收到請求時將請求數據從 character_set_client 轉換為 character_set_connection;

  2. 進行內部操作前將請求數據從 character_set_connection 轉換為內部操作字符集,其確定方法如下:

    • 使用每個數據字段的 CHARACTER SET 設定值;

    • 若上述值不存在,則使用對應數據表的 DEFAULT CHARACTER SET 設定值 (MySQL 擴展,非 SQL 標準);

    • 若上述值不存在,則使用對應數據庫的 DEFAULT CHARACTER SET 設定值;

    • 若上述值不存在,則使用 character_set_server 設定值。

  3. 將操作結果從內部操作字符集轉換為 character_set_results。

查看指定數據庫 db_name 的字符集和排序規則

1
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'db_name';

1
USE db_name;
2
SELECT @@character_set_database, @@collation_database;

查詢字符串時,顯示指定字符集

語法:

1
[_charset_name]'string' [COLLATE collation_name]

示例:

1
SELECT _utf8'abc' COLLATE utf8_danish_ci;

創建和修改庫、表、列的字符集和排序規則

創建和修改數據庫 db_name 時,都可指定

1
CREATE DATABASE db_name
2
    [[DEFAULT] CHARACTER SET charset_name]
3
    [[DEFAULT] COLLATE collation_name]
4
5
ALTER DATABASE db_name
6
    [[DEFAULT] CHARACTER SET charset_name]
7
    [[DEFAULT] COLLATE collation_name]

創建和修改指定表 tbl_name 的字符集和排序規則

1
CREATE TABLE tbl_name (column_list)
2
    [[DEFAULT] CHARACTER SET charset_name]
3
    [COLLATE collation_name]]
4
5
ALTER TABLE tbl_name
6
    [[DEFAULT] CHARACTER SET charset_name]
7
    [COLLATE collation_name]

創建和修改表指定列 col_name 的字符集和排序規則

1
col_name {CHAR | VARCHAR | TEXT} (col_length)
2
    [CHARACTER SET charset_name]
3
    [COLLATE collation_name]

also be used for ENUM and SET columns:

1
col_name {ENUM | SET} (val_list)
2
    [CHARACTER SET charset_name]
3
    [COLLATE collation_name]

修改為 utf8mb4 示例

依次修改數據庫,表,表字段

1
# 修改數據庫:
2
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
3
# 修改表:
4
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
5
# 修改表字段:
6
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

索引鍵超長

InnoDB 中使用 COMPACT 或 REDUNDANT 格式的表的單個索引的最大長度為 767 字節,因此對於 utf8mb3 或 utf8mb4 的列,可以分別索引最多 255 或 191 個字符。
字段定義的是能存儲的字符數,比如 VARCHAR (200) 代表能夠存 200 個漢字,索引定義是字符集類型最大長度算的,即 utf8 maxbytes=3, utf8mb4 maxbytes=4,算下來 utf8 和 utf8mb4 兩種情況的索引長度分別為 600 bytes 和 800bytes,後者超過了 768,導致出錯 Error 1071: Specified key was too long; max key length is 767 bytes。 。

1
col1 VARCHAR(500) CHARACTER SET utf8, INDEX (col1(255))
2
col1 VARCHAR(500) CHARACTER SET utf8mb4, INDEX (col1(191))
  • InnoDB 中 COMPRESSED 或 DYNAMIC 最多 3072 字節,所以分別為最多 1024 或 768 個字符 utf8mb3 或 utf8mb4 列。但也依然不建議索引太長,太浪費空間和 cpu 搜索資源。

如果已有定義超過這個長度的,可加上前綴索引,如果暫不能加上前綴索引(像唯一索引),可把該字段的字符集改回 utf8 或 latin1。
但是,( 敲黑板啦,很重要 ),要防止出現 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '=' 錯誤:連接字符集使用 utf8mb4,但 SELECT/UPDATE where 條件有 utf8 類型的列,且條件右邊存在不屬於 utf8 字符,就會觸發該異常。表示踩過這個坑。

再多加一個友好提示:EXPLAIN 結果裏麵的 key_len 指的搜索索引長度,單位是 bytes,而且是以字符集支持的單字符最大字節數算的,這也是為什麼 INDEX_LENGTH 膨脹厲害的一個原因。

InnoDB 默認表的行格式

在 MySQL 5.0.3 之前,REDUNDANT 是 InnoDB 唯一可用的行格式。
從 MySQL 5.0.3 到 MySQL 5.7.8 的默認行格式,為 compact 數據格式。
從 MySQL 5.7.9 開始,默認行格式由 innodb_default_row_format 配置選項定義, 默認設置為 DYNAMIC 。

更改係統表空間默認行格式 DYNAMIC, COMPACT,和 REDUNDANT。

1
SET GLOBAL innodb_default_row_format=DYNAMIC;

COMPRESSED 隻能在 CREATE TABLE 或 ALTER TABLE 時明確指定。

1
CREATE TABLE t2 (c1 INT) ROW_FORMAT=COMPRESSED;
2
ALTER TABLE t1 ADD COLUMN (c2 INT);

如未指定 ROW_FORMAT 將使用 innodb_default_row_format,也就是實際上會補充為 ROW_FORMAT=DEFAULT

使用 INFORMATION_SCHEMA.INNODB_TABLES 查詢表信息。

1
SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE 'test/t1' \G
2
*************************** 1. row ***************************
3
     TABLE_ID: 54
4
         NAME: test/t1
5
         FLAG: 33
6
       N_COLS: 4
7
        SPACE: 35
8
   ROW_FORMAT: Dynamic
9
ZIP_PAGE_SIZE: 0
10
   SPACE_TYPE: Single

查看表的行格式

tbl_name 表名

1
SHOW TABLE STATUS WHERE name LIKE 'tbl_name%';
2
#或
3
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'tbl_name%';

InnoDB 最大表空間大小,表的最大大小

innodb_page_size 隻能在初始化 MySQL 實例之前進行配置。默認的 16KB 頁麵大小或更大適用於各種工作負載,特別是涉及涉及批量更新的表掃描和 DML 操作的查詢。

InnoDB 頁麵大小最大表空間大小
4KB16TB
8KB32TB
16KB64TB
32KB128TB
64KB256TB

最大表空間大小也是表的最大大小。

MySQL 客戶端程序理論上連接時,默認使用 utf8mb4。也可以直接使用 --default-character-set 指定。

參考鏈接:
全麵了解 mysql 中 utf8 和 utf8mb4 的區別
mysql 使用 utf8mb4 經驗吐血總結

评论

此博客中的热门博文

Docker 多階段構建示例 + Docker Hexo