MySQLのIN句検索結果を指定した順序どおりに表示する方法｜ORDER BY FIND_IN_SET()の使い方

MySQLでIN()句を使ってレコードを抽出する場合、結果は必ずしもINリストに記述した順序どおりには返されません。抽出結果を自分が指定した値の順番どおりに並べたい場合は、ORDER BYとFIND_IN_SET()を組み合わせて使用します。

まず、FIND_IN_SET()の動作を理解するために、サンプル用のテーブルを作成しましょう。テーブルを作成するクエリは以下のとおりです。

mysql> create table ProductStock
    -> (
    -> ProductId int,
    -> ProductName varchar(20),
    -> ProductQuantity int,
    -> ProductPrice float
    -> );
Query OK, 0 rows affected (0.79 sec)

テーブルへのデータ挿入

続いて、INSERTコマンドを使ってテーブルにいくつかのレコードを挿入します。クエリは以下のとおりです。

mysql> insert into ProductStock values(1,'Product-101',10,500.56);
Query OK, 1 row affected (0.20 sec)
mysql> insert into ProductStock values(25,'Product-111',5,150.00);
Query OK, 1 row affected (0.21 sec)
mysql> insert into ProductStock values(67,'Product-311',7,1000.50);
Query OK, 1 row affected (0.60 sec)
mysql> insert into ProductStock values(55,'Product-561',8,900.00);
Query OK, 1 row affected (0.24 sec)
mysql> insert into ProductStock values(75,'Product-221',15,670.56);
Query OK, 1 row affected (0.14 sec)

登録済みレコードの確認

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

mysql> select *from ProductStock;

出力結果

+-----------+-------------+-----------------+--------------+
| ProductId | ProductName | ProductQuantity | ProductPrice |
+-----------+-------------+-----------------+--------------+
|         1 | Product-101 |              10 |       500.56 |
|        25 | Product-111 |               5 |          150 |
|        67 | Product-311 |               7 |       1000.5 |
|        55 | Product-561 |               8 |          900 |
|        75 | Product-221 |              15 |       670.56 |
+-----------+-------------+-----------------+--------------+
5 rows in set (0.00 sec)

FIND_IN_SET()でINリストの順序どおりにソートする

ここからが本題です。以下のクエリでは、WHERE句のIN()で抽出しつつ、ORDER BY FIND_IN_SET()によってINリスト「25, 55, 67, 75, 1」の順序どおりに結果を並べ替えています。

mysql> select *from ProductStock
    -> where ProductId IN(25,55,67,75,1)
    -> order by find_in_set(ProductId,'25,55,67,75,1');

出力結果

+-----------+-------------+-----------------+--------------+
| ProductId | ProductName | ProductQuantity | ProductPrice |
+-----------+-------------+-----------------+--------------+
|        25 | Product-111 |               5 |          150 |
|        55 | Product-561 |               8 |          900 |
|        67 | Product-311 |               7 |       1000.5 |
|        75 | Product-221 |              15 |       670.56 |
|         1 | Product-101 |              10 |       500.56 |
+-----------+-------------+-----------------+--------------+
5 rows in set (0.31 sec)

出力結果を見ると、ProductIdがINリストで指定したとおり「25 → 55 → 67 → 75 → 1」の順に表示されていることが確認できます。FIND_IN_SET()は、第2引数に渡したカンマ区切りの文字列リストの中で、第1引数の値が何番目に位置するかを数値で返す関数です。この戻り値をORDER BYに渡すことで、昇順・降順といった通常のソートではなく、任意のカスタム順序での並べ替えが実現できます。

なお、同様の目的にはFIELD()関数を使う方法もあります。ORDER BY FIELD(ProductId, 25, 55, 67, 75, 1)のように記述すれば、数値型のカラムをそのまま扱えるため、状況に応じて使い分けるとよいでしょう。