本站承诺永不接任何虚假欺骗、联盟广告、弹窗广告、病毒广告、诱导充值等影响用户体验的广告,广告屏蔽插件会影响本站部分功能,还请不要屏蔽本站广告,感谢支持!

当前位置:首页 / 正文

2021-04-19 | 编程技术 | 2517 次阅读 | 等你评论 | 2 次点赞 | 繁体

工作中会遇到从数据库中随机获取一条或多条记录的场景,下面介绍几种随机获取的方法供参考。

首先创建个 users 表演示:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表中存放30万记录。

1、使用 order by rand()

SELECT * FROM users ORDER BY RAND() LIMIT 1;

LIMIT 1 的执行时间:0.559s, LIMIT 100 耗时 0.660s。

此种方法在数据量小的情况下可以使用,但在生产环境不建议使用。

MYSQL 手册里面针对 RAND() 的提示大概意思就是,在 ORDER BY 从句里面不能使用 RAND() 函数,因为这样会导致数据列被多次扫描,导致效率相当相当的低,效率不行,切忌使用。

2、使用 join 及 rand() 函数

SELECT * FROM users AS t1 JOIN (SELECT ROUND(RAND()*(SELECT MAX(id) 
FROM users)) AS id) AS t2 WHERE t1.id>=t2.id ORDER BY t1.id LIMIT 1;

此 sql 随机获取一条的时间是 0.014s,LIMIT 100 时耗时 0.020s,性能上没差多少。

获取多条的话有时会达不到要求(获取的记录数可能达不到多条)

3、子查询及 rand() 函数

SELECT * FROM users as t1 WHERE t1.id>=(RAND()*(SELECT MAX(id) FROM users)) LIMIT 1;

随机取一条的耗时:0.015,LIMIT 100 时耗时 0.026s。

随机获取一条记录推荐使用 第 2 种方法,在 30 万条记录时也只需 0.014s。

2021.06.02 更新:

上边第二种方法更优写法:

SELECT * FROM users  AS t1  JOIN (SELECT ROUND(RAND() * ((SELECT MAX(userId) FROM `users`)-(SELECT MIN(userId) FROM users))+(SELECT MIN(userId) FROM users)) AS userId) AS t2 WHERE t1.userId >= t2.userId ORDER BY t1.userId LIMIT 1

第三种方法的更优写法:

SELECT * FROM users WHERE userId >= ((SELECT MAX(userId) FROM users)-(SELECT MIN(userId) FROM users)) * RAND() + (SELECT MIN(userId) FROM users) LIMIT 1

via:
MySQL数据库中随机获取一条或多条记录_River106的博客-CSDN博客_mysql随机取一条记录
https://blog.csdn.net/angellee1988/article/details/103845533

MYSQL随机读取一条数据_shenzhou_yh的博客-CSDN博客_mysql 随机查询一条数据
https://blog.csdn.net/shenzhou_yh/article/details/90550090

标签: mysqlsql

猜你喜欢
如何定位Mysql中CPU占用高的查询语句
今天 mysql 服务器突然 CPU 告警,记录一下问题查找的过程第一步查看具体是哪个线程占用CPU最高1、在 Linux 中使用 top 命令找到 mysql 进程 PID2、指定进程 PID...
mysql8利用CTE特性实现递归查询
递归查询分为父子查询和子父查询。父子查询: 根据父 id 查询下面所有子节点数据;子父查询: 根据子 id 查询上面所有父节点数据;下边就利用 mysql8 新增语法实现递归查询,表结构及数...
mysql数据库删除重复的数据只保留一条
问题引入假设一个场景,一张用户表,包含 3 个字段:id,identity_id,name。现在身份证号 identity_id 和姓名 name 有很多重复的数据,需要删除多余数据只保留一条有...
Mysql 窗口函数学习
窗口函数是数据库查询中的一个经典场景,在解决某些特定问题时甚至是必须的。个人认为,在单纯的数据库查询语句层面【即不考虑 DML、SQL 调优、索引等进阶】,窗口函数可看作是考察求职者 SQL 功...
三种方式修改 MySQL 数据库名
在 Innodb 数据库引擎下修改数据库名的方式与 MyISAM 引擎下修改数据库的方式完全不一样,如果是 MyISAM 可以直接去数据库目录中 mv 就可以,Innodb 如果用同样的方法修改...
Navicat Premium Mac 12.022 破解(20200131亲测可用!!!)
背景受新型冠状病毒影响,公司要求在家远程办公,故准备强行搞个 Navicat Premium Mac 12 破解版本。历经了种种种种种种磨难与艰辛与火海,终于破解成功了。因为要经常使用 MySQ...
Python小技巧之不用GUI,照样实现图形界面
小王平常的工作是做数据处理的,手中自然握有大量的数据,在日常工作中经常需要根据业务的需求提取相应的数据,有些需求是固定的,写好脚本之后只要定期提取数据就行了。 但是,像我这么懒的人,这种工作怎么
(首次提交评论需审核通过才会显示,请勿重复提交)