[CharSet] CentOS 修改 MariaDB／MySQL 預設字符集

前言

MySQL 是瑞典人開發的，所以預設字符集是 latin1，與其每次要創建資料庫都要指定字符集，不如直接更改預設的字符集。

環境

• CentOS 8.2
• MariaDB 10.3.17

確認預設字符集

登入 MariaDB

[user@localhost ~]$ mysql -u user -p

查看預設字符集，Server characterset、Db characterset、Client characterset 與 Conn. characterset

MariaDB [(none)]> \s
--------------
mysql  Ver 15.1 Distrib 10.3.17-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          8
Current database:
Current user:           user@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.3.17-MariaDB MariaDB Server
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 8 sec

更改 cnf 設定

為了不影響到原先就在的 cnf 檔，在 /etc/my.cnf.d/ 目錄下新增一個 cnf 檔 (檔名任意)，內容如下

[client]
default-character-set=utf8mb4        ## 修改 Client characterset 與 Conn. characterset
[mysqld]
character-set-server=utf8mb4         ## 修改 Server characterset 與 Db characterset
collation-server=utf8mb4_unicode_ci  ## 會修改預設的校驗比對方法

載入設定

重啟 MariaDB，確認字符集已更改為所設定的

[user@localhost ~]$ sudo systemctl restart mariadb
[user@localhost ~]$ mysql -u user -p
...
...
MariaDB [(none)]> \s
--------------
mysql  Ver 15.1 Distrib 10.3.17-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          9
Current database:
Current user:           user@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.3.17-MariaDB MariaDB Server
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 3 min 39 sec