- 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>
やっと、シアワセになれました。
|
