【已验证】MySQL中随机取数据

一、问题出现背景：

• 随机取一条存储的广告信息
• 随机从题库中选择100道题

二、可能原因及尝试过程：

1、百度“mysql中随机取一条数据”查到文章从mysql数据表中随机取出一条记录

 

 2、用数据库表做实验

2.1、数据4474条的表que_bank_list：0.03s/0.166s/0.048s

SELECT * FROM `que_bank_list` WHERE id >= ((SELECT MAX(id) FROM `que_bank_list`)-(SELECT MIN(id) FROM `que_bank_list`)) * RAND() + (SELECT MIN(id) FROM `que_bank_list`) LIMIT 1;
SELECT * FROM `que_bank_list` WHERE id >= (SELECT (MAX(id) - MIN(id)) * RAND() + MIN(id) FROM `que_bank_list`) LIMIT 1;

SELECT * FROM `que_bank_list` ORDER BY RAND() LIMIT 1;

2.2、数据1条的表advertisement：0.031s/0.028s/0.028s/0.098s

SELECT id, image, text, url, remark FROM `advertisement` WHERE is_usable = 1 AND id >= ((SELECT MAX(id) FROM `advertisement` WHERE is_usable = 1) - (SELECT MIN(id) FROM `advertisement` WHERE is_usable = 1)) * RAND() + (SELECT MIN(id) FROM `advertisement` WHERE is_usable = 1)  LIMIT 1;
SELECT id, image, text, url, remark FROM `advertisement` WHERE is_usable = 1 AND id >= (SELECT MAX(id) - MIN(id) FROM `advertisement` WHERE is_usable = 1) * RAND() + (SELECT MIN(id) FROM `advertisement` WHERE is_usable = 1)  LIMIT 1;
SELECT id, image, text, url, remark FROM `advertisement` WHERE is_usable = 1 AND id >= (SELECT (MAX(id) - MIN(id))* RAND() + MIN(id) FROM `advertisement` WHERE is_usable = 1) LIMIT 1;

SELECT id, image, text, url, remark FROM `advertisement` WHERE is_usable = 1 ORDER BY RAND() LIMIT 1;

2.3、数据16433条的表que_bank_option：0.028s/0.421s/0.05s

-- SELECT COUNT(*) FROM `que_bank_option`; # 0.03S
SELECT * FROM `que_bank_option` WHERE id >= ((SELECT MAX(id) FROM `que_bank_option`)-(SELECT MIN(id) FROM `que_bank_option`)) * RAND() + (SELECT MIN(id) FROM `que_bank_option`) LIMIT 1;
SELECT * FROM `que_bank_option` WHERE id >= (SELECT (MAX(id) - MIN(id)) * RAND() + MIN(id) FROM `que_bank_option`) LIMIT 1;

SELECT * FROM `que_bank_option` ORDER BY RAND() LIMIT 1;

三、最终解决：

• 使用效率高的查询（注意分开查询MAX()和MIN()而不要写在一起）

备注： 本文主要是借助博客环境，同大家讨论异常问题解决的办法，欢迎大家评论，谢谢！