在MySQL中,要想在表中获取随机一条记录,一般情况下,我们第一个想到的sql语句就是:
SELECT * FROM tablename ORDER BY RAND() LIMIT 1。
但在MYSQL的官方手册,里面针对RAND()的提示大概意思就是,在ORDER BY从句里面不能使用RAND()函数,因为这样会导致数据列被多次扫描
You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.
可见如果用上面的方法,查询效率会大打折扣
百度了一下有以下两种方法相对来说查询效率会高点:
方法①:
SELECT * FROM table_name AS t1
JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM table_name)-(SELECT MIN(id) FROM table_name))+(SELECT MIN(id) FROM table_name)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;
在本地上测了一下,基本是查询话费时间基本在0.06s左右
方法②:
SELECT * from table_name
WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM table_name)-(SELECT MIN(id) FROM table_name)) + (SELECT MIN(id) FROM table_name)))
ORDER BY id LIMIT 1;
在本地的查询时间在0.08s左右
从时间上看采用JOIN的语法比直接在WHERE中使用函数效率还要高很多。
记一下!!!