如何使用SELECT IN()保持订单?

Is there a way to keep the order when using SELECT WHERE IN()? For example, using the following query:

在()中使用SELECT WHERE时是否有保持顺序的方法?例如，使用以下查询:

SELECT id FROM data_table WHERE id IN(56,55,54,1,7);

The results will come back using the default order by id. 1,7,54,55,56

结果将使用id为1、7、54、55、56的默认顺序返回

When I want to keep the order used in the IN: 56,55,54,1,7

当我想保持在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.

有没有一种快速的方法可以在mySQL中实现，或者我必须在代码中命令它。

Thanks :)

谢谢:)

3 个解决方案

#1

Use FIND_IN_SET:

使用FIND_IN_SET:

ORDER BY FIND_IN_SET(id, '56,55,54,1,7')

#2

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://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/
http://ivanjovanovic.com/2008/04/01/preserving-ordering-with-where-in-clause-in-mysql/

#3

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?

为什么不让应用程序在接收结果时重新排序，而不是强制数据库执行呢?

#1