随机取mysql的N条数据

先来个看似复杂的：

需要查user表里，code为100，的随机不重复的2两个name值

select * from (SELECT id,name,code FROM `user` WHERE code='100')
AS t1 JOIN (
SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `user`)-(SELECT MIN(id) FROM `user`))+(SELECT MIN(id) FROM `user`)) AS id
) AS t2
WHERE t1.id >= t2.id
ORDER BY rand()*(t1.id) LIMIT 2;

以下内容转自：http://hi.baidu.com/allense7en/item/10fdd909ab9e1fdedde5b0b1

有个需求是这样：

要先随机向数据库里分别取100条数据，

SELECT * FROM test ORDER BY RAND() LIMIT 4 ;

SELECT * FROM test WHERE id NOT IN (1,2,5) ORDER BY RAND() LIMIT 4 ;

更优化的写法（比上一种写法快一倍）：

SELECT * FROM `user` WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM `user`)))  ORDER BY RAND() LIMIT 2 ;

SELECT * FROM `user` WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM `user`))) AND id NOT IN(2,5) ORDER BY RAND() LIMIT 2 ;

可不可以再快点：

SELECT *
FROM `user` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `user`)-(SELECT MIN(id) FROM `busstopinfo`))+(SELECT MIN(id) FROM `user`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 2;