mysql隨機獲取記錄
MYSQL的隨機抽取實現方法。舉個例子,要從tablename表中隨機提取一條記錄,大家一般的寫法就是:SELECT * FROM tablename ORDER BY RAND() LIMIT 1
但是,後來我查了一下MYSQL的官方手冊,裡面針對RAND()的提示大概意思就是,在ORDER BY從句裡面不能使用RAND()函數,因為這樣會導致數據列被多次掃描。但是在MYSQL 3.23版本中,仍然可以通過ORDER BY RAND()來實現隨機。
www.2cto.com
但是真正測試一下才發現這樣效率非常低。一個100萬余條的庫,查詢1條數據,居然要4秒以上。查看官方手冊,也說rand()放在ORDER BY 子句中會被執行多次,自然效率及很低。
You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.
搜索Google,網上基本上都是查詢max(id) * rand()來隨機獲取數據。
select * from instanceprice_attach where id <= (SELECT FLOOR(RAND() * (SELECT MAX(ID) FROM INSTANCEPRICE_ATTACH))) order by id limit 1
這樣大概95ms。
原來寫的是>=,一直是id最大的那條記錄。
有人這樣處理取得重復記錄:
SELECT t1.*
FROM tablename AS t1 JOIN
(SELECT ROUND(RAND() * ((SELECT MAX(id) FROM tablename )-(SELECT MIN(id) FROM tablename ))+
(SELECT MIN(id) FROM tablename)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;
SELECT * FROM tablename
WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM tablename )-
(SELECT MIN(id) FROM tablename )) + (SELECT MIN(id) FROM tablename )))
ORDER BY id LIMIT 1;
大概都是90多毫秒,網上說第一種寫法會快一點,但是我測試貌似沒什麼區別。