mysql字符集設置和轉換的核心是統一使用utf8mb4以支持所有unicode字符，包括emoji。1. 服務器級別設置通過修改my.cnf或my.ini文件中的character-set-server和collation-server參數實現；2. 數據庫級別在創建或修改數據庫時指定character set和collate；3. 表級別在建表或修改表時設定字符集；4. 字段級別在定義字段時單獨指定字符集；5. 客戶端連接時執行set names utf8mb4確保傳輸過程編碼一致；6. 遇到亂碼問題需檢查各層級設置并進行相應調整，優先使用utf8mb4以避免兼容性問題。正確配置后可通過show variables和show create命令驗證設置是否生效，從而有效防止亂碼并保障數據完整性。
mysql字符集設置，簡單來說，就是告訴MySQL數據庫，你的數據用什么編碼方式來存儲和處理。UTF8是目前最常用的選擇，因為它能支持絕大多數語言的字符，避免亂碼問題。字符集轉換則是在不同編碼之間切換，確保數據正確顯示。
MySQL字符集設置和轉換，涉及到服務器、數據庫、表、字段四個層級。
服務器級別字符集設置
服務器級別的字符集設置影響著整個MySQL實例的默認字符集。修改服務器字符集，需要修改MySQL的配置文件（通常是my.cnf或my.ini）。
- 找到配置文件：通常位于/etc/mysql/my.cnf或C:ProgramDataMySQLMySQL Server X.Xmy.ini (X.X代表版本號)。
- 編輯配置文件：在[mysqld]部分添加或修改以下內容：
[mysqld] character-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci
character-set-server 定義了服務器默認的字符集，collation-server 定義了服務器默認的排序規則。utf8mb4 是推薦的UTF8變種，因為它能存儲所有Unicode字符，包括Emoji。utf8mb4_unicode_ci 是一種不區分大小寫的排序規則。
- 重啟MySQL服務器：使配置生效。
sudo systemctl restart mysql # linux
或者在windows服務管理器中重啟MySQL服務。
- 驗證設置：登錄MySQL，執行以下命令：
SHOW VARIABLES LIKE 'character_set_server'; SHOW VARIABLES LIKE 'collation_server';
如果輸出結果顯示 character_set_server 和 collation_server 的值分別為 utf8mb4 和 utf8mb4_unicode_ci，則表示服務器級別的字符集設置已成功。
數據庫級別字符集設置
數據庫級別的字符集設置會覆蓋服務器級別的設置，影響數據庫中所有表的默認字符集。
- 創建數據庫時指定字符集：
CREATE DATABASE your_database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
- 修改現有數據庫字符集：
ALTER DATABASE your_database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
- 驗證設置：
SHOW CREATE DATABASE your_database_name;
查看輸出結果，確認數據庫的字符集和排序規則已更新。
表級別字符集設置
表級別的字符集設置會覆蓋數據庫級別的設置，影響表中所有字段的默認字符集。
- 創建表時指定字符集：
CREATE TABLE your_table_name ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
- 修改現有表字符集：
ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
注意：CONVERT TO CHARACTER SET 會嘗試將表中的數據轉換為新的字符集。如果數據本身包含無法轉換的字符，可能會導致數據丟失或亂碼。建議在執行此操作之前備份數據。
- 驗證設置：
SHOW CREATE TABLE your_table_name;
查看輸出結果，確認表的字符集和排序規則已更新。
字段級別字符集設置
字段級別的字符集設置會覆蓋表級別的設置，影響單個字段的字符集。
- 創建字段時指定字符集：
CREATE TABLE your_table_name ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci );
- 修改現有字段字符集：
ALTER TABLE your_table_name MODIFY COLUMN name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
- 驗證設置：
SHOW CREATE TABLE your_table_name;
查看輸出結果，確認字段的字符集和排序規則已更新。
客戶端連接字符集
除了服務器、數據庫、表、字段四個層級的字符集設置，客戶端連接字符集也很重要。客戶端連接字符集決定了客戶端發送給服務器的數據的編碼方式，以及服務器返回給客戶端的數據的編碼方式。
- 設置客戶端連接字符集：
在連接MySQL服務器后，執行以下命令：
SET NAMES utf8mb4;
這條命令相當于同時執行以下三條命令：
SET character_set_client = utf8mb4; SET character_set_connection = utf8mb4; SET character_set_results = utf8mb4;
character_set_client 定義了客戶端發送給服務器的數據的編碼方式。character_set_connection 定義了服務器在接收到客戶端的數據后，將其轉換為哪種編碼方式。character_set_results 定義了服務器返回給客戶端的數據的編碼方式。
常見亂碼問題及解決方案
即使正確設置了各個層級的字符集，仍然可能遇到亂碼問題。以下是一些常見的亂碼問題及解決方案：
-
問題： 插入數據時出現亂碼。
原因： 客戶端連接字符集不正確。
解決方案： 在連接MySQL服務器后，執行 SET NAMES utf8mb4;。
-
問題： 從數據庫中讀取數據時出現亂碼。
原因： 客戶端接收數據的字符集不正確。
解決方案： 確保客戶端的字符集設置與數據庫的字符集一致。例如，在php中，可以使用 mysqli_set_charset($conn, “utf8mb4”); 設置客戶端字符集。
-
問題： 表中的數據已經亂碼。
原因： 在錯誤的字符集下插入了數據。
解決方案： 這是一個比較棘手的問題，需要根據具體情況進行處理。可以嘗試以下方法：
- 如果知道原始數據的正確編碼方式，可以先將數據轉換為正確的編碼方式，然后再插入到數據庫中。
- 如果無法確定原始數據的正確編碼方式，可以嘗試使用一些字符集轉換工具，例如 iconv，將數據轉換為UTF8編碼。
UTF8和UTF8MB4的區別？我應該用哪個？
UTF8和UTF8MB4都是UTF-8編碼的變種，但它們之間存在一個關鍵區別：UTF8最多只能存儲3個字節的Unicode字符，而UTF8MB4可以存儲4個字節的Unicode字符。
這意味著UTF8無法存儲一些特殊的字符，例如Emoji表情符號。如果你的應用需要存儲Emoji表情符號或其他4字節的Unicode字符，必須使用UTF8MB4。
在MySQL 5.5.3之后，UTF8實際上是UTF8MB3的別名，只能存儲3字節的Unicode字符。因此，為了避免潛在的問題，建議始終使用UTF8MB4作為UTF8的替代品。
字符集設置的優先級是怎樣的？
MySQL字符集設置的優先級從高到低依次為：
- 字段級別
- 表級別
- 數據庫級別
- 服務器級別
- 客戶端連接字符集
也就是說，如果字段級別設置了字符集，則該字段的字符集將覆蓋表級別、數據庫級別、服務器級別和客戶端連接字符集的設置。如果字段級別沒有設置字符集，則使用表級別的設置，以此類推。
需要注意的是，客戶端連接字符集的作用是告訴服務器客戶端使用什么編碼發送數據，以及客戶端希望服務器返回什么編碼的數據。它并不直接影響數據庫中存儲的數據的編碼方式。
如何避免字符集帶來的麻煩？
最好的方法是在項目初期就確定好字符集，并堅持使用。推薦使用UTF8MB4作為默認字符集，并確保服務器、數據庫、表、字段以及客戶端連接字符集都設置為UTF8MB4。
此外，在處理用戶輸入時，應該始終對數據進行驗證和轉義，以防止惡意代碼注入和亂碼問題。
字符集問題看似簡單，但如果不加以重視，可能會導致各種各樣的麻煩。希望本文能夠幫助你更好地理解MySQL字符集設置和轉換，避免亂碼問題。
