How do you keep the order using SELECT WHERE IN()?
Is there a way to keep the order when using SELECT WHERE IN()? For example, using the following query:
SELECT id FROM data_table WHERE id IN(56,55,54,1开发者_StackOverflow,7);
The results will come back using the default order by id. 1,7,54,55,56
When I want to keep the order used in the IN: 56,55,54,1,7
Is there a quick way to do this in MySQL or will I be forced to order it after in code?
Use FIND_IN_SET:
ORDER BY FIND_IN_SET(id, '56,55,54,1,7')
You can also use FIELD:
ORDER BY FIELD(id, '56,55,54,1,7')
- http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field
- http://ivanjovanovic.com/2008/04/01/preserving-ordering-with-where-in-clause-in-mysql/
You could do a UNION, that might return the order the same way.
BUT:
Why not just have your application reorder the results when it receives them, rather than forcing the DB to do it?
精彩评论