2018-09-12

MySQL の「Incorrect string value」エラーを直す (サロゲートペアに対応した UTF8MB4 エンコーディングに変更する)

CentOS Linux 上にインストールした MySQL で検証。

MySQL を使っていて、絵文字を含むテキストを INSERT・UPDATE しようとしたら、以下のようなエラーが出た。

Incorrect string value: '\xF0\xA6\x9A\xB0\xE7\x94...' for column 'my_column' at row 1

調べてみると、どうも MySQL の utf8 エンコーディングはサロゲートペア文字に対応していないようで、絵文字などを扱うには、サロゲートペア文字に対応したエンコーディング utf8mb4 に変えないといけないみたい。

参考：MySQL Incorrect string value: の解決策 | テクニカルノート

設定手順は以下のページどおり。

参考：MySQLの文字コードをutf8mb4に変更 - Qiita

まずは DB の文字コードの確認。

$ mysql -u root -p
# パスワードを入力し MySQL コンソールに移る。

mysql> show variables like "chara%";`

次に MySQL とクライアントのデフォルトの文字コードを設定する。設定ファイルの在り処は以下のコマンドで確認できる。

$ mysql --help | grep my.cnf

/etc/my.cnf という設定ファイルが存在していて、最初に読み込まれているようだったので、コレを開いて以下のように設定した。

# 両項目とも、最初は「utf8」となっていたところを「utf8mb4」にした

[mysqld]
character-set-server=utf8mb4

[client]
default-character-set=utf8mb4

で、MySQL を再起動する。自分の環境だと、以下のように mysqld をフルパスで指定して restart した。

$ /usr/sbin/mysqld restart

ここまでの設定では、既存のデータベース、テーブルのエンコーディング設定が変わっていないので、

今後新たに作るデータベースのデフォルトエンコーディングを指定し直す
今後新たに作るテーブルのデフォルトエンコーディングを指定し直す
今あるテーブルのエンコーディングを直す

ために、以下のコマンドを叩いていく。

-- 今後新たに作るデータベースのデフォルトエンコーディングを指定し直す
ALTER DATABASE 【DB 名】 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 今後新たに作るテーブルのデフォルトエンコーディングを指定し直す
ALTER TABLE 【テーブル名】 default character set utf8mb4;

-- 今あるテーブルのエンコーディングを直す
ALTER TABLE 【テーブル名】 MODIFY 【カラム名】 【型】 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 例 : ALTER TABLE my_table MODIFY my_column varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

以上。コレで既存の my_table.my_column にはサロゲートペア文字を含む文字列が INSERT・UPDATE できるようになったし、今後作成する DB やテーブルのデフォルトエンコーディングも utf8mb4 に変更できた。