随机取mysql的N条数据

最新推荐文章于 2023-06-17 15:48:40 发布

design321

最新推荐文章于 2023-06-17 15:48:40 发布

阅读量957

点赞数

分类专栏： mysql php

php 同时被 2 个专栏收录

63 篇文章

订阅专栏

mysql

32 篇文章

订阅专栏

本文介绍如何使用SQL进行高效随机数据抽取，包括基本的随机查询、排除特定ID的随机查询及进一步优化技巧。通过不同的SQL语句实现方式对比，提供了一种更快速的数据随机选取方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

先来个看似复杂的：

需要查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;