MySQLでSELECT…WHERE id IN()の結果を指定した順序で並べ替える方法（FIELD関数）

MySQLで SELECT … WHERE id IN(..) を実行すると、IN 句で指定した順序に関係なく、通常は主キーなどの順番で結果が返されます。そこで活躍するのが FIELD() 関数です。FIELD() 関数を ORDER BY と組み合わせることで、任意の列を基準に、指定した値の並び順どおりに結果を取得できます。

基本構文

SELECT *FROM yourTableName WHERE yourColumnName IN('value1','value2',.......N)
ORDER BY FIELD(yourColumnName,'value1','value2',.......N);

FIELD() 関数は、第1引数で渡した値が、第2引数以降のリストの中で何番目に位置するかを数値で返します。この戻り値を基準にソートすることで、IN 句で指定した通りの順序で結果を並べ替えられる仕組みです。

サンプルテーブルの作成

構文を理解するために、まず動作確認用のテーブルを作成しましょう。

mysql> create table SelectOrderbyField
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Name varchar(30),
   -> Age int,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.62 sec)

レコードの挿入

次に、INSERT コマンドを使ってテーブルにいくつかのレコードを登録します。

mysql> insert into SelectOrderbyField(Name,Age) values('John',23);
Query OK, 1 row affected (0.13 sec)

mysql> insert into SelectOrderbyField(Name,Age) values('Carol',25);
Query OK, 1 row affected (0.16 sec)

mysql> insert into SelectOrderbyField(Name,Age) values('Bob',21);
Query OK, 1 row affected (0.15 sec)

mysql> insert into SelectOrderbyField(Name,Age) values('Mike',28);
Query OK, 1 row affected (0.17 sec)

mysql> insert into SelectOrderbyField(Name,Age) values('Sam',26);
Query OK, 1 row affected (0.12 sec)

mysql> insert into SelectOrderbyField(Name,Age) values('David',23);
Query OK, 1 row affected (0.19 sec)

登録データの確認

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

mysql> select *from SelectOrderbyField;

以下が出力結果です。

+----+-------+------+
| Id | Name  | Age  |
+----+-------+------+
|  1 | John  |   23 |
|  2 | Carol |   25 |
|  3 | Bob   |   21 |
|  4 | Mike  |   28 |
|  5 | Sam   |   26 |
|  6 | David | 23   |
+----+-------+------+
6 rows in set (0.00 sec)

ここから、FIELD() 関数を使った「select … where id in(..)」の並べ替えクエリを見ていきます。

ID列を基準に並べ替える

Id 列を指定した順序（5, 1, 3, 2, 6, 4）で並べ替えるクエリは以下の通りです。

mysql> select *from SelectOrderbyField where Id IN(5,1,3,2,6,4)
   -> order by field(Id,5,1,3,2,6,4);

出力結果を見ると、IN 句で指定した通りの順序でレコードが取得できているのが分かります。

+----+-------+------+
| Id | Name  | Age  |
+----+-------+------+
|  5 | Sam   |   26 |
|  1 | John  |   23 |
|  3 | Bob   |   21 |
|  2 | Carol |   25 |
|  6 | David |   23 |
|  4 | Mike  | 28   |
+----+-------+------+
6 rows in set (0.00 sec)

Name列を基準に並べ替える

文字列型の列でも同様に、Name 列を基準として正しく並べ替えることができます。クエリは以下の通りです。

mysql> select *from SelectOrderbyField where Name IN('Sam','John','Bob','Carol','David','Mike')
   -> order by field(Name,'Sam','John','Bob','Carol','David','Mike');

出力結果：

+----+-------+------+
| Id | Name  | Age  |
+----+-------+------+
| 5  | Sam   | 26   |
| 1  | John  | 23   |
| 3  | Bob   | 21   |
| 2  | Carol | 25   |
| 6  | David | 23   |
| 4  | Mike  | 28   |
+----+-------+------+
6 rows in set (0.03 sec)

まとめ

WHERE 句の IN 条件と組み合わせて FIELD() 関数を ORDER BY で使えば、数値・文字列を問わず、任意の列を指定した順序でソートできます。特定の優先順位でデータを表示したいケース（おすすめ商品の表示順やステータスの並び替えなど）に非常に便利なテクニックなので、ぜひ活用してみてください。