WHERE .. IN (..)のリストの順番でソートするORDER BY FIELDの仕組み

MySQLには`WHERE col IN (..) ORDER BY FILED(col, ..)`という書き方でINに並べた順番にソートしなおせるという知見がある。


こんなテーブルがあって、

mysql56> SELECT * FROM t1;
+-----+-------+
| num | val   |
+-----+-------+
|   1 | one   |
|   2 | two   |
|   3 | three |
|   4 | four  |
|   5 | five  |
|   6 | six   |
|   7 | seven |
|   8 | eight |
|   9 | nine  |
|  10 | ten   |
+-----+-------+
10 rows in set (0.00 sec)

INにテキトーな値を並べてやっても、

mysql56> SELECT * FROM t1 WHERE num IN (7, 5, 3);
+-----+-------+
| num | val   |
+-----+-------+
|   3 | three |
|   5 | five  |
|   7 | seven |
+-----+-------+
3 rows in set (0.00 sec)

IN演算子に渡した順番には返ってこない、これがフツーの動作。

これを7 => 5 => 3の順番で返してほしいとか 業の深い 俺の知らないところで大変な何かを抱えている人もいたりするので、そんな時に使うのがORDER BY FIELD()

mysql56> SELECT * FROM t1 WHERE num in (7, 5, 3) ORDER BY FIELD(num, 7, 5, 3);
+-----+-------+
| num | val   |
+-----+-------+
|   7 | seven |
|   5 | five  |
|   3 | three |
+-----+-------+
3 rows in set (0.00 sec)

初めて見た時はﾌｧｯ!? ってなったけど、クエリーをこう書き換えると、たぶんやってることが伝わる。

mysql56> SELECT *, FIELD(num, 7, 5, 3) AS sort_rank FROM t1 WHERE num in (7, 5, 3) ORDER BY sort_rank;
+-----+-------+-----------+
| num | val   | sort_rank |
+-----+-------+-----------+
|   7 | seven |         1 |
|   5 | five  |         2 |
|   3 | three |         3 |
+-----+-------+-----------+
3 rows in set (0.00 sec)

ORDER BY FIELDはORDER BY句のバリエーションじゃなくて、FILED関数の結果でORDER BYしている。

FIELD関数のドキュメントはこちら。第1引数に検索したい値、第2引数以降に検索元となるリストを与える感じ。
これが、numの値が(7, 5, 3)の何番目にあるかを整数で返すので、そこでソートできる。

http://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_field

で、FIELD関数は関数で、ORDER BY FIELDという構文じゃない。
つまり、やってることは「関数の演算結果でソート」だから、ORDER BY狙いのキーは動かない。

mysql56> explain SELECT * FROM t1 WHERE num in (7, 5, 3) ORDER BY FIELD(num, 7, 5, 3);
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | t1    | range | num           | num  | 8       | NULL |    3 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

基本的にWHERE .. IN ..でPRIMARY KEYをリテラルリストで数行～数十行取ってくる時くらいしか安全な使い道はなさそうな気がする（けど、そういうケースでは割と便利だったりする）
ご利用は計画的に。