MySQL按特定ID值排序 [英] Mysql order by specific ID values

问题描述

是否有可能使用预定义的一组列值(ID)按"order by"对mysql进行排序，例如:order by(ID = 1,5,4,3)，因此我将获得记录1、5、4依次排列3个?

Is it possible to sort in mysql by "order by" using predefined set of column values (ID) like: order by (ID=1,5,4,3) so I would get record 1, 5, 4, 3 in that order out?

更新:关于滥用mysql ;-)我必须解释为什么我需要这个...

UPDATE: About abusing mysql ;-) I have to explain why I need this...

我希望我的记录每5分钟随机更改一次排序.我有一个cron任务来执行更新表，以在其中放置不同的随机排序顺序.只有一个问题！分页.我将有一位访问者来到我的页面，我给他前20个结果.他将等待6分钟，然后转到第2页，并且由于排序顺序已经更改，因此他将得到错误的结果.

I want my records change sort randomly every 5 minutes. I have a cron task to do the update table to put different, random sort order in it. There is just one problem! PAGINATION. I will have a visitor who comes to my page and I give him first 20 results. He will wait 6 minutes and go to page 2 and he will have wrong results as the sort order had allready changed.

所以我认为，如果他进入我的网站，我会将所有ID都放在一个会话中，而当他进入第2页时，即使排序已经改变，他也会得到正确的记录.

So I thought that if he comes to my site I put all the ID's to a session and when he is in page 2 he get's the correct records out even if the sorting allready changed.

还有其他更好的方法吗?

Is there any other way, better, to do this?

推荐答案

您可以使用ORDER BY和FIELD函数. 参见 http://lists.mysql.com/mysql/209784

You can use ORDER BY and FIELD function. See http://lists.mysql.com/mysql/209784

SELECT * FROM table ORDER BY FIELD(ID,1,5,4,3)

它使用 Field()函数，根据文档，返回str1，str2，str3，...列表中str的索引(位置).如果找不到str，则返回0".因此，实际上您是根据此函数的返回值(即给定集中字段值的索引)对结果集进行排序.

It uses Field() function, Which "Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found" according to the documentation. So actually you sort the result set by the return value of this function which is the index of the field value in the given set.

这篇关于MySQL按特定ID值排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！