今天在做项目中遇到有一个需求是:随机取出广告表中的一条数据。一开始我想在程序获得所有广告ID后随机一个ID再去取对应的数据,但始终感觉这样不好。接下来我就在网上搜解决办法搜的一觉得不错方法如下:
SELECT * FROM BusinessAdvert ad WHERE ad.Deleted<>1 AND ad.Id=(SELECT r1.id FROM BusinessAdvert AS r1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM BusinessAdvert)) AS id)AS r2 WHERE ad.Deleted<>1 AND r1.id >= r2.id ORDER BY r1.id ASC LIMIT 1)
试了几次感觉还不错,效率也挺高的。好景不长,过了两天,测试过来跟我说她感觉有些广告出现的概率特别高,我过去看了下的确是这样。下来我仔细看了下上面的代码,才发现这条语句确实有缺陷。分析如下:
代码一:SELECT ROUND(RAND() * (SELECT MAX(id) FROM BusinessAdvert)) AS id 获取0-最大广告ID之间的随机数
代码二:SELECT r1.id FROM BusinessAdvert AS r1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM BusinessAdvert)) AS id)AS r2 WHERE ad.Deleted<>1 AND r1.id >= r2.id ORDER BY r1.id ASC LIMIT 1 获取大于等于取得随机数的第一条数据
从代码一和代码二中我们可以看出如果此批数据ID是连续的则是没哟任何问题的,但如果其中有被删除的数据那删除数据之后的数据获取到的