MySQL：获取连续记录的数量(日期)

您忘记了为每行设置@prevDate，并且返回的是最大条纹而不是最新条纹.

如果您使用的是MySQL&>=8.0，则可以使用window functions，而不是deprecated setting of user variables in expressions:

WITH streaks AS (
    SELECT
        habit_id,
        DATE(created_at) AS dt,
        DATE(created_at) - INTERVAL DENSE_RANK() OVER (PARTITION BY habit_id ORDER BY DATE(created_at) ASC) DAY AS grp
    FROM habit_progress_logs
    WHERE habit_id = 4
),
streaks2 AS (
    SELECT
        habit_id,
        grp,
        COUNT(DISTINCT dt) AS length,
        ROW_NUMBER() OVER (PARTITION BY habit_id ORDER BY grp DESC) AS rn
    FROM streaks
    GROUP BY habit_id, grp
)
SELECT habit_id, length AS currentStreak
FROM streaks2
WHERE rn = 1;

前CTE个给出了日期序列分组，使用DENSE_RANK()(添加rnk用于说明):

第二个CTE进行分组，并添加ROW_NUMBER():

如果您在使用MySQL<；8.0时遇到困难:

SELECT habit_id, COUNT(*) AS currentStreak
FROM (
  SELECT
    habit_id,
    dt,
    @grp := IF(@prevHabitId = habit_id AND dt = @prevDate + INTERVAL 1 DAY, @grp, @grp + 1) AS grp,
    @prevDate := dt,
    @prevHabitId := habit_id
  FROM (
    SELECT habit_id, DATE(created_at) AS dt
    FROM habit_progress_logs
    JOIN (SELECT @prevHabitId := NULL, @prevDate := NULL, @grp := 0) i
    WHERE habit_id = 4
    GROUP BY habit_id, dt
    ORDER BY habit_id, dt
  ) s
) t1
GROUP BY habit_id, grp
ORDER BY habit_id, grp DESC
LIMIT 1;

这是一张db<>fiddle