データベース - MySQL - 文字コード設定

 
1. 概要
2. MySQL 5.6 時の初期値と設定
3. MySQL 5.7 時の初期値と設定 中間編
4. 解決編

1. 概要

 本項の内容は、サーバの設定に関するものです。  FreeBSD に MySQL をインストールして使用しているのですが・・・。  どうも、MySQL 5.6 のときの、デフォルトの文字コードと MySQL 5.7 のデフォルトの文字コードが違うようなのです。  目的は、すべての文字コードを UTF-8 で扱うことです。

2. MySQL 5.6 時の初期値と設定

 MySQL 5.6(実はここうろ覚え)のときは、文字コードが

mysql> show variables like "chara%";
+--------------------------+----------------------------------+
| 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/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.46 sec)

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.6.26, for FreeBSD10.2 (i386) using  EditLine wrapper

Connection id:          3
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          more
Using outfile:          ''
Using delimiter:        ;
Server version:         5.6.26 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql.sock
Uptime:                 52 min 13 sec

Threads: 1  Questions: 12  Slow queries: 0  Opens: 67  Flush tables: 1  Open tables: 60  Queries per second avg: 0.003
--------------
 てなことになっておって、FreeBSD では(Windows や Linux では場所が違うのでご注意)

/usr/local/etc/mysql/my.cnf
 に

[mysqld]

・・・	略	・・・

character-set-server            = utf8
 の記述を加えて

service mysql-server restart
 してやれば

mysql> show variables like "chara%";
+--------------------------+----------------------------------+
| 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/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.46 sec)

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.6.26, for FreeBSD10.2 (i386) using  EditLine wrapper

Connection id:          3
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          more
Using outfile:          ''
Using delimiter:        ;
Server version:         5.6.26 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql.sock
Uptime:                 52 min 13 sec

Threads: 1  Questions: 12  Slow queries: 0  Opens: 67  Flush tables: 1  Open tables: 60  Queries per second avg: 0.003
--------------
 となって、シアワセになれたのです。

3. MySQL 5.7 時の初期値と設定 中間編

 MySQL 5.7 では(インストールしている正確なバージョンは「mysql57-server-5.7.26_1」)、デフォルトが

mysql> show variables like "chara%";
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | latin1                           |
| character_set_connection | latin1                           |
| character_set_database   | latin1                           |
| character_set_filesystem | binary                           |
| character_set_results    | latin1                           |
| character_set_server     | latin1                           |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.26, for FreeBSD12.0 (amd64) using  EditLine wrapper

Connection id:          2
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          less
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.26-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /tmp/mysql.sock
Uptime:                 10 min 37 sec

Threads: 1  Questions: 6  Slow queries: 0  Opens: 109  Flush tables: 1  Open tables: 102  Queries per second avg: 0.009
--------------

mysql>
 てなことになっておって、MySQL 5.6 と同じ処置を施してやると

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

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.26, for FreeBSD12.0 (amd64) using  EditLine wrapper

Connection id:          2
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          less
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.26-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /tmp/mysql.sock
Uptime:                 15 sec

Threads: 1  Questions: 6  Slow queries: 0  Opens: 109  Flush tables: 1  Open tables: 102  Queries per second avg: 0.400
--------------

mysql>
 となって、ありゃ、Client が設定されないぞ・・・と。  よう知らんうちに、何か変わったのであるな・・・。

4. 解決編

 ということで「mysql で文字コードを utf8 にセットする - Qiita」を参考にさせていただいたのです。

/usr/local/etc/mysql/my.cnf
 に

[mysqld]

・・・	略	・・・

character-set-server            = utf8
 の他に

[client]

・・・	略	・・・

default-character-set           = utf8
 ちゅうのも必要なそうで。  変更して、再起動すると

mysql> show variables like "chara%";
+--------------------------+----------------------------------+
| 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/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.26, for FreeBSD12.0 (amd64) using  EditLine wrapper

Connection id:          2
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          less
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.26-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql.sock
Uptime:                 13 sec

Threads: 1  Questions: 6  Slow queries: 0  Opens: 109  Flush tables: 1  Open tables: 102  Queries per second avg: 0.461
--------------

mysql>
 やっと、シアワセになれました。