MariaDB で日本語を扱う場合の文字コードの設定

MariaDB で日本語を扱う場合の文字コードの設定。

設定変更前

まずは MariaDB をインストールした状態の文字コードを確認。

MariaDB [(none)]> show variables like 'char%';
+--------------------------+----------------------------+
| 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)
 
MariaDB [(none)]> status;
--------------
mysql  Ver 15.1 Distrib 10.0.11-MariaDB, for Linux (x86_64) using readline 5.1
 
Connection id:          3
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.0.11-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:                 2 min 18 sec
 
Threads: 1  Questions: 6  Slow queries: 0  Opens: 0  Flush tables: 1  Open tables: 63  Queries per second avg: 0.043
--------------

データベースとサーバーの項目が latin1 になっているのでここを utf8 にする。

設定変更

/etc/my.cnf の一番下に /etc/my.cnf.d/ 以下のファイルを読み込む設定が書かれている。

$ cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
 
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

/etc/my.cnf.d/server.cnf を編集して [mysqld] に character-set-server = utf8 を追加。

$ sudo vim /etc/my.cnf.d/server.cnf
// ...
[mysqld]
character-set-server = utf8
#collation-server     = utf8_general_ci
#skip-character-set-client-handshake
// ...

collation-server = utf8_general_ci
はデフォルトになっているので省略可。

MariaDB [(none)]> show collation like 'utf8%';
+------------------------------+---------+-----+---------+----------+---------+
| Collation                    | Charset | Id  | Default | Compiled | Sortlen |
+------------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci              | utf8    |  33 | Yes     | Yes      |       1 |

クライアントとサーバーで文字コードを合わせているので skip-character-set-client-handshake も不要。
というか使わない方が良さそう?→skip-character-set-client-handshake について

MariaDB を再起動して設定を反映。

$ sudo /etc/init.d/mysql restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

設定変更後

確認。

MariaDB [(none)]> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
 
MariaDB [(none)]> status;
--------------
mysql  Ver 15.1 Distrib 10.0.10-MariaDB, for Linux (x86_64) using readline 5.1
 
Connection id:          5
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.0.10-MariaDB MariaDB Server
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 34 min 15 sec
 
Threads: 1  Questions: 10  Slow queries: 0  Opens: 0  Flush tables: 1  Open tables: 63  Queries per second avg: 0.004
--------------

【環境】
MariaDB 10.0.10
CentOS 6.5

$ mysql -V
mysql  Ver 15.1 Distrib 10.0.10-MariaDB, for Linux (x86_64) using readline 5.1
$ cat /etc/redhat-release
CentOS release 6.5 (Final)

【参考】
CentOS 6.4にMariaDB 10をインストールする方法
MariaDBをCentOS 6にyumでインストールする方法
MARIADB 10.0.1 で日本語が文字化けしないようにする


カテゴリー: MariaDB タグ: , , パーマリンク