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表

列出常見字符集

Charset	Description	Default collation	Maxlen
gb18030		gb18030_chinese_ci	4
gb2312		gb2312_chinese_ci	2
gbk		gbk_chinese_ci	2
utf16	UTF-16 Unicode	utf16_general_ci	4
utf16le	UTF-16LE Unicode	utf16le_general_ci	4
utf32	UTF-32 Unicode	utf32_general_ci	4
utf8		utf8_general_ci	3
utf8mb4	UTF-8 Unicode	utf8mb4_0900_ai_ci	4

是的，沒寫錯，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，口音不敏感，不區分大小寫。

Collation	Charset	Id	Default	Compiled	Sortlen	Pad_attribute
utf8mb4_0900_ai_ci	utf8mb4	255	Yes	Yes	0	NO PAD
utf8mb4_general_ci	utf8mb4	45		Yes	1	PAD SPACE
utf8mb4_unicode_520_ci	utf8mb4	246		Yes	8	PAD SPACE
utf8mb4_unicode_ci	utf8mb4	224		Yes	8	PAD SPACE

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

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

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

SELECT 'a ' = 'a';
+------------+
| 'a ' = 'a' |
+------------+
|          1 |
+------------+

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

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

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

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

OS	lower_case_table_names	是否區分大小寫	存儲數據庫、表、表別名
Linux、Unix	0	區分大小寫	表名將按指定存儲
Windows	1	不區分大小寫	表名將以小寫形式存儲在磁盤上
MacOS	2	不區分大小寫	表名按照給定值存儲，但以小寫形式比較

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

查詢時 COLLATE 指定排序規則

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME COLLATE utf8_general_ci = 'MYSQL';
+-------------+
| SCHEMA_NAME |
+-------------+
| mysql       |
+-------------+

使用 UPPER 或 LOWER

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE UPPER(SCHEMA_NAME) = 'MYSQL';
+-------------+
| SCHEMA_NAME |
+-------------+
| mysql       |
+-------------+

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

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 屬性。

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

SET NAMES binary;
SELECT 'a ' = 'a';
+------------+
| 'a ' = 'a' |
+------------+
|          0 |
+------------+

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

CREATE TABLE t1 (
         a CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin,
         b BINARY(10)
       );
INSERT INTO t1 VALUES ('a','a');
SELECT HEX(a), HEX(b),b = 'a',b = 'a\0\0\0\0\0\0\0\0\0' FROM t1;
+--------+----------------------+---------+---------------------------+
| HEX(a) | HEX(b)               | b = 'a' | b = 'a\0\0\0\0\0\0\0\0\0' |
+--------+----------------------+---------+---------------------------+
| 61     | 61000000000000000000 |       0 |                         1 |
+--------+----------------------+---------+---------------------------+

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

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

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

SHOW VARIABLES LIKE 'character_set%';
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;

等同於

SET character_set_client = utf8mb4;
SET character_set_results = utf8mb4;
SET character_set_connection = utf8mb4;

MySQL 中的字符集轉換過程

MySQL Server 收到請求時將請求數據從 character_set_client 轉換為 character_set_connection；
進行內部操作前將請求數據從 character_set_connection 轉換為內部操作字符集，其確定方法如下：
- 使用每個數據字段的 CHARACTER SET 設定值；
- 若上述值不存在，則使用對應數據表的 DEFAULT CHARACTER SET 設定值 (MySQL 擴展，非 SQL 標準)；
- 若上述值不存在，則使用對應數據庫的 DEFAULT CHARACTER SET 設定值；
- 若上述值不存在，則使用 character_set_server 設定值。
將操作結果從內部操作字符集轉換為 character_set_results。

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

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

或

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

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

語法：

1	[_charset_name]'string' [COLLATE collation_name]

示例：

1	SELECT _utf8'abc' COLLATE utf8_danish_ci;

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

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

CREATE DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]
ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

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

CREATE TABLE tbl_name (column_list)
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]]
ALTER TABLE tbl_name
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]

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

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

also be used for ENUM and SET columns:

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

修改為 utf8mb4 示例

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

# 修改數據庫:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# 修改表:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 修改表字段:
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。 。

col1 VARCHAR(500) CHARACTER SET utf8, INDEX (col1(255))
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 時明確指定。

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

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

使用 INFORMATION_SCHEMA.INNODB_TABLES 查詢表信息。

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

查看表的行格式

tbl_name 表名

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

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

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

InnoDB 頁麵大小	最大表空間大小
4KB	16TB
8KB	32TB
16KB	64TB
32KB	128TB
64KB	256TB

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

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

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

搜索此博客

第二天堂

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

原文

概述