MySQLでINSERT時に文字コードエラーが出た時の対処法

MySQLをセットアップして、INSERT文を実行したときに”Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) ”というエラーが発生した。

これは、MySQL側の文字コードがLatin1であるのに対して、入力されたデータがUTF8だった場合に発生するエラーだ。
それでは、このエラーが出たときの対処法をみていくことにしましょう。

INSERT時に文字コードエラーが出たらこの2つを確認しよう。

MySQLそのものの文字コード
テーブルの文字コード

1,MySQLの文字コードを設定する

まずは一つ目のケース。
MySQLの文字コードがUTF8になっていない場合の対処法です。

まずは、MySQLの文字コードを確認してみましょう。
MySQLの文字コードを確認するには、以下のSQL文を実行します。

mysql> show variables like '%character%';

このSQL文を実行すると、次のようなテーブルが表示される。

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

character_set_client : クライアントが発行するSQL文の文字コード
character_set_connection : クライアントからの文字をこの文字コードへ変換する
character_set_database : 現在参照しているデータベースの文字コード
character_set_results : クライアントへ送信するデータの文字コード
character_set_server : データベース作成時のデフォルトの文字コード
character_set_system : システムの使用する文字セット

※ちなみにfilesystemとsystemの文字コードはいじる必要はありません。

さて、上のテーブルでは、Databaseとserverがlatin1になっていることが確認できました。
まずはこれをUTF8mb4にする必要があります。

ところで、ここでUTF8mb4という聞き慣れない文字コードが出てきましたね。
深く考える必要はありませんが、これはUTF8の強化版だと思っておいてください。

通常のUTF8が4バイト文字を扱えないため、後にUTF8mb4が使われるようになりました。
もしも通常のUTF8に設定されているデータベースに4バイト文字コードのデータを渡すと、エラーが発生したり、文字化けして”?”になったりします。

つまり、通常のUTF8を使用するとデメリットはあってもメリットは無いということです。
ですので、UTF8mb4を利用するほうが無難です。

MySQLの設定ファイルを編集する

MySQLの文字コードを変更するには、設定ファイルを編集してMySQLを再起動します。
設定ファイルは、大抵の場合”/etc/my.cnf”にあります。
このファイルをVIなどで開いて編集します。

# sudo vi /etc/my.cnf

そして、[mysqld]セクションと、[client]セクションに次のように追記します。
[client]セクションはデフォルトだと無い場合があるので、その場合はセクションも追加します。

[mysqld]
character-set-server=utf8mb4
[client]
default-character-set=utf8mb4

これでmy.cnfを上書き保存します。
そして、次にMySQLを再起動します。

# sudo systemctl restart mysqld.service

MySQLのコンソールにログインして、文字コードが変更できているか確認します。

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

2,テーブルの文字コードを変更する

MySQLの文字コードをUTF8にしても同じエラーが発生する場合、すでに作成しているテーブルがlatin1になっている可能性があります。

まずはテーブルの文字コードを確認してみましょう。
そのためには、次のSQL文を実行します。
teblenameには対象のテーブル名を入れます。

mysql> show create table tablename;

すると、次のようなデータが表示されます。

| tablename | CREATE TABLE `tablename` (
  `product_id` varchar(100) NOT NULL,
  `maker` varchar(400) DEFAULT NULL,
  `introduce` text,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 
|

”DEFAULT CHARSET” がlatin1になっていますね。
これをUTF8mb4に変更します。

テーブルの文字コードを変更するには、次のSQL文を実行します。

mysql> alter table tablename convert to character set utf8mb4;

SQL文を実行したら、文字コードが変更できているか確認してみましょう。
無事変更できていればOKです。

mysql> show create table tablename;
| tablename | CREATE TABLE `tablename` (
  `product_id` varchar(100) NOT NULL,
  `maker` varchar(400) DEFAULT NULL,
  `introduce` text,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|