MySQL：字元集問題

事件起源與分析

收到客戶發來的請求封包，入庫後發現有些字元不能正常顯示。檢視日志發現insert操作中，字段内容沒有問題。于是開始排查：

首先我們先來檢視目前資料庫的字元集

show variables like ‘character_set_%’           

結果如下

Variable_name         Value
character_set_client utf8mb4
character_set_connection utf8mb4
character_set_results utf8mb4
character_set_server utf8mb4
character_set_database utf8mb4
character_set_system utf8
character_sets_dir C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\
character_set_filesystem binary           

各個字段具體意義如下：

• character_set_client

  主要用來設定用戶端使用的字元集。通俗的講就是mysql把用戶端傳遞過來的資料都當成是utf8mb4

• character_set_connection

  主要用來設定連接配接資料庫時的字元集，如果程式中沒有指明連接配接資料庫使用的字元集類型則按照這個字元集設定。

• character_set_database

  主要用來設定預設建立資料庫的編碼格式，如果在建立資料庫時沒有設定編碼格式，就按照這個格式設定。

• character_set_filesystem

  檔案系統的編碼格式，把作業系統上的檔案名轉化成此字元集，即把 character_set_client轉換character_set_filesystem， 預設binary是不做任何轉換的。

• character_set_results

  資料庫給用戶端傳回時使用的編碼格式，如果沒有指明，使用伺服器預設的編碼格式。通俗的講就是mysql發送個用戶端的資料是utf8mb4的

• character_set_server

  伺服器安裝時指定的預設編碼格式，這個變量建議由系統自己管理，不要人為定義。

• character_set_system

  資料庫系統使用的編碼格式，這個值一直是utf8，不需要設定，它是為存儲系統中繼資料的編碼格式。

• character_sets_dir

  這個變量是字元集安裝的目錄。

通過以上資訊進行分析，我們可以推斷

• 插入語句沒有問題
• 背景字元集為utf8mb4，支援生僻字

剩下隻能是程式連接配接資料庫時是否設定了什麼，進一步排查代碼發現MySQL建立連接配接之後執行了如下語句，原因找到了。

mysql_set_character_set(mysql, "utf8");           

解決方法

mysql在C/C++中調用api設定連接配接mysql的編碼方式有以下幾種方法：

1. mysqli_set_charset

ret = mysql_set_character_set(mysql, "utf8mb4");
ret = mysql_set_character_set(mysql, "utf8mb4");           

說明：

推薦使用的設定方法，與mysql的連接配接斷開自動重連後仍能保持設定的編碼格式，并且影響mysql_real_escape_string函數功能，使mysql_real_escape_string函數使用設定的編碼格式轉義字元串。

2. 執行sql語句：SET NAMES

ret = mysql_real_query(mysql, "SET NAMES UTF8MB4;",
(unsigned long) strlen ("SET NAMES UTF8MB4;"));
ret = mysql_real_query(mysql, "SET NAMES UTF8MB4;",
(unsigned long) strlen ("SET NAMES UTF8MB4;"));           

說明：

使用sql語句執行，隻能影響目前與資料庫的連接配接，斷開自動重連後編碼格式會重置為預設的配置。

3. 設定MYSQL_SET_CHARSET_NAME屬性

ret = mysql_options(mysql, MYSQL_SET_CHARSET_NAME, "utf8mb4");
ret = mysql_options(mysql, MYSQL_SET_CHARSET_NAME, "utf8mb4");           

說明：

跟mysql_set_character_set類似，斷開自動重連後仍能保持設定的編碼格式，隻是不會影響到mysql_real_escape_string函數。 需要特别說明的是隻有在調用mysql_real_connect連接配接資料庫之前修改該屬性才會生效。