MySQL 5.5.46版本_t_user表用户登录有效性更新需求咨询

需求说明 #

在_t_user表中，存储用户的登录（IN）和登出（OUT）记录。当某条登出记录与对应的最近登录记录的时间差小于3分钟时，需要将这两条记录的Effective字段更新为'N'。

解决方案 #

由于MySQL 5.5不支持窗口函数，我们可以通过关联查询+分组的方式匹配符合条件的记录对，再执行更新操作：

UPDATE _t_user t1
JOIN (
    -- 筛选出时间差小于3分钟的IN-OUT记录对
    SELECT 
        o.IP_ADDRESS,
        o.User_name,
        o.Date_Time_from_system AS out_time,
        MAX(i.Date_Time_from_system) AS in_time
    FROM _t_user o
    INNER JOIN _t_user i 
        ON o.IP_ADDRESS = i.IP_ADDRESS 
        AND o.User_name = i.User_name 
        AND i.Event = 'IN' 
        AND o.Event = 'OUT' 
        AND i.Date_Time_from_system < o.Date_Time_from_system
    GROUP BY o.IP_ADDRESS, o.User_name, o.Date_Time_from_system
    HAVING TIMESTAMPDIFF(MINUTE, MAX(i.Date_Time_from_system), o.Date_Time_from_system) < 3
) matched_records 
    ON (t1.Event = 'IN' 
        AND t1.IP_ADDRESS = matched_records.IP_ADDRESS 
        AND t1.User_name = matched_records.User_name 
        AND t1.Date_Time_from_system = matched_records.in_time)
    OR (t1.Event = 'OUT' 
        AND t1.IP_ADDRESS = matched_records.IP_ADDRESS 
        AND t1.User_name = matched_records.User_name 
        AND t1.Date_Time_from_system = matched_records.out_time)
SET t1.Effective = 'N';

逻辑说明 #

1. 子查询部分：
   • 关联OUT记录和IN记录，匹配同一IP、同一用户且登录时间早于登出时间的记录
   • 通过MAX(i.Date_Time_from_system)找到每条OUT对应的最近一次登录记录
   • 用TIMESTAMPDIFF计算时间差，筛选出小于3分钟的记录对
2. 外层更新：
   • 通过JOIN匹配到对应的IN和OUT记录，统一将它们的Effective字段设为'N'

验证结果 #

执行上述SQL后，示例中时间差为1分02秒的登录（2022-08-11 12:21:04）和登出（2022-08-11 12:22:06）记录的Effective字段会被更新为'N'，与需求示例一致。

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