mysql で insert時に 「SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x98\x8D\x0A\x0D...' 」

mysql のログで以下エラーログが表示され、少々ハマったのでメモしておきます。

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x98\x8D\x0A\x0D...' for column 'comment'

調べてみるとエラーの元になっているのは絵文字を使用しているからということで、mysql の文字コードが「utf8mb4」でないと insert できないようでした。ただ確認してみると utf8mb4 になっているようなのですが、処理に失敗していました。最終的な原因は mysql に接続するフロントエンド側の文字コードの指定が「utf8」となっていたため、これを「utf8mb4」としたら解決しました。

'mysql:host=myhost;dbname=mydatabase;charset=utf8', user, password

上記エラーが発生した場合、確認事項がいくつかあるので記載します。

１．mysql設定ファイル（my.cnf等）での文字コードは正しいか

設定されている文字コードを見てみる。

$ show variables like '%character%';
+--------------------------+----------------------------+
| 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     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.001 sec)

utf8mb4 になっていない場合（上記ではlatin1）となっているため、mysql の設定ファイル（my.cnf等）を確認。文字コードの設定が無い場合は追記し、mysqlを再起動して確認する。

[mysqld]
～省略
#以下追加
character-set-server=utf8mb4
[client]
default-character-set=utf8mb4

起動後、再度確認してみる。

$ 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     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.001 sec)

正しく設定できた。

２．テーブルのフィールドの照合順序が utf8mb4 になっているか

テーブルの情報を確認してみる。

$ show create table reviews;
+-------+-----------------------------------------+
| Table | Create Table
+-------+-----------------------------------------+
| reviews | CREATE TABLE `reviews` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `comment` text CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4
+-------+-----------------------------------------+
1 row in set (0.001 sec)

latin1 になっているので、utf8mb4に変更する。

ALTER TABLE reviews CONVERT TO CHARACTER SET utf8mb4;

再度確認してみる。

$ show create table reviews;
+-------+-----------------------------------------+
| Table | Create Table
+-------+-----------------------------------------+
| reviews | CREATE TABLE `reviews` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `comment` text CHARACTER SET utf8mb4 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4
+-------+-----------------------------------------+
1 row in set (0.001 sec)

正しく設定できた。

3.フロントエンド側からの接続設定は正しいか

mysqlに接続するフロントエンドからの接続の際の接続文字列が正しく表示されているか確認する。

'mysql:host=myhost;dbname=mydatabase;charset=utf8', user, password

utf8になっているので、utf8mb4に修正する。

MySQLの文字コードは、今後は「utf8mb4」の設定にしていくべきですね。この文字コードの設定箇所が多いので、DB設計時に全てutf8mb4にしておいた方がよさそうです。