MySQL 5.7で濁点・半濁点を含む半角カタカナをあいまい検索

TL; DR

照合順序を利用したLIKE検索の際に、濁点・半濁点を含む半角カタカナを区別せずに検索する方法を簡単にまとめました。

SELECT
    title
FROM
    articles
WHERE
    REPLACE(REPLACE(title, 'ﾞ', ''), 'ﾟ', '') COLLATE utf8mb4_unicode_ci LIKE CONCAT(
        '%',
        REPLACE(REPLACE(@keyword, 'ﾞ', ''), 'ﾟ', ''),
        '%'
    )
;

環境

• MySQL 5.7

前提

簡単なあいまい検索を実装する際に、照合順序(COLLATION)を変更してLIKE検索をかけるという方法があります。
詳細は割愛しますが、MySQLでは照合順序に utf8_unicode_ci ( utf8mb4_unicode_ci )を指定することで、大文字・小文字、全角・半角、ひらがな・カタカナ、濁音・半濁音をすべて区別しないようになります。

mysql> SELECT title FROM articles WHERE title COLLATE utf8mb4_unicode_ci LIKE '%かんたん%';
+--------------+
| title        |
+--------------+
| かんたん      |
| カンタン      |
| がんたん      |
| ｶﾝﾀﾝ         |
+--------------+
4 rows in set (0.00 sec)

mysql> SELECT title FROM articles WHERE title COLLATE utf8mb4_unicode_ci LIKE '%a%';
+-----------+
| title     |
+-----------+
| abc       |
| ABC       |
| ａｂｃ     |
| ＡＢＣ     |
+-----------+
4 rows in set (0.01 sec)

問題

上記の方法だけでは濁点・半濁点を含む半角カタカナに関してはうまく検索できないです。
半角カタカナで濁音・半濁音を入力すると濁点と半濁点で1文字使用するため、単純に照合順序を変更するだけではLIKE検索の際にマッチングしません。¹

mysql> SELECT title FROM articles;
+-----------------------------------------+
| title                                   |
+-----------------------------------------+
| Fabricでかんたんデプロイ                  |
| Fabricでかんたんﾃﾞﾌﾟﾛｲ                    |
+-----------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT title FROM articles WHERE title COLLATE utf8mb4_unicode_ci LIKE '%デプロイ%';
+-----------------------------------+
| title                             |
+-----------------------------------+
| Fabricでかんたんデプロイ            |
+-----------------------------------+
1 row in set (0.01 sec)

mysql> SELECT title FROM articles WHERE title COLLATE utf8mb4_unicode_ci LIKE '%ﾃﾞﾌﾟﾛｲ%';
+-----------------------------------------+
| title                                   |
+-----------------------------------------+
| Fabricでかんたんﾃﾞﾌﾟﾛｲ                    |
+-----------------------------------------+
1 row in set (0.00 sec)

解決法

やや強引ですが、検索対象のカラムの値から半角の濁点・半濁点を削除すればマッチするようになります。

mysql> SELECT title FROM articles WHERE REPLACE(REPLACE(title, 'ﾞ', ''), 'ﾟ', '') COLLATE utf8mb4_unicode_ci LIKE '%デプロイ%';
+-----------------------------------------+
| title                                   |
+-----------------------------------------+
| Fabricでかんたんデプロイ                  |
| Fabricでかんたんﾃﾞﾌﾟﾛｲ                    |
+-----------------------------------------+
2 rows in set (0.01 sec)

濁点・半濁点を含む半角カタカナでLIKE検索したい場合は、検索キーワード側でも同様の処理をすれば良いです。

mysql> SET @keyword='ﾃﾞﾌﾟﾛｲ';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT title FROM articles WHERE REPLACE(REPLACE(title, 'ﾞ', ''), 'ﾟ', '') COLLATE utf8mb4_unicode_ci LIKE CONCAT('%', REPLACE(REPLACE(@keyword, 'ﾞ', ''), 'ﾟ', ''), '%');
+-----------------------------------------+
| title                                   |
+-----------------------------------------+
| Fabricでかんたんデプロイ                  |
| Fabricでかんたんﾃﾞﾌﾟﾛｲ                    |
+-----------------------------------------+
2 rows in set (0.00 sec)

まとめ

どうしても運用の都合上半角カタカナのデータを登録する必要があるとのことだったので今回のような実装をしましたが、データの登録時点で制限を設けるなどすればこのような実装は不要です。
パフォーマンスについては計測していないので、気が向いたら確認したいです。

1. LIKE検索では1文字ずつマッチングするため。=で比較した場合はちゃんとマッチングします。 ↩