MySQL查询列表中任意月份落在季节起止区间内的记录实现

假设存在名为Seasons的数据表，核心字段如下：

需求为：针对给定的月份列表，返回所有满足**列表中至少存在1个月份符合start_month <= month <= end_month**条件的Seasons记录。

现有基于JDBC编写的原生查询基础代码如下，仅缺失WHERE子句实现：

@Repository
public class SeasonsRepositoryImpl implements SeasonsRepositoryCustom {

    @PersistenceContext
    private EntityManager em;

    @Override
    public List<SeasonsProjection> findByMonths(List<Integer> months) {
        String query = "select * " +
                       "from seasons as s " + // 注：原代码此处缺空格，拼接后会出现swhere语法错误，已补
                       "where ...."

        try {
            return  em.createNativeQuery(query)
                    .setParameter("months", months)
                    .unwrap(org.hibernate.query.NativeQuery.class)
                    .setResultTransformer(Transformers.aliasToBean(SeasonsProjection.class))
                    .getResultList();
        } catch (Exception e) {
            log.error("Exception with an exception message: {}", e.getMessage());
            throw e;
        }
    }

}

当前实现卡点：

• 尝试使用ANY运算符，但了解到ANY仅支持操作表数据，无法直接处理传入的列表参数
• 考虑过编写子查询将传入的列表转换为表结构，但不确定MySQL是否支持该实现，未找到相关官方说明

方案1：MySQL 8.0.4+ 推荐方案（JSON_TABLE实现） #

MySQL 8.0.4及以上版本内置JSON_TABLE函数，可以将JSON数组格式的入参直接转换为可查询的临时表，配合EXISTS判断即可实现需求，写法规范且性能优秀。

实现步骤 #

1. 在Java层将传入的月份列表转换为JSON数组字符串（比如[2,3,5]），推荐使用Jackson等JSON工具类转换，避免SQL注入风险
2. WHERE子句直接通过EXISTS匹配是否存在落在区间内的月份即可

WHERE子句示例 #

WHERE EXISTS (
    SELECT 1
    FROM JSON_TABLE(
        :monthsJson,
        '$[*]' COLUMNS (m INT PATH '$')
    ) AS month_list
    WHERE month_list.m BETWEEN s.start_month AND s.end_month
)

对应参数设置代码 #

// 提前初始化Jackson的ObjectMapper即可
ObjectMapper objectMapper = new ObjectMapper();
String monthsJson = objectMapper.writeValueAsString(months);
// 后续将原setParameter("months", months)替换为setParameter("monthsJson", monthsJson)

方案2：全版本兼容方案（动态拼接OR条件） #

如果使用MySQL 5.x等不支持JSON_TABLE的旧版本，可以直接在Java层动态拼接OR条件，完全兼容所有MySQL版本，只要start_month、end_month字段建立了索引，性能表现也很好。

实现逻辑 #

遍历传入的月份列表，为每个月份生成一个区间匹配条件，用OR连接即可。比如传入月份[2,3,5]，最终生成的WHERE子句为：

WHERE (s.start_month <= 2 AND s.end_month >= 2)
   OR (s.start_month <= 3 AND s.end_month >= 3)
   OR (s.start_month <= 5 AND s.end_month >= 5)

注意事项 #

• 不要直接拼接月份数值到SQL中，要使用?占位符对应设置参数，避免SQL注入
• 拼接时注意括号优先级，避免和其他查询条件组合时出现逻辑错误

注意：不要使用「传入月份列表最大值 >= start_month 且 列表最小值 <= end_month」的判断逻辑，该逻辑仅适用于传入月份是连续区间的场景，对于离散月份列表会出现误匹配（比如传入[1,3,7]、季节区间为4-6时，该逻辑会误判为匹配，但实际列表中没有月份落在4-6区间内）。

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