如何在MySQL 5.6中从同settingid最近记录补全status空值？

需求说明 #

日志表logs的status列存在NULL值，需要按以下规则更新这些NULL值：

• 对于每条status为NULL的记录，取相同settingid下、status非NULL且mtime小于等于当前记录mtime的最近一条记录的status值
• 若不存在符合条件的记录，默认设置为'INACTIVE'

示例数据 #

原MySQL 8.x可用语句（不适配5.6） #

原语句使用了MySQL 8.0引入的CTE（WITH子句），但MySQL 5.6不支持该特性，因此无法运行：

WITH cte AS (
     SELECT
           logid,
           status,
           mtime,
           CASE WHEN status IS NULL THEN
               IFNULL(
                   (
                       SELECT status
                       FROM logs
                       WHERE
                           settingid = t.settingid
                           AND status IS NOT NULL
                           AND mtime <= t.mtime
                           ORDER BY mtime DESC
                           LIMIT 1
                   ),
                   'INACTIVE'
               ) ELSE status END AS new_status
       FROM
           logs t
       ORDER BY mtime
   )
   UPDATE logs t LEFT JOIN cte USING (logid)
   SET t.status = cte.new_status
   WHERE t.status IS NULL;

适配MySQL 5.6的解决方案 #

由于MySQL 5.6不支持CTE，我们可以将CTE的逻辑转换为子查询嵌入到UPDATE语句中，以下两种方案均满足需求：

方案一：使用JOIN子查询替代CTE #

UPDATE logs t
LEFT JOIN (
    SELECT
        logid,
        CASE WHEN status IS NULL THEN
            IFNULL(
                (
                    SELECT status
                    FROM logs l
                    WHERE l.settingid = t.settingid
                        AND l.status IS NOT NULL
                        AND l.mtime <= t.mtime
                    ORDER BY l.mtime DESC
                    LIMIT 1
                ),
                'INACTIVE'
            )
        ELSE status END AS new_status
    FROM logs t
) cte ON t.logid = cte.logid
SET t.status = cte.new_status
WHERE t.status IS NULL;

方案二：直接在SET子句中使用相关子查询（更简洁） #

UPDATE logs t
SET t.status = IFNULL(
    (
        SELECT status
        FROM logs l
        WHERE l.settingid = t.settingid
            AND l.status IS NOT NULL
            AND l.mtime <= t.mtime
        ORDER BY l.mtime DESC
        LIMIT 1
    ),
    'INACTIVE'
)
WHERE t.status IS NULL;

方案说明 #

两种方案的逻辑与原语句完全一致：

• 针对每条status为NULL的记录，通过相关子查询匹配同settingid下最近的非NULL状态记录
• 若未找到匹配记录，则用'INACTIVE'作为默认值填充

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