[Mariadb] 如何解決資料庫中文亂碼(問號)的編碼問題

魏子靖


在使用資料庫的時候，常常會遇到中文亂碼的問題，自己花了點時間找了一些相關資料測試，結果發現大家提供的方法其實算是大同小異，因此，這邊整理了我比較常用的解決方式供大家參考，或許下次有遇到類似的問題時就可以很快的解決。(本文適用於MySQL、Mariadb等資料庫)


如果資料庫有中文亂碼的時候，通常都是顯示問號居多，而這種問題通常可以透過資料庫的字元編碼來解決，因此，首先可以先檢查一下自己的資料庫編碼是否為UTF8，確認一下亂碼的問題是否是預設的編碼所造成
查看資料庫編碼

show variables like "%character_set%";

由以下查詢的結果可以看出資料庫編碼有的項目是utf8，有的卻是latin1，這就是此範例造成中文亂碼(問號)的原因所在

MariaDB [(none)]> show variables like "%character_set%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

修改資料庫編碼
再來通常只要解決編碼的問題，就可以搞定中文亂碼的問題了，設定的方法網路上有些不同的解法，而我自己的習慣是由「/etc/my.cnf.d/」目錄下的client.cnf、server.cnf來修改設定

1. 修改client.conf

sudo vi /etc/my.cnf.d/client.cnf

找到 [client] 的段落(若找不到，請自行加入)，並加入以下設定：

[client]
default-character-set=utf8

2. 修改server.conf

sudo vi /etc/my.cnf.d/server.cnf

找到 [mysqld] 的關鍵字後(若找不到，請自行加入)，加入以下指令：

[mysqld]
character-set-server=utf8

3. 重啟資料庫
將上面的設定處理好之後，直接重啟資料庫即可，之後再去查看資料庫編碼時，理論上應該就會由「latin1」變成「utf8」了

sudo systemctl restart mariadb

還有問題？重設現存的資料庫/表格編碼
當重設資料庫編碼後，還有一些地方要注意，例如：在修改成utf8編碼前，已經建立的表格編碼並不會跟著變動，所以如果做一些新增資料的測試，你會發現中文還是呈現問題，如下面的SQL指令與呈現的結果：

insert into demo_table(name, description) values ('test01', '中文');
+---------------------+-----------------------------------+
| name                | description                       |
+---------------------+-----------------------------------+
| NORMAL              | Hello MySQL!                      |
| test01              | ??                                |
+---------------------+-----------------------------------+

1. 查看表格編碼
查看表格編碼的方法有蠻多種的，以下我列出四種供大家參考：

指令一：
show full columns from table_name;
指令二：
show create table table_name;

註：將表格轉換編碼後，如果去查詢建立表格時的編碼，該編碼的值會變成後來我們新設定的編碼方式

+-------------+-------------------------------------+
| Table       | Create Table                                                                                                                                   |
+-------------+-------------------------------------+
| demo_table | CREATE TABLE `demo_table` (
  `name` varchar(50) DEFAULT NULL,
  `description` varchar(512) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+-------------------------------------+
指令三：
show table status from db_name;
指令四：
SELECT TABLE_SCHEMA , TABLE_NAME , TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'table_name';

第四種方法可以列出幾個我們比較需要的欄位資訊，可讀性比較好，TABLE_SCHEMA即是查詢在哪個資料庫

2. 轉換表格編碼
若上個步驟發現表格的編碼仍然不是utf8，那麼可以直接將表格轉成我們要的編碼：

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8;

當轉換編碼後再插入新的中文資料試試，即可驗證中文是否可正常的呈現

+---------------------+-----------------------------------+
| name                | description                       |
+---------------------+-----------------------------------+
| NORMAL              | Hello MySQL!                      |
| test01              | ??                                |
| test02              | 中文                               |
+---------------------+-----------------------------------+

3. 轉換資料庫編碼
承上，如果現有已建立的資料庫也有同樣的問題，那麼一樣可以利用指令來轉換成utf8

ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8_general_ci;

延伸閱讀：
．[教學] MariaDB/MySQL備份 – 如何匯出、匯入資料庫或表格
．How do I see what character set a MySQL database / table / column is?