永遠(yuǎn)不要在 MySQL 中使用 utf8，并且始終使用 utf8mb4 。

utf8mb4介紹

MySQL/MariaDB中，utf8字符集并不是對(duì)Unicode的真正實(shí)現(xiàn)，即不是真正的UTF-8編碼，因?yàn)樗С肿疃?個(gè)字節(jié)的字符。
作為可變長(zhǎng)字符編碼，UTF-8最多支持4個(gè)字節(jié)，對(duì)于emoji、中日韓繁體等部分字符，需要使用4個(gè)字節(jié)才能完整存儲(chǔ)。也就是，如果想要支持全部的Unicode編碼，MySQL/MariaDB中的utf8是不夠的。

UTF-8 編碼可以表示 Unicode 字符集中的每個(gè)符號(hào)，范圍從 U+000000 到 U+10FFFF。那是 1,114,112 個(gè)可能的符號(hào)（并非所有這些 Unicode 代碼點(diǎn)都被分配了字符，但這并不影響UTF-8對(duì)其進(jìn)行編碼。Unicode實(shí)際映射了字符的共有1,112,064個(gè)碼位）。UTF-8 是一種可變寬度編碼；它使用一到四個(gè)(8位bit)字節(jié)對(duì)每個(gè)符號(hào)進(jìn)行編碼。這樣既可以節(jié)省存儲(chǔ)低代碼值的空間，

又能充分表示所有的字符。

可以說(shuō)UTF-8是最受歡迎的Unicode編碼實(shí)現(xiàn)。

而MySQL/MariaDB真正實(shí)現(xiàn)UTF-8編碼的是utf8mb4，最多支持4個(gè)字節(jié)的存儲(chǔ)。

總共有 1,048,576 個(gè)無(wú)法使用的可能代碼點(diǎn)。MySQL 的 utf8 只允許存儲(chǔ)所有可能的 Unicode 代碼點(diǎn)的 5.88% ((0x00FFFF + 1) / (0x10FFFF + 1))。正確的 UTF-8 可以編碼 100% 的所有 Unicode 代碼點(diǎn)。

utf8字節(jié)數(shù)超出的報(bào)錯(cuò)

如果使用utf8，存儲(chǔ)emoji等4個(gè)字節(jié)的字符時(shí)，通常會(huì)報(bào)錯(cuò) Error: 1366 錯(cuò)誤：

[Err] 1366 - Incorrect string value: '\xF0\x9F\x98\x93' for column 'xxx' at row xx

[Error] 1366 - Incorrect string value: '\x...' for column 'xxx' at row xx

utf8mb4支持

MySQL/MariaDB 中的utf8mb4是對(duì)原先utf8只能存儲(chǔ)3個(gè)字節(jié)大小的字符的一種補(bǔ)充，是一種真正的UTF-8編碼。

MySQL 5.5.3+ 版本之后開(kāi)始支持。

從 MySQL 8.0 開(kāi)始默認(rèn)的字符集已經(jīng)變?yōu)?utf8mb4。也就是使用MySQL 8.0及以上版本，就不用擔(dān)心字節(jié)長(zhǎng)度問(wèn)題。
MariaDB 10.x 版本默認(rèn)的字符集仍是 latin1。因此通常情況下，應(yīng)該修改為utf8mb4。

utf8mb4對(duì)應(yīng)常用的排序規(guī)則為：utf8mb4_general_ci 和 utf8mb4_unicode_ci。

將默認(rèn)字符編碼設(shè)置為utf8mb4，及對(duì)應(yīng)排序規(guī)則。

查看當(dāng)前編碼

登陸MySQL/MariaDB之后，使用SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';查看字符集和排序規(guī)則。

查看字符集：

MariaDB [(none)]> show variables like 'character%';
+--------------------------+------------------------------------------------+
| Variable_name            | Value                                          |
+--------------------------+------------------------------------------------+
| character_set_client     | gbk                                            |
| character_set_connection | gbk                                            |
| character_set_database   | utf8                                           |
| character_set_filesystem | binary                                         |
| character_set_results    | gbk                                            |
| character_set_server     | utf8                                           |
| character_set_system     | utf8                                           |
| character_sets_dir       | C:\Program Files\MariaDB 10.3n\share\charsets\ |
+--------------------------+------------------------------------------------+
8 rows in set (0.043 sec)

由于是在中文Windows下安裝的，所以含有g(shù)bk。

修改MySQL/Mariadb的配置文件，將utf8編碼改為utf8mb4

• Linux系統(tǒng)下，修改 /etc/my.cnf 或 /etc/my.cnf.d/server.cnf 文件，在對(duì)應(yīng)[mysql]、[mysqld]等下面添加內(nèi)容如下：
• Windows系統(tǒng)下，修改MySQL/Mariadb安裝目錄下data/my.ini文件，同樣在對(duì)應(yīng)[mysql]、[mysqld]等下面添加內(nèi)容。

