MYSQLで日本語insertしようとしてSQL エラー (1366): Incorrect string value:

概要

MySQLで日本語が入れられないので調べたらmysqlの設定をしてなかったのでこの機会に一般的な設定をまとめておく

作業環境

CentOS6.9
mysql5.6

実行内容

日本語の文字を入れようとしたらこういうエラーがでた
SQL エラー (1366): Incorrect string value:
調べるとinsert時の文字コードとデータベースの文字コードが違うらしい

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| 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)

mysql> show global variables like 'character%';
+--------------------------+----------------------------+
| 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/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

なるほどlatin1になってる
my.cnfの位置を調べて編集する

# find / -name 'my.cnf'
/etc/my.cnf
# vi /etc/my.cnf

[mysqld]の項目の下に下記を追加する

[mysqld]
#[mysqld]の文字の下に下記を追加する
character-set-server=utf8
# ついでにストレージエンジンをinnoDBにする場合は更に[mysqld]の項目の下に下記を追加する
default-storage-engine=InnoDB
innodb_file_per_table

#これはファイルの一番下に下記をまるごと追加する
[mysql]
default-character-set=utf8

文字コードの修正だけなら上記設定でいいので再起動する

# service mysqld restart

mysqlにログインしてもう一度確認

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| 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)

mysql> show global variables like 'character%';
+--------------------------+----------------------------+
| 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)

でもこのままだと既存のテーブルはデフォルトのキャラセットが下記のようにlatin1のままなのでdatabeseをdropして作り直したほうがいい

mysql> show create table etc_table;
| sites | CREATE TABLE `etc_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |

こんなこと二度としなくていいように初期設定はきちんとやろう…