mysql随机抽取数据
2024-10-20 20:53:01
-- 慢
SELECT * FROM table_name ORDER BY rand() LIMIT 5; -- 较慢
SELECT * FROM `table`
WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`)))
ORDER BY id LIMIT 1; -- 快 `table 有 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; -- 快 `table 没有有 id 字段
select * from (select @rownum:=@rownum + 1 as id,value from `table`,(select @rownum:=0) as a) as t1 join (
select round( rand() * (
(select max(b.id) from (select @rownum_max:=@rownum_max + 1 as id,value from `table`,(select @rownum_max:=0) as a) as b ) -
(select min(b.id) from (select @rownum_min:=@rownum_min + 1 as id,value from `table`,(select @rownum_min:=0) as a) as b )
)) +
(select min(b.id) from (select @rownum_min1:=@rownum_min1 + 1 as id,value from `table`,(select @rownum_min1:=0) as a) as b ) as id
) as t2
on t1.id>= t2.id
order by t1.id limit 1
缺点:
每次查询后会获得连续的n条数据
解决办法:
每次查一条数据,重复查询n 次
最新文章
- 三、jQuery--jQuery基础--jQuery基础课程--第11章 jQuery 工具类函数
- Hadoop JobHistory
- Java中对List集合排序的两种方法
- ubuntu 14.04 安装preforce
- 新版ADT出现appcompat_v7的问题
- POJ 3419 Difference Is Beautiful
- C# DropDownList 绑定枚举类
- [Luogu1342] 请柬 - 最短路模板
- Android中R文件的丢失问题以及aapt.exe停止工作如何解决
- Linux下的磁盘分区和逻辑卷
- [认证授权] 5.OIDC(OpenId Connect)身份认证(扩展部分)
- WordPress版微信小程序2.6版发布
- 读书笔记--Android Gradle权威指南(上)
- Matlab disp()
- JavaScript -基础- 函数与对象
- Redis学习---Redis操作之有序集合
- 扯不清楚的virtual和abstract
- IT行业的个人见解
- linux下 XGCOM串口助手的安装
- linux 进程间通信方式
热门文章
- 简单理解设计模式——享元模式-线程池-任务(tesk)
- NOde.js的安装和简介
- 当vps服务器被墙,如果用xshell连接
- Windows下Charles从下载安装到证书设置和浏览器抓包
- Git详解之文件状态
- Message: 'chromedriver' executable needs to be available in the path.
- Arduino系列之按键模块(一)
- 如何更改Jframe里Jpanel的大小
- qt creator源码全方面分析(2-6)
- One Stage目标检测