Linux下my.cnf ：

> vim /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

[mysqld]
character-set-server = utf8mb4
init_connect='SET NAMES utf8mb4'
collation-server=utf8mb4_unicode_ci
character-set-client-handshake=FALSE

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Windows下my.ini ：

[mysqld]
datadir=C:/Program Files/MariaDB 10.3n/data
port=3306
character_set_server=utf8mb4
# Using unique option prefix 'character_set_client' is error-prone and can break in the future. Please use the full name 'character-set-client-handshake' instead.
character-set-client-handshake=utf8mb4  
# character_set_client=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
skip-character-set-client-handshake=false
character_set_filesystem = binary
lower_case_table_names=2 

[mysqldump]
loose_character_set_client=utf8mb4

[mysql]
default-character-set=utf8mb4

[client]
port=3306
plugin-dir=C:/Program Files/MariaDB 10.3n/lib/plugin
default-character-set=utf8mb4

幾個(gè)設(shè)置項(xiàng)說(shuō)明：

• character-set-client-handshake=FALSE 可以影響collation_connection的結(jié)果為utf8mb4_unicode_ci，而不是utf8mb4_general_ci。當(dāng)然，使用SET collation_connection = utf8mb4_unicode_ci或collation_connection = utf8mb4_unicode_ci也可以。
• SET NAMES 指示客戶(hù)端連接使用的字符集，即向服務(wù)器發(fā)送 SQL 語(yǔ)句的字符集。 character-set-server 設(shè)置服務(wù)器字符集。要正確使用 utf8mb4，需要確?？蛻?hù)端、服務(wù)器和連接都設(shè)置為 utf8mb4。
• init_connect等所有其他有關(guān)字符集的默認(rèn)設(shè)置都會(huì)繼承自character-set-server，也即單獨(dú)指定init-connect、character_set_client, character_set_results,character_set_connection等都是不必須的。因此，上面設(shè)置中的init_connect可以省略。
• 連接的編碼值，可以使 MySQL 正確解碼輸入并對(duì)結(jié)果進(jìn)行編碼。 否則會(huì)在內(nèi)部重新編碼處理。

純凈的精簡(jiǎn)的關(guān)于utf8mb4字符集和排序規(guī)則的設(shè)置，如下，只需要5項(xiàng)設(shè)置即可：

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

重啟MySQL/MariaDB

Linux下使用systemctl restart mariadb或systemctl restart mysqld。必須使用restart重啟(非reload)。

低版本MariaDB啟動(dòng)或重啟的服務(wù)名為mysqld(mysqld.service)。systemctl restart mysqld

Windows下打開(kāi)“服務(wù)”，在服務(wù)中找到MariaDB或MySQL對(duì)應(yīng)的服務(wù)，右鍵重啟即可。

再次查看字符集和排序規(guī)則

上面的修改重啟生效后，再次查看，如下，已經(jīng)變成為utf8mb4和utf8mb4_unicode_ci。

MariaDB [(none)]> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.008 sec)

character_set_filesystem和character_set_system的說(shuō)明

上面顯示的字符集和排序規(guī)則可以看到，有兩項(xiàng)并不是utf8mb4。

• character_set_filesystem 應(yīng)該是二進(jìn)制的，除非你使用的文件系統(tǒng)的文件名中支持多字節(jié) UTF-8 編碼字符。
• character_set_system 始終是 utf8 并且不能被覆蓋。

關(guān)于字符集設(shè)置的其他參考

上面的設(shè)置已經(jīng)是正確的修改utf8mb4的設(shè)置。下面將可能的幾個(gè)相關(guān)設(shè)置項(xiàng)列出來(lái)，供可能的參考(雖然基本用不到)：

[client]
default-character-set                     = utf8mb4

[mysql]
default-character-set                     = utf8mb4

[mysqld]
explicit_defaults_for_timestamp           = 1 # Posting it here as a tip to disable the Timestamp message, maybe it can help someone :)
character-set-client-handshake            = 0 # FALSE
init_connect                              = 'SET character_set_system = utf8mb4'
init_connect                              = 'SET character_set_connection = utf8mb4'
init_connect                              = 'SET character_set_database = utf8mb4'
init_connect                              = 'SET character_set_results = utf8mb4'
init_connect                              = 'SET collation_database = utf8mb4_unicode_ci'
init_connect                              = 'SET collation_connection = utf8mb4_unicode_ci'
init_connect                              = 'SET NAMES utf8mb4'
character-set-server                      = utf8mb4
#character_set_client                     = utf8mb4
collation-server                          = utf8mb4_unicode_ci
collation_connection                      = utf8mb4_unicode_ci
collation_database                        = utf8mb4_unicode_ci

多個(gè)init_connect也可以這樣設(shè)置：init_connect  = 'SET collation_connection = utf8mb4_unicode_ci,NAMES utf8mb4'。未驗(yàn)證

