MySQLで関連度順に並べ替える方法は？

MySQLで検索結果を関連度の高い順に並べ替えるには、CASE文をORDER BY句と組み合わせて使用します。この記事では、実際にテーブルを作成しながら、その仕組みをわかりやすく解説します。

サンプルテーブルの作成

まず、動作を確認するためのテーブルを作成します。テーブル作成のクエリは以下の通りです。

mysql> create table OrderByRelevance
    -> (
    -> UserId int,
    -> UserName varchar(200)
    -> );
Query OK, 0 rows affected (0.51 sec)

テストデータの挿入

次に、INSERTコマンドを使ってテーブルにレコードを挿入します。クエリは以下の通りです。

mysql> insert into OrderByRelevance values(101,'Carol Smith');
Query OK, 1 row affected (0.18 sec)

mysql> insert into OrderByRelevance values(102,'Carol Adams');
Query OK, 1 row affected (0.17 sec)

mysql> insert into OrderByRelevance values(103,'Carolnathan Todd');
Query OK, 1 row affected (0.33 sec)

mysql> insert into OrderByRelevance values(104,'John Smith');
Query OK, 1 row affected (0.22 sec)

mysql> insert into OrderByRelevance values(105,'Sam Carol Bond');
Query OK, 1 row affected (0.12 sec)

テーブルの全レコードを確認

SELECT文を使って、テーブル内のすべてのレコードを表示してみましょう。

mysql> select *from OrderByRelevance;

出力結果

+--------+------------------+
| UserId | UserName         |
+--------+------------------+
| 101    | Carol Smith      |
| 102    | Carol Adams      |
| 103    | Carolnathan Todd |
| 104    | John Smith       |
| 105    | Sam Carol Bond   |
+--------+------------------+
5 rows in set (0.00 sec)

関連度順に並べ替えるクエリ

それでは、キーワード「Carol」に対する関連度順に結果を並べ替えるクエリを見てみましょう。

mysql> select max(UserId)as Id,UserName from OrderByRelevance
    -> where UserName like '%Carol%' group by UserName
    -> order by case when UserName like 'Carol%' THEN 0
    -> WHEN UserName like '% %Carol% %' THEN 1
    -> WHEN UserName like '%Carol' THEN 2
    -> else 3
    -> end,UserName;

出力結果

+------+------------------+
|   Id | UserName         |
+------+------------------+
| 102  | Carol Adams      |
| 101  | Carol Smith      |
| 103  | Carolnathan Todd |
| 105  | Sam Carol Bond   |
+------+------------------+
4 rows in set (0.00 sec)

クエリの仕組み

このクエリでは、CASE文を使って関連度に優先順位を付けています。具体的には以下のロジックです。

• 優先度 0：名前が「Carol」で始まるもの（例：Carol Adams、Carol Smith）
• 優先度 1：名前の中間に「Carol」を含むもの（例：Sam Carol Bond）
• 優先度 2：名前が「Carol」で終わるもの
• 優先度 3：上記以外のもの

このように、検索キーワードが文字列のどの位置に現れるかによって優先順位を変えることで、ユーザーにとってより自然で関連性の高い並べ替えを実現できます。なお、キーワード「Carol」を含まない「John Smith」はWHERE句の条件によって除外されている点にも注目してください。