目錄
- utf8mb4介紹
- utf8字節數超出的報錯
- utf8mb4支持
- 將默認字符編碼設置為utf8mb4，及對應排序規則。
- 查看當前編碼
- 修改MySQL/Mariadb的配置文件，將utf8編碼改為utf8mb4
- 重啟MySQL/MariaDB
- 再次查看字符集和排序規則
- character_set_filesystem和character_set_system的說明
- 關于字符集設置的其他參考
- 現有數據庫切換字符集到utf8mb4的完整過程
- 參考
永遠不要在 MySQL 中使用 utf8，并且始終使用 utf8mb4 。
utf8mb4介紹
MySQL/MariaDB中，utf8字符集并不是對Unicode的真正實現，即不是真正的UTF-8編碼，因為它支持最多3個字節的字符。
作為可變長字符編碼，UTF-8最多支持4個字節，對于emoji、中日韓繁體等部分字符，需要使用4個字節才能完整存儲。也就是，如果想要支持全部的Unicode編碼，MySQL/MariaDB中的utf8是不夠的。
UTF-8 編碼可以表示 Unicode 字符集中的每個符號，范圍從 U+000000 到 U+10FFFF。那是 1,114,112 個可能的符號（并非所有這些 Unicode 代碼點都被分配了字符，但這并不影響UTF-8對其進行編碼。Unicode實際映射了字符的共有1,112,064個碼位）。UTF-8 是一種可變寬度編碼；它使用一到四個(8位bit)字節對每個符號進行編碼。這樣既可以節省存儲低代碼值的空間，
又能充分表示所有的字符。
可以說UTF-8是最受歡迎的Unicode編碼實現。
而MySQL/MariaDB真正實現UTF-8編碼的是utf8mb4，最多支持4個字節的存儲。
總共有 1,048,576 個無法使用的可能代碼點。MySQL 的 utf8 只允許存儲所有可能的 Unicode 代碼點的 5.88% ((0x00FFFF + 1) / (0x10FFFF + 1))。正確的 UTF-8 可以編碼 100% 的所有 Unicode 代碼點。
utf8字節數超出的報錯
如果使用utf8，存儲emoji等4個字節的字符時，通常會報錯 Error: 1366 錯誤：
[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是對原先utf8只能存儲3個字節大小的字符的一種補充，是一種真正的UTF-8編碼。
MySQL 5.5.3+ 版本之后開始支持。
從 MySQL 8.0 開始默認的字符集已經變為 utf8mb4。也就是使用MySQL 8.0及以上版本，就不用擔心字節長度問題。
MariaDB 10.x 版本默認的字符集仍是 latin1。因此通常情況下，應該修改為utf8mb4。
utf8mb4對應常用的排序規則為：utf8mb4_general_ci 和 utf8mb4_unicode_ci。
將默認字符編碼設置為utf8mb4，及對應排序規則。
查看當前編碼
登陸MySQL/MariaDB之后，使用SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';查看字符集和排序規則。
查看字符集：
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下安裝的，所以含有gbk。
修改MySQL/Mariadb的配置文件，將utf8編碼改為utf8mb4
- Linux系統下，修改 /etc/my.cnf 或 /etc/my.cnf.d/server.cnf 文件，在對應[mysql]、[mysqld]等下面添加內容如下：
- Windows系統下，修改MySQL/Mariadb安裝目錄下data/my.ini文件，同樣在對應[mysql]、[mysqld]等下面添加內容。
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
幾個設置項說明：
- character-set-client-handshake=FALSE 可以影響collation_connection的結果為utf8mb4_unicode_ci，而不是utf8mb4_general_ci。當然，使用SET collation_connection = utf8mb4_unicode_ci或collation_connection = utf8mb4_unicode_ci也可以。
- SET NAMES 指示客戶端連接使用的字符集，即向服務器發送 SQL 語句的字符集。 character-set-server 設置服務器字符集。要正確使用 utf8mb4，需要確保客戶端、服務器和連接都設置為 utf8mb4。
- init_connect等所有其他有關字符集的默認設置都會繼承自character-set-server，也即單獨指定init-connect、character_set_client, character_set_results,character_set_connection等都是不必須的。因此，上面設置中的init_connect可以省略。
- 連接的編碼值，可以使 MySQL 正確解碼輸入并對結果進行編碼。 否則會在內部重新編碼處理。
純凈的精簡的關于utf8mb4字符集和排序規則的設置，如下，只需要5項設置即可：
[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啟動或重啟的服務名為mysqld(mysqld.service)。systemctl restart mysqld
Windows下打開“服務”，在服務中找到MariaDB或MySQL對應的服務，右鍵重啟即可。
再次查看字符集和排序規則
上面的修改重啟生效后，再次查看，如下，已經變成為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的說明
上面顯示的字符集和排序規則可以看到，有兩項并不是utf8mb4。
- character_set_filesystem 應該是二進制的，除非你使用的文件系統的文件名中支持多字節 UTF-8 編碼字符。
- character_set_system 始終是 utf8 并且不能被覆蓋。
關于字符集設置的其他參考
上面的設置已經是正確的修改utf8mb4的設置。下面將可能的幾個相關設置項列出來，供可能的參考(雖然基本用不到)：
[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
多個init_connect也可以這樣設置：init_connect = 'SET collation_connection = utf8mb4_unicode_ci,NAMES utf8mb4'。未驗證
現有數據庫切換字符集到utf8mb4的完整過程
如果數據庫創建之初就是utf8mb4，則就沒有這些煩心事了！！！
第 1 步：創建備份
創建要升級的服務器上所有數據庫的備份。安全第一！
第 2 步：升級MySQL服務器
將 MySQL 服務器升級到 v5.5.3+。MySQL升級到8.0就不用第5步MySQL服務器字符集的修改了，默認就是utf8mb4。MariaDB還需要修改字符集。
第 3 步：修改數據庫、表和列
將數據庫、表和列的字符集和排序規則屬性更改為使用 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;
# （不要盲目復制粘貼！具體的語句取決于列類型、最大長度和其他屬性。上面這行只是一個`VARCHAR`列的例子。）
# 或者modefy語句
ALTER TABLE table_name MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
第 4 步：檢查列和索引鍵的最大長度
這可能是整個升級過程中最乏味的部分。
從 utf8 轉換為 utf8mb4 時，列或索引鍵的最大長度以字節為單位不變。因此，它在字符方面更小，因為字符的最大長度現在是四個字節而不是三個字節。
例如，TINYTEXT 列最多可容納 255 個字節，這與 85 個三字節字符或 63 個四字節字符相關。假設你有一個使用 utf8 的 TINYTEXT 列，但必須能夠包含 63 個以上的字符。鑒于此要求，無法將此列轉換為 utf8mb4，除非還將數據類型更改為更長的類型，例如 TEXT — 因為如果你嘗試用四字節字符填充它，將只能輸入 63 個字符，但不能更多。
索引鍵也是如此。 InnoDB 存儲引擎的最大索引長度為 767 字節，因此對于 utf8 或 utf8mb4 列，您最多可以分別索引 255 或 191 個字符。如果您當前有索引長度超過 191 個字符的 utf8 列，則在使用 utf8mb4 時需要索引較少數量的字符。 （因此，我不得不將一些索引的 VARCHAR(255) 列更改為 VARCHAR(191)。）
第 5 步：修改連接、客戶端和服務器字符集
在應用程序代碼中，將連接字符集設置為 utf8mb4。這可以通過簡單地用 SET NAMES utf8mb4 替換 SET NAMES utf8 來完成。同時排序規則也要對應修改，例如 SET NAMES utf8 COLLATE utf8_unicode_ci 變為 SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci。
確保同時設置客戶端和服務器字符集。
MySQL 配置文件 (/etc/my.cnf) 中有以下內容：
[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 步：修復優化所有表（ Repair and optimize ）
升級 MySQL 服務器并進行上述必要更改后，請確保修復和優化所有數據庫和表。否則可能會遇到奇怪的錯誤，即使沒有錯誤被拋出。
可以為要修復和優化的每個表運行以下 MySQL 查詢：
# For each table
REPAIR TABLE table_name;
OPTIMIZE TABLE table_name;
該工作，可以使用命令行 mysqlcheck 實用程序一次性輕松完成：
$ mysqlcheck -u root -p --auto-repair --optimize --all-databases
這將提示輸入root用戶的密碼，之后將修復和優化所有數據庫中的所有表。
參考
主要參考翻譯自：How to support full Unicode in MySQL databases，歡迎閱讀原文。
到此這篇關于MySQL/MariaDB中如何支持全部的Unicode的文章就介紹到這了,更多相關MySQL/MariaDB支持Unicode內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- 遠程連接mysql錯誤代碼1130的解決方法
- mysql error 1130 hy000:Host''localhost''解決方案
- MySQL連接異常報10061錯誤問題解決
- mac安裝mysql數據庫及配置環境變量的圖文教程
- 基于JavaSwing+mysql開發一個學生社團管理系統設計和實現
- Linux下MySQL多實例部署及安裝指南
- docker 部署mysql連接問題及遇到的坑
- MySQL 1130異常,無法遠程登錄解決方案詳解
