[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;

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;