MySQL IN Result Set Operator

Question

In MySQL, when using the IN operator, can we ask MySQL to return the result set (record set) in the same order as the IN set?

Explanation: Suppose we have a table items (item_id, item_name);

and request:

select * from items where item_id in (1,3,5,7,2,4,6,8);

can we set the result (recordset) where the records are in the same order as the IN operator. i 1,3,5,7,2,4,6,8 of record_id s

This is not true; MySQL seems to optimize the search and give a default order (the same as the order of these records stored in the File System).

Answer 1

You can use the MySQL field function in the ORDER BY clause:

 select * from items where item_id in (1,3,5,7,2,4,6,8) order by field(item_id, 1,3,5,7,2,4,6,8) 

field function:

FIELD(str,str1,str2,str3,...)

Returns the index (position) of str in str1, str2, str3, ... list. Returns 0 if str is not found.

If all FIELD() arguments are strings, all arguments are compared as strings. If all arguments are numbers, they are compared as numbers. Otherwise, the arguments are compared as double.