MySQL を UTF-8 で使おうと思ってハマりがちなのは charset utf8 を指定してしまうことです。
MySQL の UTF-8 には歴史的事情により utf8 と utf8mb4 の二つあります。
UTF-8 は1バイト〜4バイトで1文字が構成される文字コードですが、MySQL の utf8 は4バイト文字を扱うことができません。ハマりたくなければ utf8mb4 を使いましょう。
utf8 を使ってしまった場合に4バイト文字がどのように扱われるか、自分でもうろ覚えだったのでメモしておきます。
登録
接続が utf8mb4 でカラムが utf8mb4
あたりまえですが、そのまま登録されます。
mysql> insert into utf8mb4 (c) values ('美味しい🍣と🍺');
mysql> select * from utf8mb4;
+-------------------------+
| c |
+-------------------------+
| 美味しい🍣と🍺 |
+-------------------------+
接続が utf8 でカラムが utf8mb4
4バイト文字が「????」になります。
mysql> insert into utf8mb4 (c) values ('美味しい🍣と🍺');
Warning (Code 1300): Invalid utf8 character string: 'F09F8D'
Warning (Code 1366): Incorrect string value: '\xF0\x9F\x8D\xA3\xE3\x81...' for column 'c' at row 1
mysql> select * from utf8mb4;
+-------------------------+
| c |
+-------------------------+
| 美味しい????と???? |
+-------------------------+
utf8 の接続から送られてくるデータでは4バイト文字は不正な4バイトデータなので、4つの「?」に置き換えられます。
なお、sql_mode の設定によってはエラーになります。MySQL 5.7 のデフォルトではエラーになります。安全ですね。
mysql> insert into utf8mb4 (c) values ('美味しい🍣と🍺');
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x8D\xA3\xE3\x81...' for column 'c' at row 1
接続が utf8mb4 でカラムが utf8
4バイト文字が「?」になります。
mysql> insert into utf8 (c) values ('美味しい🍣と🍺');
Warning (Code 1366): Incorrect string value: '\xF0\x9F\x8D\xA3\xE3\x81...' for column 'c' at row 1
mysql> select * from utf8;
+-------------------+
| c |
+-------------------+
| 美味しい?と? |
+-------------------+
utf8mb4 の接続上は4バイト文字は正しい1文字ですが、utf8 に対応する文字がないため、1つの「?」に置き換えられます。
sql_mode の設定によってエラーになるのは同上です。
接続が utf8 でカラムが utf8
4バイト文字が現れるとそこで文字列が切られてしまいます！
mysql> insert into utf8 (c) values ('美味しい🍣と🍺');
Warning (Code 1300): Invalid utf8 character string: 'F09F8D'
Warning (Code 1366): Incorrect string value: '\xF0\x9F\x8D\xA3\xE3\x81...' for column 'c' at row 1
mysql> select * from utf8;
+--------------+
| c |
+--------------+
| 美味しい |
+--------------+
utf8 の接続で4バイト文字は4バイトの不正データなので「????」になっても良さそうなのですが、MySQL の気持ちはよくわかりません。 接続もカラムも utf8 なので文字コードの変換が行われず、そのまま登録しようとしたらおかしな文字があったからそこで打ち切り…ということなのかもしれません。
まあ、これも sql_mode をちゃんと設定しておけばいいのですけど…。
参照
utf8mb4 接続で参照
あたりまえですが、そのまま参照できます。
mysql> select * from utf8mb4; +-------------------------+ | c | +-------------------------+ | 美味しい🍣と🍺 | +-------------------------+
utf8 接続で参照
4バイト文字は「?」に置換されます。
mysql> select * from utf8mb4; +-------------------+ | c | +-------------------+ | 美味しい?と? | +-------------------+
文字「?」そのものが入っているのか、参照時に置換されたのかは HEX() 関数で確認できます。
mysql> select right(c,1),hex(right(c,1)) from utf8mb4; +------------+-----------------+ | right(c,1) | hex(right(c,1)) | +------------+-----------------+ | ? | F09F8DBA | +------------+-----------------+
右端1文字の文字コードを16進で出力すると「?」の文字コード 3F ではなく「🍺」の F09F8DBA になっていることがわかります。
