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 の気持ちはよくわかりません。
まあ、これも 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 になっていることがわかります。