分类
MySql SQL 笔记

从SQL查询结果随机取一条数据

最近工作中会涉及复杂的SQL语句,还是挺有意思的。以前想写没有应用场景,现在正好可以多锻炼锻炼。我觉得在实际开发中,程序代码并不是最难的,最难的还是SQL。SQL逻辑性最强,而且还要很清楚表设计,业务场景。特别是复杂的业条场景下提炼数据,要考虑到功能,性能,是否会锁表。
需求:有一张表保存诺干条广告信息。当APP启动时,根据登录用户所在的区域,随机取出一条广告返回,作为启动页。

数据库:MySQL
刚开始,我的做法是:

select floor(max(startPageId) * rand()) from ghome.ghome_startpage_info where 1=1 and status!='00';

startPageId是自动增长的Integer型
其他where条件不贴出来了。这句意思是,查询符合条件的数据,根据 max 函数找出结果最大的,乘以 rand 函数随机生成的一个小于1的数,再用 floor 去掉小数位。得出一个 randId。

再执行

select startPageId,startPageName,validStartTime,validEndTime,pageImageUrl,pageLinkUrl,allowBreak,waitTime
    from ghome.ghome_startpage_info where 1=1 and status!='00' and startPageId >= randId limit 1;

根据查询条件,startPageId大于等于刚刚得出来的随机id。
这个语句没有错,性能也挺好。但是有一个问题,当数据有删除,startPageId就不连续,或者条件筛选后最小startPageId比较大时,容易得到的一个范围,很大可能性取的是同一条。
对于这种情况,需要自己根据查询结果重新设置id序号,然后再取结果。优化如下:

select floor(count(startPageId) * rand()) from ghome.ghome_startpage_info where 1=1 and status!='00';

使用 count 函数查出结果数,乘以 rand 函数随机生成的一个小于1的数,再用 floor 去掉小数位。得出一个 randId。
然后从结果表里取数据。

该图是子语句的结果信息。可能是MySQL不允许直接用生成的id作条件。所以把查询结果作为外面select语句的结果表。

select * from (select (@i:=@i+1) as id, startPageId,startPageName,validStartTime,validEndTime,pageImageUrl,pageLinkUrl,allowBreak,waitTime
 from ghome.ghome_startpage_info,(select @i:=0) as it where 1=1 and status='02') as t where id => randId limit 1;

执行成功,取出结果数据返回。
PS:第一种方法,是大多数博主推荐的做法。但实际应用时,出现了刚刚这个问题。所以程序开发一定要仔细考虑各种情况,亲自动手验证。

发表评论

电子邮件地址不会被公开。