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

また、他にも CREATE TABLE 文の DEFAULT 値に日本語等が入っている時に Invalid default value for なんていうエラーが出ることがあった。

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

まずは 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

ここまでの設定では、既存のデータベース、テーブルのエンコーディング設定が変わっていないので、それらを変更するため以下のコマンドを叩いていく。

-- 既存 DB スキーマ内で、今後新たに作るテーブルのデフォルトエンコーディングを変更する
ALTER DATABASE 【DB 名】 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- もしくは以下
SET character_set_database=utf8mb4;

-- 既存テーブルのデフォルトエンコーディングを変更する (カラムの文字コードは変わらない)
ALTER TABLE 【テーブル名】 DEFAULT CHARACTER SET utf8mb4;

-- 既存テーブルの全カラムのエンコーディングを変更する
ALTER TABLE 【テーブル名】 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 既存テーブルの指定カラムのエンコーディングを変更する
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;

-- その他、サーバやクライアント設定なども全て utf8mb4 に変更する
SET character_set_database=utf8mb4;
SET character_set_server=utf8mb4;
SET character_set_client=utf8mb4;
SET character_set_connection=utf8mb4;

utf8mb4 というエンコード指定は、通常の utf8 にプラスして、絵文字など普段あまり使わない SMP と呼ばれる文字にも対応しているモノ。

utf8mb4_general_ci という指定は照合順序というモノの指定で、他に utf8mb4_unicode_ci といった指定もある。_ci は Case Insensitive で大文字・小文字を区別しない。_unicode の方は、日本語の濁点・半濁点を同値と捉えてしまうようなので、特に普段何も考えがなければ _general の方が良いだろう。

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