插入回调预约记录前如何校验并比较datetime实现4小时防重复

业务规则说明 #

• 同一客户手机号发起的回调预约请求，仅允许在4小时时效窗口内生成1条有效记录
• 插入新记录前的校验逻辑：比对数据库中同手机号的预约创建时间，若存在4小时时效内的有效预约，直接拦截本次请求不重复插入；无匹配有效预约时，正常创建新的回调预约记录

基础约定 #

• 预约表AppointmentsList使用datetime类型字段存储预约创建时间，存储格式示例为2021-03-14 23:50:34.263

现有实现缺陷 #

当前编写的插入逻辑仅校验手机号是否存在历史记录，未加入时间范围比对维度，只要手机号有过任意历史预约就会拦截新请求，完全不满足4小时时效的业务要求，原有代码如下：

INSERT INTO AppointmentsList(mobileNo)
SELECT 'mobileNo'
WHERE NOT EXISTS (
    SELECT mobileNo
    FROM AppointmentsList
    WHERE mobileNo = 'mobileNo'
)

优化后可落地的实现代码 #

INSERT INTO AppointmentsList(mobileNo, appointment_create_time)
SELECT '实际传入的客户手机号', NOW() -- 生产环境替换为真实入参值
WHERE NOT EXISTS (
    SELECT 1
    FROM AppointmentsList
    WHERE
        -- 匹配同一客户手机号
        mobileNo = '实际传入的客户手机号'
        -- 校验是否存在4小时时效窗口内的有效预约
        AND appointment_create_time >= DATE_SUB(NOW(), INTERVAL 4 HOUR)
)

落地注意事项 #

• 时间窗口的判断逻辑为：只要存在同手机号的预约记录，其创建时间在「当前时间向前推4小时」的区间内，就判定为有效预约，拦截本次插入请求
• 若历史预约的创建时间距离当前时间超过4小时，校验逻辑会自动放行新的预约创建请求，不会产生过度拦截
• 高并发场景下，可针对mobileNo、appointment_create_time字段建立联合索引，减少NOT EXISTS子查询的全表扫描开销

内容的提问来源于stack exchange，提问作者Hussein ALSHAMIRI