只需一次查询即可在不使用ORDER BY RAND()的情况下获取RAND()行 [英] Fetching RAND() rows without ORDER BY RAND() in just one query

问题描述

在MySQL中使用RAND()从巨大的表中获取单个随机行非常慢:

Using RAND() in MySQL to get a single random row out of a huge table is very slow:

SELECT quote FROM quotes ORDER BY RAND() LIMIT 1

他们的解决方案是使用两个查询:

Their solution is to use two queries:

SELECT COUNT(*) AS cnt FROM quotes

- Use result to generate a number between 0 and COUNT(*)

SELECT quote FROM quotes LIMIT $generated_number, 1

我想知道，是否仅通过一个查询就能做到这一点.

I was wondering, whether this would be possible in just one query.

所以我的方法是:

SELECT * FROM quotes
LIMIT (
  ROUND(
    (SELECT COUNT(*) FROM quotes) * RAND()
  )
), 1

但是它暗示MySQL在Limit中不允许任何逻辑. 尽管我找不到有关此主题的任何信息，但这是否是正确的.

But it seams MySQL does not allow any logic within Limit. Though I can not find any information about this topic, whether this is true.

所以我的问题:

1. 如何在LIMIT内使用RAND()?
2. 您是否知道其他方法 只需一个查询即可解决此问题?

1. How can I use RAND() within LIMIT?
2. Do you know of any other way to solve this with just one query?

推荐答案

是否存在不能使用存储过程创建准备好的语句的原因?

Is there a reason why a stored procedure cannot be used to create a prepared statement?

DELIMITER //
DROP PROCEDURE IF EXISTS rand_quote//
CREATE PROCEDURE rand_quote()
BEGIN
    SET @rand := ROUND((SELECT COUNT(*) FROM quotes) * RAND());
    SET @sql := CONCAT('SELECT * FROM quotes LIMIT ', @rand, ', 1');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;
//
DELIMITER ;

这篇关于只需一次查询即可在不使用ORDER BY RAND()的情况下获取RAND()行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！