MySQLでutf8_unicode_ciのLIKEエスケープ

utf8_unicode_ciを指定した時
「\」や「%」を部分一致検索する際に思った通りの結果が得られなくて苦労したのでメモ

最初に結果を書くと (参考にさせていただいた記事)
全角の「＼」「％」を使ったらとりあえず思い通りの動作になりました
※SQLを直に書いた場合です
※簡単な確認しかしていません

utf8_unicode_ciを指定すると挙動がおかしくなるものの
全角、半角を区別しないのでこの指定ができたという
腑に落ちないような納得したような...

最初は「\\\\」とか「\\\\\\」とか「\\%」とか「\\\\%」とか書いていたものの
「%%」という文字列はどうしても正しく検索できませんでした。

ここでは試していませんが、「_」「＿」も同じ動作なんでしょうかね

MySQLのリファレンス・マニュアルには

MySQL では文字列で C のエスケープ構文 (たとえば、改行文字を表すために 「\n」) が使用されているため、LIKE 文字列で使用される 「\」 はすべて二重に指定する必要があります。たとえば、「\n」 を検索するには、「\\n」 と指定します。「\」 を検索するには、「\\\\」 と指定します。これは、バックスラッシュはパーサーによって一度削除され、パターン一致が実行されるときにも再度削除される結果、一致対象のバックスラッシュは 1 つしか残らないためです。

例外: パターン文字列の末尾では、バックスラッシュを 「\\」 と指定できます。文字列の末尾では、エスケープの後ろに何もないため、バックスラッシュはそれ自体を表します。テーブルに次の値が含まれると仮定します。

とあります

utf8_general_ciの場合はこれで良かったのですが
utf8_unicode_ciの場合は先頭に「\」が来たらもう謎の挙動に

以下は（最後まで）適当なテーブルを作成して実験した結果です

準備

