第一种语句0.055秒查询
<?php $offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `table` "); $offset_row = mysql_fetch_object( $offset_result ); $offset = $offset_row->offset; $result = mysql_query( " SELECT * FROM `table` LIMIT $offset, 1 " ); |
第二种语句。0.001秒查询
SELECT * FROM `dede_arctiny` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `dede_arctiny`)-(SELECT MIN(id) FROM `dede_arctiny`))+(SELECT MIN(id) FROM `dede_arctiny`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id LIMIT 1; |
但是这样获得的是5条连续的记录。 解决办法只能是每次查询一条,查询5次,但这个又不能满足我的要求了,我要一次找几条
高效写法
SELECT * FROM USER WHERE userId >= ((SELECT MAX(userId) FROM USER )-(SELECT MIN(userId) FROM USER )) * RAND() + (SELECT MIN(userId) FROM USER ) LIMIT 5 |
这样测试发现满足了我的要求同时测试10W数据也只要0.0几秒非常的快。如果把*换成单独查询id给数组,然后根据数组id单独查出一条记录,会更有效率。
代码实例:
$r = mysql_query("SELECT file_id FROM pd_files WHERE file_id >= (( SELECT MAX(file_id) FROM pd_files ) - ( SELECT MIN(file_id) FROM pd_files )) * RAND() + ( SELECT MIN(file_id) FROM pd_files ) LIMIT 14"); $arr = array(); while ($row = mysql_fetch_assoc($r)) { $arr[] = $row; } $s0 = $arr[0]['file_id']; $s1 = $arr[1]['file_id']; print($s0);//用来测试 $get_shuchu = mysql_query("select * from (SELECT file_id,file_name,file_time,file_extension FROM pd_files WHERE file_id=$s0)t union all select * from (SELECT file_id,file_name,file_time,file_extension FROM pd_files WHERE file_id=$s1)t union all select * from (SELECT file_id,file_name,file_time,file_extension FROM pd_files WHERE file_id=$s2)t"); print($get_shuchu);//用来测试 |