2016年3月17日木曜日

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で弾いてくれるそうです。



ということで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ポチって

ください!!

2016年3月3日木曜日

第2回 MySQL・PostgreSQLユーザーグループ(MyNA・JPUG)合同DB勉強会 in 東京を開催してきた

先月の話ですが半年ぶりくらいに合同勉強会してきました。
資料のリンク等はポータルサイトにまとめたので下記のリンクから辿ってください。


第2回 MySQL・PostgreSQLユーザーグループ(MyNA・JPUG)合同DB勉強会 in 東京


■登壇資料など
中国地方DB勉強会のポータルサイトにまとめました。

中国地方DB勉強会


※今回は動画の配信があります

■メーリングリスト
次回の告知についてはMLやDoorKeeperを使います。
興味がある方はチェックしてみてください。

中国地方DB勉強会ML Google Group

DoorKeeper

■関連slack

MySQLCasualのSlackはこちら

PostgreSQLのSlackはこちら

■twitterのまとめ

・ぬこさんバージョン
http://togetter.com/li/940915


・yokuさんバージョン
http://togetter.com/li/940891


大変素晴らしいセッションばかりで本当に楽しい会でした。
今回も例に漏れず関東の洗礼を受け、ドタキャン率は高めでしたが来てない人は本当に勿体無い内容だったと思います。
次回があるかはわかりませんが次回の中国地方DB勉強会は4月に福山を予定してます。
皆様のご参加お待ちしております。