MySQL中的随机行 [英] Random Row in MySQL

问题描述

我正在尝试在MySQL中获得随机行.这是我的方法:

I am trying to get a random row in MySQL. Here is my approach:

SELECT * 
FROM users 
WHERE id = 
 (SELECT floor((max(id) - min(id) + 1) * rand()) + min(id)
  FROM users);

当id中有空格时，这可以返回一个空集.我很好.但是，

This can return an empty set when there are gaps in the ids. I am fine with that. However,

1. 我没有差距，但我仍然经常空套.
2. 更令人不安的是:我偶尔会得到两个或更多结果.

1. I don't have gaps and I still get an empty set rather frequently.
2. More disturbing: I will get two or more results every once in a while.

这种奇怪行为的原因是什么?我该怎么办?

What is the reason for this strange behavior? How, do I get it right?

注意:

• 桌子很大(10 ^ 6 ... 10 ^ 7);我不能使用基于随机排序甚至基于 count()的任何显而易见的已知解决方案.
• 我坚持使用MySQL，我不在乎它是如何在客户端ruby/PHP/任何地方完成的.
• 我试图转换浮点类型，但没有帮助.
• 相应的PostgreSQL变体(随机代替rand，以及某些类型强制转换)可以正常工作.

• the table is quite big (10^6 ... 10^7); I can't use any of the obvious, and known, solutions based on random ordering or even based on using count().
• I'am stuck with MySQL, I don't care how it's done on the client side ruby/PHP/whatever.
• I tried to cast the floating types, but it did not help.
• The corresponding PostgreSQL variant (random instead of rand, and some type casts) works fine.

推荐答案

这样可以避免在聚合过程中重复评估random()，并且也应该更快，因为min(id)和random()仅被评估一次.

This avoids repeated evaluation of random() during the aggregation and should be faster, too, as min(id) and random() are only evaluated once.

SELECT u.* 
FROM   users u
      ,(SELECT min(id) AS min_id, max(id) AS max_id FROM users) x
WHERE  u.id > (floor((x.max_id - x.min_id + 1) * rand()) + x.min_id)
LIMIT  1;

这篇关于MySQL中的随机行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！