MYSQL 随机数据获取
以下方法是前提WHERE为空的时候,当存在WHERE的时候效率非常低..
网上目前流窜着各种取数据的方法,除了
SELECT s . * , rand( ) AS r
FROM ko_stars AS s
ORDER BY r
LIMIT 5
FROM ko_stars AS s
ORDER BY r
LIMIT 5
这种超低效率的方法外,好像都存在数据量获取小于指定值的现象,因为网上的普遍方法是随机一个最小ID获取连续的5个记录,但这个最小ID没做好判断,网上版本如下:
SELECT *
FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;
FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;
根据需求的实际情况,本人加已改版
"SELECT t.*
FROM `${table}` AS t JOIN (
SELECT
@maxid:=ifnull((FLOOR(RAND() * (SELECT MAX(id) FROM `${table}` as t where ${where}))),0),
@lid:=ifnull((select id from ${table} as t where ${where} order by id desc limit ".(${show}-1).",1),0),
(if(@maxid-@lid>0,@lid,@maxid)+0) as id
) as mt
WHERE t.id >= mt.id and ${where}
ORDER BY t.id LIMIT ${show}";
FROM `${table}` AS t JOIN (
SELECT
@maxid:=ifnull((FLOOR(RAND() * (SELECT MAX(id) FROM `${table}` as t where ${where}))),0),
@lid:=ifnull((select id from ${table} as t where ${where} order by id desc limit ".(${show}-1).",1),0),
(if(@maxid-@lid>0,@lid,@maxid)+0) as id
) as mt
WHERE t.id >= mt.id and ${where}
ORDER BY t.id LIMIT ${show}";
本质原理跟上面那个差不多,但这个SQL可以保证取出足量的数据,除非你总数都没有${show}的数量多,那样有几个就返回几个
