MySQL 5.5.62如何实现按日期重置的递增排序序号列

原语句问题说明 #

原SQL存在两个核心问题导致生成全局连续序号：

• 仅使用单个递增变量，没有加入按自然日分组的判断逻辑，无法实现跨天序号重置
• 日期过滤条件放在了UPDATE外层，子查询生成序号时扫描了全表数据，即使仅更新单日数据，获取到的也是全局维度的序号

可行实现方案 #

方案1：全表一次性生成所有历史日期的序号 #

适合需要一次性给历史所有数据按天补全pPrio值的场景，通过双用户变量判断日期边界，跨天自动重置序号：

UPDATE `dotable` t
JOIN (
    SELECT
        CASE 
            WHEN @prev_date = DATE(pDateHH) THEN @rn := @rn + 1
            ELSE @rn := 1 AND @prev_date := DATE(pDateHH)
        END AS pPrio,
        pDateHH,
        sID 
    FROM
        `dotable`,
        ( SELECT @rn := 0, @prev_date := NULL ) AS x 
    ORDER BY
        pDateHH ASC 
    ) q ON t.sID = q.sID 
SET t.pPrio = q.pPrio;

方案2：每日定时更新前一天的序号 #

适合日常调度仅处理前一天数据的场景，直接把日期过滤条件移到子查询内，子查询仅扫描单日数据，序号自然从1开始，性能更高：

UPDATE `dotable` t
JOIN (
    SELECT
        ( @rn := @rn + 1 ) AS pPrio,
        pDateHH,
        sID 
    FROM
        `dotable`,
        ( SELECT @rn := 0 ) AS x 
    WHERE DATE(pDateHH) = DATE_SUB(CURRENT_DATE,INTERVAL 1 DAY)
    ORDER BY
        pDateHH ASC 
    ) q ON t.sID = q.sID 
SET t.pPrio = q.pPrio;

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