MySQL と寿司ビール問題 - かみぽわーる

MySQL と Unicode Collation Algorithm (UCA) - かみぽわーるに関連するトピックで、 MySQL には寿司ビール問題というのがある。

寿司ビール問題どっかで詳しくお話を聞くべきだよなぁ。。。
— RKajiyama (@RKajiyama) March 18, 2015

これはどういう問題かというと、 MySQL の Unicode では binary collation にしてコードポイントで比較しないと🍣と🍺に限らず絵文字が同値判定されるという問題です。

あれ？ MySQL の utf8mb4 charset って、4バイト文字同士を比較すると同じ文字扱いされる？
SELECT '🍣'='🍺' → 1
MySQL的には寿司とビールは同じ扱い。
— とみたまさひろ (@tmtms) December 22, 2014

MySQLで
select concat(欲しい物,'おごってあげるよ') from 欲しい物リスト where 欲しい物='🍺'
が
'🍣おごってあげるよ'
になる。
「🍣脆弱性」と名付けよう。
— とみたまさひろ (@tmtms) December 22, 2014

なぜこんな挙動にしたのか、どういうケースのときにこの挙動だとうれしいのか全く理解が及ばないが、残念ながらこの挙動はドキュメントに明記されており、仕様である。

以下に引用しますが、ざっくりいうと

collating weight がある文字はそれを使う。ない場合は以下に従う。
BMP文字で general collation (xxx_general_ci) の場合、コードポイントを使う。
BMP文字で unicode collation (xxx_unicode_ci) の場合、なんかいい感じの計算式で導出する。
SMP文字(絵文字とか)の場合、0xfffd REPLACEMENT CHARACTER と同じ weight になる。

とのこと😨。

For all Unicode collations except the “binary” (xxx_bin) collations, MySQL performs a table lookup to find a character's collating weight. This weight can be displayed using the WEIGHT_STRING() function. (See Section 12.5, “String Functions”.) If a character is not in the table (for example, because it is a “new” character), collating weight determination becomes more complex:

For BMP characters in general collations (xxx_general_ci), weight = code point.

For BMP characters in UCA collations (for example, xxx_unicode_ci and language-specific collations), the following algorithm applies:
(snip)

For supplementary characters in general collations, the weight is the weight for 0xfffd REPLACEMENT CHARACTER. For supplementary characters in UCA 4.0.0 collations, their collating weight is 0xfffd. That is, to MySQL, all supplementary characters are equal to each other, and greater than almost all BMP characters.

http://dev.mysql.com/doc/refman/5.6/en/charset-unicode-sets.html

なので、絵文字を検索で区別する必要がある場合は utf8mb4_bin にするしかないんですが、'ハハ' = 'パパ' 問題を気にしなければもうひとつ方法があることをドキュメント読んでて知りました。

xxx_unicode_ci というのは UCA 4.0.0 というかなり古いと思われる仕様に対する実装で、より新しい UCA 5.2.0 を実装した xxx_unicode_520_ci だとSMP文字にも weight を持っており、 weight がなくてもいい感じの計算式で導出するので 0xfffd REPLACEMENT CHARACTER と同じ weight にはならないと書いてある。

For supplementary characters based on UCA versions later than 4.0.0 (for example, xxx_unicode_520_ci), supplementary characters do not necessarily all have the same collation weight. Some have explicit weights from the UCA allkeys.txt file. Others have weights calculated from this algorithm:
(snip)

http://dev.mysql.com/doc/refman/5.6/en/charset-unicode-sets.html

実際試してみるとこんな感じ。まず区別できない例。

SET NAMES utf8mb4;

DROP TABLE IF EXISTS wishlist_general;
CREATE TABLE wishlist_general (
  item varchar(1)
) COLLATE utf8mb4_general_ci;

DROP TABLE IF EXISTS wishlist_unicode;
CREATE TABLE wishlist_unicode (
  item varchar(1)
) COLLATE utf8mb4_unicode_ci;

INSERT INTO wishlist_general VALUES ('🍣'), ('🍺');
INSERT INTO wishlist_unicode VALUES ('🍣'), ('🍺');

SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_general WHERE item = '🍣';
+------+--------------------------+
| item | HEX(WEIGHT_STRING(item)) |
+------+--------------------------+
| 🍣     | FFFD                     |
| 🍺     | FFFD                     |
+------+--------------------------+

SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_unicode WHERE item = '🍣';
+------+--------------------------+
| item | HEX(WEIGHT_STRING(item)) |
+------+--------------------------+
| 🍣     | FFFD                     |
| 🍺     | FFFD                     |
+------+--------------------------+

区別できる例。

SET NAMES utf8mb4;

DROP TABLE IF EXISTS wishlist_unicode_520;
CREATE TABLE wishlist_unicode_520 (
  item varchar(1)
) COLLATE utf8mb4_unicode_520_ci;

DROP TABLE IF EXISTS wishlist_bin;
CREATE TABLE wishlist_bin (
  item varchar(1)
) COLLATE utf8mb4_bin;

INSERT INTO wishlist_unicode_520 VALUES ('🍣'), ('🍺');
INSERT INTO wishlist_bin VALUES ('🍣'), ('🍺');

SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_unicode_520 WHERE item = '🍣';
+------+--------------------------+
| item | HEX(WEIGHT_STRING(item)) |
+------+--------------------------+
| 🍣     | FBC3F363                 |
+------+--------------------------+

SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_unicode_520;
+------+--------------------------+
| item | HEX(WEIGHT_STRING(item)) |
+------+--------------------------+
| 🍣     | FBC3F363                 |
| 🍺     | FBC3F37A                 |
+------+--------------------------+

SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_bin WHERE item = '🍣';
+------+--------------------------+
| item | HEX(WEIGHT_STRING(item)) |
+------+--------------------------+
| 🍣     | 01F363                   |
+------+--------------------------+

SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_bin;
+------+--------------------------+
| item | HEX(WEIGHT_STRING(item)) |
+------+--------------------------+
| 🍣     | 01F363                   |
| 🍺     | 01F37A                   |
+------+--------------------------+

とりあえず現状これが仕様だというのは分かったけど、どう考えてもSMP文字を 0xfffd REPLACEMENT CHARACTER と同じ weight にするの現世においてデメリットしかないと思うんで、これがちゃんと区別されるようになるのを願ってやまないです。