如何限制SQL LIKE匹配相同通配符编号，实现正确日期范围查询

问题根源 #

原查询未限制start与end记录的编号匹配，导致不同组的日期被错误关联（如事件2的date_1_start和date_0_end配对），从而返回不符合条件的事件。

修正方案 #

通过提取meta_key中的编号，强制关联同一事件下编号相同的起止日期记录，再进行区间重叠判断。

修正后的SQL代码 #

SELECT DISTINCT events.id
FROM events
INNER JOIN events_meta AS start_meta 
    ON events.id = start_meta.event_id 
    AND start_meta.meta_key LIKE 'date_%_start'
INNER JOIN events_meta AS end_meta 
    ON events.id = end_meta.event_id 
    AND end_meta.meta_key LIKE 'date_%_end'
    -- 提取并匹配编号，确保起止日期属于同一组
    AND SUBSTRING_INDEX(SUBSTRING_INDEX(start_meta.meta_key, '_', 2), '_', -1) 
        = SUBSTRING_INDEX(SUBSTRING_INDEX(end_meta.meta_key, '_', 2), '_', -1)
WHERE 
    -- 判断区间是否与查询范围重叠
    CAST(start_meta.meta_value AS DATE) <= '2022-04-03'
    AND CAST(end_meta.meta_value AS DATE) >= '2022-04-01';

关键细节说明 #

1. 编号匹配：利用SUBSTRING_INDEX函数从meta_key中提取中间的编号部分（如date_0_start中的0），保证关联的起止记录属于同一日期区间组。
2. 区间重叠判断：将日期条件调整为事件区间与查询范围存在重叠的逻辑，覆盖以下场景：
   • 事件区间完全包含查询范围
   • 事件区间部分覆盖查询范围
   • 查询范围完全包含事件区间
3. 去重处理：添加DISTINCT避免同一事件因多个符合条件的区间被重复返回。

验证示例 #

针对事件2的两个日期区间：

• date_0_start=2022-03-30，date_0_end=2022-03-31：该区间结束早于查询起始日（2022-04-01），不符合条件。
• date_1_start=2022-04-04，date_1_end=2022-04-05：该区间起始晚于查询结束日（2022-04-03），不符合条件。
  因此事件2不会被错误返回，符合预期。

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