CREATE TABLE `Hoge` (
  `Id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `Name` varchar(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `Hoge` (`Name`) VALUES ('\\'),('\\\\'),('%'),('%%'),('\\%'),('AB\\CD'),('EF%GH'),('IJ\\\\%%\\\\KL');

mysql> select * from `Hoge`;
+----+------------+
| Id | Name       |
+----+------------+
|  1 | \          |
|  2 | \\         |
|  3 | %          |
|  4 | %%         |
|  5 | \%         |
|  6 | AB\CD      |
|  7 | EF%GH      |
|  8 | IJ\\%%\\KL |
+----+------------+
8 rows in set (0.00 sec)

「\」の検索

mysql> select * from `Hoge` where `Name` COLLATE utf8_unicode_ci LIKE '%\\\\%';
Empty set (0.00 sec)

mysql> select * from `Hoge` where `Name` COLLATE utf8_unicode_ci LIKE '%\\\\\\%';
+----+------------+
| Id | Name       |
+----+------------+
|  1 | \          |
|  2 | \\         |
|  5 | \%         |
|  6 | AB\CD      |
|  8 | IJ\\%%\\KL |
+----+------------+
5 rows in set (0.00 sec)

mysql> select * from `Hoge` where `Name` COLLATE utf8_unicode_ci LIKE '%＼%';
+----+------------+
| Id | Name       |
+----+------------+
|  1 | \          |
|  2 | \\         |
|  5 | \%         |
|  6 | AB\CD      |
|  8 | IJ\\%%\\KL |
+----+------------+
5 rows in set (0.00 sec)

mysql> select * from `Hoge` where `Name` COLLATE utf8_GENERAL_ci LIKE '%\\\\%';
+----+------------+
| Id | Name       |
+----+------------+
|  1 | \          |
|  2 | \\         |
|  5 | \%         |
|  6 | AB\CD      |
|  8 | IJ\\%%\\KL |
+----+------------+
5 rows in set (0.00 sec)

「\\」の検索

mysql> select * from `Hoge` where `Name` COLLATE utf8_unicode_ci LIKE '%\\\\\\\\%';
+----+------+
| Id | Name |
+----+------+
|  5 | \%   |
+----+------+
1 row in set (0.00 sec)

mysql> select * from `Hoge` where `Name` COLLATE utf8_unicode_ci LIKE '%\\\\\\\\\\%';
+----+------------+
| Id | Name       |
+----+------------+
|  2 | \\         |
|  8 | IJ\\%%\\KL |
+----+------------+
2 rows in set (0.00 sec)

mysql> select * from `Hoge` where `Name` COLLATE utf8_unicode_ci LIKE '%＼＼%';
+----+------------+
| Id | Name       |
+----+------------+
|  2 | \\         |
|  8 | IJ\\%%\\KL |
+----+------------+
2 rows in set (0.00 sec)

mysql> select * from `Hoge` where `Name` COLLATE utf8_GENERAL_ci LIKE '%\\\\\\\\%';
+----+------------+
| Id | Name       |
+----+------------+
|  2 | \\         |
|  8 | IJ\\%%\\KL |
+----+------------+
2 rows in set (0.00 sec)

「%」の検索

mysql> select * from `Hoge` where `Name` COLLATE utf8_unicode_ci LIKE '%\\%%';
+----+------------+
| Id | Name       |
+----+------------+
|  3 | %          |
|  4 | %%         |
|  5 | \%         |
|  7 | EF%GH      |
|  8 | IJ\\%%\\KL |
+----+------------+
5 rows in set (0.00 sec)

mysql> select * from `Hoge` where `Name` COLLATE utf8_unicode_ci LIKE '%\\\\%%';
Empty set (0.00 sec)

mysql> select * from `Hoge` where `Name` COLLATE utf8_unicode_ci LIKE '%％%';
+----+------------+
| Id | Name       |
+----+------------+
|  3 | %          |
|  4 | %%         |
|  5 | \%         |
|  7 | EF%GH      |
|  8 | IJ\\%%\\KL |
+----+------------+
5 rows in set (0.00 sec)

mysql> select * from `Hoge` where `Name` COLLATE utf8_GENERAL_ci LIKE '%\\%%';
+----+------------+
| Id | Name       |
+----+------------+
|  3 | %          |
|  4 | %%         |
|  5 | \%         |
|  7 | EF%GH      |
|  8 | IJ\\%%\\KL |
+----+------------+
5 rows in set (0.00 sec)

「%%」の検索

mysql> select * from `Hoge` where `Name` COLLATE utf8_unicode_ci LIKE '%\\%\\%%';
+----+------------+
| Id | Name       |
+----+------------+
|  3 | %          |
|  4 | %%         |
|  5 | \%         |
|  7 | EF%GH      |
|  8 | IJ\\%%\\KL |
+----+------------+
5 rows in set (0.00 sec)

mysql> select * from `Hoge` where `Name` COLLATE utf8_unicode_ci LIKE '%\\\\%\\%%';
Empty set (0.00 sec)

mysql> select * from `Hoge` where `Name` COLLATE utf8_unicode_ci LIKE '%\\%\\%\\%';
+----+------------+
| Id | Name       |
+----+------------+
|  3 | %          |
|  4 | %%         |
|  5 | \%         |
|  7 | EF%GH      |
|  8 | IJ\\%%\\KL |
+----+------------+
5 rows in set (0.00 sec)

mysql> select * from `Hoge` where `Name` COLLATE utf8_unicode_ci LIKE '%\\%\\%\\%\\%\\%\\%\\%\\%\\%';
+----+------------+
| Id | Name       |
+----+------------+
|  3 | %          |
|  4 | %%         |
|  5 | \%         |
|  7 | EF%GH      |
|  8 | IJ\\%%\\KL |
+----+------------+
5 rows in set (0.00 sec)

mysql> select * from `Hoge` where `Name` COLLATE utf8_unicode_ci LIKE '%％％%';
+----+------------+
| Id | Name       |
+----+------------+
|  4 | %%         |
|  8 | IJ\\%%\\KL |
+----+------------+
2 rows in set (0.00 sec)

mysql> select * from `Hoge` where `Name` COLLATE utf8_GENERAL_ci LIKE '%\\%\\%%';
+----+------------+
| Id | Name       |
+----+------------+
|  4 | %%         |
|  8 | IJ\\%%\\KL |
+----+------------+
2 rows in set (0.00 sec)

「\%」の検索

mysql> select * from `Hoge` where `Name` COLLATE utf8_unicode_ci LIKE '%\\\\\%%';
+----+------------+
| Id | Name       |
+----+------------+
|  1 | \          |
|  2 | \\         |
|  5 | \%         |
|  6 | AB\CD      |
|  8 | IJ\\%%\\KL |
+----+------------+
5 rows in set (0.00 sec)

mysql> select * from `Hoge` where `Name` COLLATE utf8_unicode_ci LIKE '%\\\\\\\%%';
+----+------------+
| Id | Name       |
+----+------------+
|  5 | \%         |
|  8 | IJ\\%%\\KL |
+----+------------+
2 rows in set (0.00 sec)

mysql> select * from `Hoge` where `Name` COLLATE utf8_unicode_ci LIKE '%＼％%';                                                    
+----+------------+
| Id | Name       |
+----+------------+
|  5 | \%         |
|  8 | IJ\\%%\\KL |
+----+------------+
2 rows in set (0.00 sec)

mysql> select * from `Hoge` where `Name` COLLATE utf8_GENERAL_ci LIKE '%\\\\\%%';
+----+------------+
| Id | Name       |
+----+------------+
|  5 | \%         |
|  8 | IJ\\%%\\KL |
+----+------------+
2 rows in set (0.00 sec)

「%\」の検索

mysql> select * from `Hoge` where `Name` COLLATE utf8_unicode_ci LIKE '%\\%\\\\%';
Empty set (0.00 sec)

mysql> select * from `Hoge` where `Name` COLLATE utf8_unicode_ci LIKE '%\\\\%\\\\%';
Empty set (0.00 sec)

mysql> select * from `Hoge` where `Name` COLLATE utf8_unicode_ci LIKE '%\\%\\\\\\%';
+----+------------+
| Id | Name       |
+----+------------+
|  8 | IJ\\%%\\KL |
+----+------------+
1 row in set (0.00 sec)

mysql> select * from `Hoge` where `Name` COLLATE utf8_unicode_ci LIKE '%％＼%';
+----+------------+
| Id | Name       |
+----+------------+
|  8 | IJ\\%%\\KL |
+----+------------+
1 row in set (0.00 sec)

mysql> select * from `Hoge` where `Name` COLLATE utf8_GENERAL_ci LIKE '%\\%\\\\%';
+----+------------+
| Id | Name       |
+----+------------+
|  8 | IJ\\%%\\KL |
+----+------------+
1 row in set (0.00 sec)