一、业务功能描述:最近一个项目中我做了登录和注册功能,开发了手机号和邮箱地址发送验证码的功能。由于也是第一次做发送验证码的功能,和其他网站一样,我的验证码发送时间也是至少间隔60s,但心里一直错误的认为必须在60秒内输入有效的验证码,所以按照这个思路,我查询有效验证码的SQL语句是:查当前时间60秒内的记录,如下:
# SQLyog 里面的执行语句 account_code用于存放手机号或者邮箱地址 send_time验证码发送时间
# 此sql查询了当前时间60秒内的记录
SELECT * FROM identify_code WHERE account_code = '135******3@qq.com' AND send_time BETWEEN NOW() - INTERVAL 60 SECOND AND SYSDATE();
二、虽然开发出来功能上没啥问题,但是在测试的时候同事提出来,查询60s内的记录,时间太短了,用户体验不好,最后建议查询5分钟内的记录,并根据j发送时间降序排列,取一条数据即可,优化SQL语句如下(两种方法):
# 使用between ~ and 划定前5分钟的区域,发送时间位于区间内即符合条件 时间降序取一条即拿到最新发送的验证码
SELECT * FROM identify_code WHERE account_code = '135******3@qq.com' AND send_time BETWEEN NOW() - INTERVAL 5 MINUTE AND NOW() ORDER BY send_time DESC LIMIT 1;
# 使用DATE_SUB()函数得到一个5分钟前的特定时间点,发送时间大于这个时间点即满足条件 时间降序取一条即拿到最新发送的验证码
SELECT * FROM identify_code WHERE account_code = '135******3@qq.com' AND send_time >= DATE_SUB(NOW(), INTERVAL 5 MINUTE) ORDER BY send_time DESC LIMIT 1;
评价:优化的SQL语句不仅把查询的时间放长,增加了用户体验度,而且和页面上发送验证码的时间间隔也不会有啥冲突,本人特地测试了一下CSDN发送验证码的有效时长,大概有效期也在5分钟左右呢!
三、问题经验分享:
A.该SQL语句会遇到到底使用 NOW() 还是使用 SYSDATE()获取当前时间呢?,建议使用 NOW() 获取当前时间,因为 NOW() 为该条SQL语句执行时间,执行过程中是固定不变的,符合我们的功能场景;SYSDATE()为系统当前时间,执行过程中时间是变化的。
B.如何小伙伴们不是使用的本地数据库,而是其他机器上的数据库的话,NOW() 和 SYSDATE() 获取的时间可能出现与本地的电脑上的时间不一致问题,就可能产生SQL语句失效的错觉呢!换本地库测试即可!