Spring Data JPA中tags数组为空时SQL语法错误问题排查

当tags参数为null时，Spring Data JPA会直接将SQL中的:tags替换成null，导致生成t.tag IN null这种MySQL无法解析的非法语法，这就是触发SQLSyntaxErrorException的根源。

方案1：用SpEL表达式动态跳过IN子句 #

修改原生SQL查询，通过Spring EL表达式判断tags是否为null，动态生成条件，同时调整关联方式和分组逻辑，避免额外问题：

@Query(value = "SELECT s.id, s.title, s.teacher_id, s.short_description, s.time_exam_end, s.time_start FROM subjects s " +
        "LEFT JOIN tags_subject t ON s.id = t.subject_id " +
        "WHERE (s.time_exam_end > UNIX_TIMESTAMP() * 1000) " +
        "AND (:#{#tags == null} OR t.tag IN :tags) " +
        "AND ((:has_exam IS NULL) OR (:has_exam = true AND s.exam <> '') OR (:has_exam = false AND s.exam = '')) " +
        "GROUP BY s.id, s.title, s.teacher_id, s.short_description, s.time_exam_end, s.time_start " +
        "HAVING :tag_count = 0 OR COUNT(DISTINCT t.tag) = :tag_count " +
        "LIMIT 10 OFFSET :pos ", nativeQuery = true)
List<SubjectAnnounceDB> findAccessAnnounceSubject(int pos, String[] tags, Boolean has_exam, int tag_count);

default List<SubjectAnnounceDB> findAccessAnnounceSubject(int pos, String[] tags, Boolean has_exam) {
    return findAccessAnnounceSubject(pos, tags, has_exam, tags == null ? 0 : tags.length);
}

关键修改点： #

• 替换JOIN为LEFT JOIN：确保无标签的科目在tags为null时也能被查询到
• 新增SpEL表达式:#{#tags == null}：当tags为null时，该条件直接为true，不会执行后续的IN :tags子句，彻底避免语法错误
• 修正GROUP BY：移除t.tag，按科目自身字段分组，避免同一科目因多标签被重复统计
• 使用COUNT(DISTINCT t.tag)：确保统计的是唯一标签数量，避免重复标签干扰计数逻辑

方案2：用COALESCE生成合法空集合 #

在IN子句中用COALESCE函数，当tags为null时替换成一个空结果集，保证语法合法性：

@Query(value = "SELECT s.id, s.title, s.teacher_id, s.short_description, s.time_exam_end, s.time_start FROM subjects s " +
        "JOIN tags_subject t ON s.id = t.subject_id " +
        "WHERE (s.time_exam_end > UNIX_TIMESTAMP() * 1000) " +
        "AND (:tags IS NULL OR t.tag IN COALESCE(:tags, (SELECT tag FROM tags_subject WHERE 1=0))) " +
        "AND ((:has_exam IS NULL) OR (:has_exam = true AND s.exam <> '') OR (:has_exam = false AND s.exam = '')) " +
        "GROUP BY t.tag, s.id, s.title, s.teacher_id, s.short_description, s.time_exam_end, s.time_start " +
        "HAVING :tag_count = 0 OR COUNT(t.tag) = :tag_count " +
        "LIMIT 10 OFFSET :pos ", nativeQuery = true)
List<SubjectAnnounceDB> findAccessAnnounceSubject(int pos, String[] tags, Boolean has_exam, int tag_count);

说明： #

• (SELECT tag FROM tags_subject WHERE 1=0)会返回空结果集，当tags为null时，COALESCE会用这个空集合替代，此时t.tag IN ()是MySQL 8.0+支持的合法语法；低版本MySQL可替换为(SELECT NULL LIMIT 0)
• 若需要包含无标签的科目，仍需将JOIN改为LEFT JOIN

方案3：使用动态查询（Specification） #

如果参数组合复杂，推荐用Spring Data JPA的动态查询能力，根据参数是否为null自动跳过对应条件，彻底避免硬编码SQL的语法问题：

public interface SubjectAnnounceRepository extends JpaRepository<SubjectAnnounceDB, Long>, JpaSpecificationExecutor<SubjectAnnounceDB> {

    default List<SubjectAnnounceDB> findAccessAnnounceSubject(int pos, String[] tags, Boolean has_exam) {
        Specification<SubjectAnnounceDB> spec = (root, query, cb) -> {
            List<Predicate> predicates = new ArrayList<>();

            // 时间过滤条件
            predicates.add(cb.greaterThan(root.get("timeExamEnd"), System.currentTimeMillis()));

            // 标签过滤与分组统计
            if (tags != null && tags.length > 0) {
                Join<SubjectAnnounceDB, TagsSubject> tagJoin = root.join("tagsSubjects", JoinType.INNER);
                predicates.add(tagJoin.get("tag").in(tags));
                query.groupBy(root.get("id"));
                query.having(cb.equal(cb.count(tagJoin.get("tag")), tags.length));
            }

            // 考试存在性过滤
            if (has_exam != null) {
                Predicate examPredicate = has_exam ? 
                    cb.notEqual(root.get("exam"), "") : 
                    cb.equal(root.get("exam"), "");
                predicates.add(examPredicate);
            }

            // 分页设置
            query.offset(pos).limit(10);

            return cb.and(predicates.toArray(new Predicate[0]));
        };

        return findAll(spec);
    }
}

优势： #

• 类型安全，避免原生SQL拼写错误
• 完全动态构建查询，参数为null时自动跳过对应条件，无语法风险
• 条件组合更灵活，便于后续扩展

内容的提问来源于stack exchange，提问作者Максим Маньковский