MySQLで部分文字列の位置に基づいて検索結果を並べ替える方法

MySQLで検索結果を「特定の部分文字列が文字列内のどこに出現するか」という位置情報に基づいて並べ替えたい場合には、ORDER BY句とLOCATE()関数を組み合わせることで実現できます。

LOCATE()関数は、指定した部分文字列が対象の文字列内で最初に現れる位置（1始まりのインデックス）を返します。この値をORDER BYに渡すことで、「部分文字列が先頭近くにあるレコードほど上位に表示される」という並び替えが可能になります。

ここでは、実際の手順をサンプルとともに見ていきましょう。

1. サンプルテーブルを作成する

まず、並べ替えの動作を確認するためのテーブルを作成します。

mysql> create table DemoTable1838
    (
    Subject varchar(100)
    );
Query OK, 0 rows affected (0.00 sec)

2. テストデータを挿入する

INSERT文を使って、いくつかのレコードを登録します。すべてのレコードに「MySQL」というキーワードが含まれていますが、出現位置はそれぞれ異なります。

mysql> insert into DemoTable1838 values('MongoDB MySQL');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1838 values('MySQL Java');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1838 values('JavaWithMySQL');
Query OK, 1 row affected (0.00 sec)

3. 登録したレコードを確認する

SELECT文ですべてのレコードを表示してみましょう。

mysql> select * from DemoTable1838;

実行すると、以下のような結果が得られます。

+---------------+
| Subject       |
+---------------+
| MongoDB MySQL |
| MySQL Java    |
| JavaWithMySQL |
+---------------+
3 rows in set (0.00 sec)

この時点では、挿入した順番どおりに表示されています。

4. 部分文字列の位置で並べ替える

それでは本題のクエリです。「MySQL」を含むレコードを抽出し、その出現位置が早い順に並べ替えます。

mysql> select * from DemoTable1838
    where Subject LIKE '%MySQL%'
    ORDER BY LOCATE('MySQL', Subject);

実行結果は以下のとおりです。

+---------------+
| Subject       |
+---------------+
| MySQL Java    |
| MongoDB MySQL |
| JavaWithMySQL |
+---------------+
3 rows in set (0.00 sec)

結果のポイント

注目すべきは並び順の変化です。

• 「MySQL Java」 … 「MySQL」が先頭（位置1）にあるため最上位
• 「MongoDB MySQL」 … 「MySQL」が途中（位置9）にあるため2番目
• 「JavaWithMySQL」 … 「MySQL」が最後尾（位置10）にあるため最下位

このように、LOCATE()関数の戻り値をORDER BYに指定するだけで、部分文字列の出現位置による柔軟なソートが簡単に実装できます。逆順にしたい場合は「DESC」を付ければOKです。検索機能の関連度スコア的な並び替えなどにも応用できる便利なテクニックなので、ぜひ覚えておきましょう。