MySQL多日期范围校验查询失效问题求助

我来帮你搞定这个新增学期的问题～首先咱们先理清楚核心需求和现有数据情况，再一步步推导正确的实现逻辑。

核心需求回顾 #

• 新增的学期日期范围必须完全落在2018-01-01 至 2018-06-30的学年区间内
• 新增学期的日期不能与term_id为1或2的学期存在任何重叠（包括部分重叠）

Term表结构与现有数据 #

先把现有Term表的结构和数据整理成清晰的表格：

关键逻辑：如何判断日期区间不重叠 #

两个日期区间[A_start, A_end]和[B_start, B_end]不重叠的条件是：

A_end < B_start 或者 A_start > B_end

反过来，如果不满足这个条件，就说明两个区间存在重叠。我们的SQL逻辑就是基于这个规则来做判断。

场景1：查询学年内所有可用的时间段 #

如果你想先找出学年内未被term1、2占用的所有可新增学期的时间段，可以用下面的SQL：

WITH excluded_intervals AS (
    -- 先把需要排除的term1、2的区间列出来
    SELECT start_date, end_date 
    FROM term 
    WHERE term_id IN (1, 2)
),
boundaries AS (
    -- 生成所有关键边界点：学年起止、排除区间的起止（加1天是为了衔接间隙）
    SELECT '2018-01-01' AS point UNION ALL
    SELECT end_date + INTERVAL 1 DAY FROM excluded_intervals UNION ALL
    SELECT start_date FROM excluded_intervals UNION ALL
    SELECT '2018-06-30' AS point
),
candidate_intervals AS (
    -- 把排序后的边界点两两配对成候选区间
    SELECT 
        point AS start_candidate,
        LEAD(point) OVER (ORDER BY point) AS end_candidate
    FROM boundaries
)
-- 筛选出有效的、非重叠的可用区间（至少1天）
SELECT 
    start_candidate,
    end_candidate - INTERVAL 1 DAY AS end_candidate
FROM candidate_intervals
WHERE 
    end_candidate IS NOT NULL
    AND start_candidate <= end_candidate - INTERVAL 1 DAY
    -- 确保候选区间不与排除区间重叠
    AND NOT EXISTS (
        SELECT 1 
        FROM excluded_intervals ei
        WHERE 
            candidate_intervals.start_candidate <= ei.end_date
            AND candidate_intervals.end_candidate - INTERVAL 1 DAY >= ei.start_date
    );

执行这个查询后，你会得到所有可以用来新增学期的时间段，比如2018-01-31到2018-06-30这个大区间就是可用的。

场景2：插入新学期时直接做验证 #

如果你已经确定了新学期的起止日期，想直接插入并确保符合要求，可以用带条件的INSERT语句（这里假设你要用变量@new_start和@new_end存储新的日期）：

INSERT INTO term (parent_id, start_date, end_date)
SELECT NULL, @new_start, @new_end
WHERE 
    -- 条件1：完全落在学年范围内
    @new_start >= '2018-01-01' 
    AND @new_end <= '2018-06-30'
    -- 条件2：不与term1、2的区间重叠
    AND NOT EXISTS (
        SELECT 1 
        FROM term 
        WHERE term_id IN (1, 2)
        AND (
            @new_start <= end_date 
            AND @new_end >= start_date
        )
    );

这个语句的逻辑很直接：只有当两个条件都满足时，才会执行插入操作。如果新日期不符合要求，插入会自动失败（不会新增任何记录）。

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