現(xiàn)有數(shù)據(jù)庫(kù)切換字符集到utf8mb4的完整過(guò)程

如果數(shù)據(jù)庫(kù)創(chuàng)建之初就是utf8mb4，則就沒(méi)有這些煩心事了?。。?br />

第 1 步：創(chuàng)建備份
創(chuàng)建要升級(jí)的服務(wù)器上所有數(shù)據(jù)庫(kù)的備份。安全第一！

第 2 步：升級(jí)MySQL服務(wù)器
將 MySQL 服務(wù)器升級(jí)到 v5.5.3+。MySQL升級(jí)到8.0就不用第5步MySQL服務(wù)器字符集的修改了，默認(rèn)就是utf8mb4。MariaDB還需要修改字符集。

第 3 步：修改數(shù)據(jù)庫(kù)、表和列
將數(shù)據(jù)庫(kù)、表和列的字符集和排序規(guī)則屬性更改為使用 utf8mb4 。

# For each database:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# For each table:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# For each column:
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# （不要盲目復(fù)制粘貼！具體的語(yǔ)句取決于列類(lèi)型、最大長(zhǎng)度和其他屬性。上面這行只是一個(gè)`VARCHAR`列的例子。）
# 或者modefy語(yǔ)句
ALTER TABLE table_name MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

第 4 步：檢查列和索引鍵的最大長(zhǎng)度

這可能是整個(gè)升級(jí)過(guò)程中最乏味的部分。

從 utf8 轉(zhuǎn)換為 utf8mb4 時(shí)，列或索引鍵的最大長(zhǎng)度以字節(jié)為單位不變。因此，它在字符方面更小，因?yàn)樽址淖畲箝L(zhǎng)度現(xiàn)在是四個(gè)字節(jié)而不是三個(gè)字節(jié)。

例如，TINYTEXT 列最多可容納 255 個(gè)字節(jié)，這與 85 個(gè)三字節(jié)字符或 63 個(gè)四字節(jié)字符相關(guān)。假設(shè)你有一個(gè)使用 utf8 的 TINYTEXT 列，但必須能夠包含 63 個(gè)以上的字符。鑒于此要求，無(wú)法將此列轉(zhuǎn)換為 utf8mb4，除非還將數(shù)據(jù)類(lèi)型更改為更長(zhǎng)的類(lèi)型，例如 TEXT — 因?yàn)槿绻銍L試用四字節(jié)字符填充它，將只能輸入 63 個(gè)字符，但不能更多。
索引鍵也是如此。 InnoDB 存儲(chǔ)引擎的最大索引長(zhǎng)度為 767 字節(jié)，因此對(duì)于 utf8 或 utf8mb4 列，您最多可以分別索引 255 或 191 個(gè)字符。如果您當(dāng)前有索引長(zhǎng)度超過(guò) 191 個(gè)字符的 utf8 列，則在使用 utf8mb4 時(shí)需要索引較少數(shù)量的字符。 （因此，我不得不將一些索引的 VARCHAR(255) 列更改為 VARCHAR(191)。）

第 5 步：修改連接、客戶(hù)端和服務(wù)器字符集

在應(yīng)用程序代碼中，將連接字符集設(shè)置為 utf8mb4。這可以通過(guò)簡(jiǎn)單地用 SET NAMES utf8mb4 替換 SET NAMES utf8 來(lái)完成。同時(shí)排序規(guī)則也要對(duì)應(yīng)修改，例如 SET NAMES utf8 COLLATE utf8_unicode_ci 變?yōu)?SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci。

確保同時(shí)設(shè)置客戶(hù)端和服務(wù)器字符集。

MySQL 配置文件 (/etc/my.cnf) 中有以下內(nèi)容：

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

第 6 步：修復(fù)優(yōu)化所有表（ Repair and optimize ）
升級(jí) MySQL 服務(wù)器并進(jìn)行上述必要更改后，請(qǐng)確保修復(fù)和優(yōu)化所有數(shù)據(jù)庫(kù)和表。否則可能會(huì)遇到奇怪的錯(cuò)誤，即使沒(méi)有錯(cuò)誤被拋出。
可以為要修復(fù)和優(yōu)化的每個(gè)表運(yùn)行以下 MySQL 查詢(xún)：

# For each table
REPAIR TABLE table_name;
OPTIMIZE TABLE table_name;

該工作，可以使用命令行 mysqlcheck 實(shí)用程序一次性輕松完成：

$ mysqlcheck -u root -p --auto-repair --optimize --all-databases

這將提示輸入root用戶(hù)的密碼，之后將修復(fù)和優(yōu)化所有數(shù)據(jù)庫(kù)中的所有表。

參考

主要參考翻譯自：How to support full Unicode in MySQL databases，歡迎閱讀原文。

到此這篇關(guān)于MySQL/MariaDB中如何支持全部的Unicode的文章就介紹到這了,更多相關(guān)MySQL/MariaDB支持Unicode內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！