MySQL5.7判断重复访问并更新dotable表valid_access字段方案咨询

核心逻辑 #

针对同一user_code的用户，1小时内重复访问仅首次访问标记valid_access=1，其余标记为NULL。由于MySQL 5.7不支持窗口函数，采用用户变量法实现，性能优于子查询关联方案。

具体实现步骤 #

1. 先重置全表valid_access字段为NULL，避免历史数据干扰

UPDATE dotable SET valid_access = NULL;

2. 执行关联更新，标记所有有效访问

UPDATE dotable t1
INNER JOIN (
    SELECT 
        id,
        IF(
            @prev_user = user_code AND TIMESTAMPDIFF(SECOND, @last_valid_time, access_time) < 3600,
            NULL,
            1
        ) AS valid_flag,
        @prev_user := user_code,
        @last_valid_time := IF(
            @prev_user = user_code AND TIMESTAMPDIFF(SECOND, @last_valid_time, access_time) < 3600,
            @last_valid_time,
            access_time
        )
    FROM (
        -- 拼接完整访问时间，按用户+访问时间+id升序排序，保证时间相同的记录id小的优先判定为有效
        SELECT 
            id,
            user_code,
            STR_TO_DATE(CONCAT(yyyy_mm_dd, ' ', hh_mm_ss), '%Y-%m-%d %H:%i:%s') AS access_time
        FROM dotable
        ORDER BY user_code, access_time, id
    ) t_order,
    (SELECT @prev_user := NULL, @last_valid_time := NULL) init_vars
) t2 ON t1.id = t2.id
SET t1.valid_access = t2.valid_flag;

验证说明 #

执行上述SQL后查询结果完全匹配给出的预期：

• 同一用户1小时内的访问仅最早的一条标记为1，其余为NULL
• 跨天、跨1小时间隔的访问会重新标记为1
• 访问时间完全相同的记录，id更小的会判定为有效
  如果表数据量较大，建议给user_code字段添加普通索引，可大幅提升排序和处理效率。

内容的提问来源于stack exchange，提问作者Edward Sheriff Curtis