投稿
mysqlで漢字コードをutf8にする
●mysqlのバージョンを確認する
mysql -u root -p
mysql> select version();
+-------------------------+
| version() |
+-------------------------+
| 5.7.18-0ubuntu0.16.04.1 |
+-------------------------+
1 row in set (0.00 sec)
●my.cnfの編集
sudo vi /etc/mysql/my.cnf
[mysqld]
character-set-server=utf8
[mysql]
#default-character-set=latin1
#
# utf8にする
default-character-set=utf8
sudo /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.
mysql>
mysql> status
--------------
mysql Ver 14.14 Distrib 5.7.18, for Linux (i686) using EditLine wrapper
Connection id: 4
Current database:
Current user: test_user@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18-0ubuntu0.16.04.1 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 1 min 11 sec
Threads: 1 Questions: 6 Slow queries: 0 Opens: 107 Flush tables: 1 Open tables: 26 Queries per second avg: 0.084
--------------
mysql> 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/ |
+--------------------------+----------------------------+
ケース１（サーバー側が latin１）
my.cnfの内容と漢字コードの関係を確認してみました
my.cnfの内容
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
character-set-server=latin1
[mysql]
default-character-set=utf8
漢字コード確認(mysql)
mysql> status
--------------
mysql Ver 14.14 Distrib 5.7.18, for Linux (i686) using EditLine wrapper
Connection id: 5
Current database:
Current user: test_user@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18-0ubuntu0.16.04.1 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 25 sec
Threads: 1 Questions: 6 Slow queries: 0 Opens: 107 Flush tables: 1 Open tables: 26 Queries per second avg: 0.240
--------------
mysql> 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/ |
+--------------------------+----------------------------+
ケース２(おそらくデフォルト、クライアントがlatin1になっている)
my.cnfの内容
[mysqld]
character-set-server=utf8
[mysql]
default-character-set=latin1
漢字コード確認(mysql)
mysql> status
--------------
mysql Ver 14.14 Distrib 5.7.18, for Linux (i686) using EditLine wrapper
Connection id: 4
Current database:
Current user: test_user@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18-0ubuntu0.16.04.1 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 52 sec
Threads: 1 Questions: 6 Slow queries: 0 Opens: 107 Flush tables: 1 Open tables: 26 Queries per second avg: 0.115
--------------
mysql> show variables like 'char%';
+--------------------------+----------------------------+
| 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/share/mysql/charsets/ |
+--------------------------+----------------------------+
ケース３（これでOK）
my.cnf
[mysqld]
character-set-server=utf8
[mysql]
default-character-set=utf8
漢字コード確認(mysql)
mysql> status
--------------
mysql Ver 14.14 Distrib 5.7.18, for Linux (i686) using EditLine wrapper
Connection id: 4
Current database:
Current user: test_user@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18-0ubuntu0.16.04.1 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 1 min 11 sec
Threads: 1 Questions: 6 Slow queries: 0 Opens: 107 Flush tables: 1 Open tables: 26 Queries per second avg: 0.084
--------------
mysql> 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/ |
+--------------------------+----------------------------+
0 件のコメント:
コメントを投稿