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屋！