MySQLに絵文字を保存しようとしたら文字列が消える問題

最近、すっかりPostgreSQLの匂いがしなくなったと噂の私です。
最近MySQLで新たな気付き（問題）があったのでメモとして残します。

まずMySQLは🍣🍻問題があることは知っていました。

MySQL と寿司ビール問題

ではutf8mb4以外で保存していた場合どうなるのでしょう？

・latin1の場合

絵文字は文字化けします。
そして非常に残念な事にALTER文で変更した場合はどうでしょう？
Slackで質問したらみんな大好き@yoku0825さんが試してくれました。

mysql57> create table t2 (val varchar(32) charset latin1);
Query OK, 0 rows affected (0.02 sec)

mysql57> INSERT INTO t2 SET val= x'F09F8DA3';
Query OK, 1 row affected (0.01 sec)

mysql57> SELECT HEX(val) FROM t2;
+----------+
| HEX(val) |
+----------+
| F09F8DA3 |
+----------+
1 row in set (0.00 sec)

mysql57> ALTER TABLE t2 CONVERT TO CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql57> SELECT HEX(val) FROM t2;
+------------------+
| HEX(val)         |
+------------------+
| C3B0C5B8C28DC2A3 |
+------------------+
1 row in set (0.00 sec)

…壊れてますね。
この場合は@tmtmsさんのアドバイスを引用すると

latin1 に utf8mb 文字を入れてしまった場合は、データを HEX ダンプして、テーブルを utf8mb4 で作り直して、ダンプデータをリストアって感じで、復旧したことはあります。

とのことです。
辛いやつですね。

ちなみに@yoku0825さんがこちらで詳しく検証してくれてます。

latin1からutf8mb4_binへの変換:yoku0825さんのGist

・unicode*_ciな場合

例えばutf8_general_ciの場合ですね。
この場合は

絵文字を含めた以降の文字が全部捨てられて保存されます

つまり何も残りません。
これが今回のタイトルの現象ですね。
僕はこれに遭遇しました。
MySQL5.6でSQL_MODE=""とかしてた場合Errorも吐きません。
すーっと切り捨ててINSERTされました。
絵文字を使わせないって方針の場合は

sql_mode= STRICT_TRANS_TABLES

とかしとくとErrorで弾いてくれるそうです。

@yoku0825 なるほど。SQLモードをもっと調べてみます！！ https://t.co/c7uyZBgYui

— そーだい＠初代ALF (@soudai1025) 2016年3月14日

ということで4バイト文字関連ですが

|                    | utf8mb4_bin | utf8mb4_general_ci | utf8mb4_unicode_ci | utf8mb4_unicode_520_ci|
|--------------------|-------------|--------------------|--------------------|-----------------------|
| Hiragana-Katakana  | cs (unkind) | cs (unkind)        | ci (good)          | ci(good)              |
| Youon              | cs (good)   | cs (good)          | ci (critical)      | ci(critical)          |
| Dakuten-Handakuten | cs (good)   | cs (good)          | ci (critical)      | ci(critical)          |
| Wide-Narrow        | cs (unkind) | cs (unkind)        | ci (good)          | ci(good)              |
| Sushi-Beer         | cs          | ci                 | ci                 | cs                    |

https://bugs.mysql.com/bug.php?id=79977

なので

• 文字コード　utf8mb4
• collation　 utf8mb4_bin　または　utf8mb4_general_ci

とするという結論です。
既存環境でいきなり絵文字が消える場合や化ける場合は上記の環境をチェックしてみてください。
下記の通りになってれば🍻も🍣も登録出来るはずです。

mysql> show variables like '%character\_set\_%';
+--------------------------+---------+
| 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    |
+--------------------------+---------+
7 rows in set (0.00 sec)

# おまけ

さっきのバグチケットですがMySQL5.8でutf8mb4_unicode_520_ciがデフォルトになりそうって噂です。
見ての通りutf8mb4_unicode_520_ciだとクリティカルな問題があるので是非

右上くらいに有るボタンのAffects meポチって

ください！！