◆ 私の環境は以下のとおりです
- Amazon Linux AMI 2017.03.0 (HVM), SSD Volume Type (64bit)
- MySQL 5.7 - mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper
◆ デフォルトでは言語設定がlatin1になっています
| mysql> show variables like 'chara%'; |
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/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
[Variable_name]
character_set_database : デフォルトのデータベースにセットされる文字セット
character_set_server : サーバのデフォルトの文字セット
Variable name
なので日本語をinsertしようとすると失敗します
mysql> insert into testdb.testtbl values ('てすと');
ERROR 1366 (HY000): Incorrect string value: '\xE3\x81\xA6\xE3\x81\x99...' for column 'test' at row 1
mysql>
☞ リファレンス
◆ 手順です
1. my.cnf を修正します
| sudo vi /etc/my.cnf |
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character-set-server=utf8
⇒ character-set-serverを追記します
2. MySQLを再起動します
| sudo service mysqld restart |
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
| mysql> show variables like 'chara%'; |
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/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
⇒ 文字コードがutf8に変更されています
3. データベースを再作成します
| mysql> drop database testdb; |
mysql> drop database testdb;
Query OK, 1 row affected (0.01 sec)
mysql>
| mysql> create database testdb; |
mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)
mysql>
| mysql> create table testdb.testtbl (test varchar(20)); |
mysql> create table testdb.testtbl (test varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql>
こんどは無事に日本語もinsertできます
mysql> insert into testdb.testtbl values ('てすと');
Query OK, 1 row affected (0.00 sec)
mysql> select * from testdb.testtbl;
+-----------+
| test |
+-----------+
| てすと |
+-----------+
1 row in set (0.00 sec)
mysql>
ここまではググればいっぱいでてきました。私がつまづいたのはDBの再作成で
| mysql> create database test2; |
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
mysql>
もとのテーブルをコピーしようとすると
| mysql> create table test2db.testtbl2 select * from testdb.testtbl; |
mysql> create table test2db.testtbl2 select * from testdb.testtbl;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
日本語はinsertできないみたいです(´・ω・｀)。ちゃんと1から作らなくちゃです
mysql> insert into test2db.testtbl2 values ('てすと');
ERROR 1366 (HY000): Incorrect string value: '\xE3\x81\xA6\xE3\x81\x99...' for column 'test' at row 1
mysql>
参考にしたリンクです
MySQLに日本語でINSERTできない場合
