Spring Boot查询返回多结果时触发MariaDB SQL语法错误

错误根源 #

从报错信息和代码来看，带分页时SQL语法错误的核心原因有三个：

1. 参数名不匹配：方法参数里的@Param("studenttId")多了一个t，但SQL中引用的是:studentId，参数绑定失败后会生成包含空括号的无效SQL，这就是报错里near ')'的直接原因。
2. SELECT * 与 GROUP BY 的冲突：多表JOIN后用SELECT *会返回重复字段（比如student.oid、classes.oid、grade.oid），搭配GROUP BY student_oid时，MariaDB的SQL模式会判定这种非聚合字段的查询不合法，分页时Spring Data自动拼接LIMIT/OFFSET后，语法校验会触发错误。
3. 自动生成的COUNT查询异常：Spring Data JPA处理原生SQL分页时会自动生成COUNT语句，但多表JOIN+GROUP BY的场景下，自动生成的COUNT逻辑会出错，进一步加剧语法问题。

分步修复 #

1. 修正参数名 #

把方法里的@Param("studenttId")改为@Param("studentId")，和SQL中的:studentId完全匹配：

@Param("studentId") String patientId,

2. 替换SELECT *为明确字段 #

多表查询时必须指定需要的字段，避免重复字段冲突，同时确保GROUP BY的字段是student表的主键（用student.oid比student_oid更清晰）：

SELECT student.oid, student.first_name, student.last_name, student.birth_date, 
       student.student_id, student.teacher_oid,
       classes.school_oid AS class_school_oid, grade.school_oid AS grade_school_oid
FROM student 
LEFT JOIN classes ON student.oid = classes.student_oid 
LEFT JOIN grade ON classes.oid = grade.classes_oid 
-- WHERE条件保持不变
GROUP BY student.oid

3. 手动指定COUNT查询 #

为分页提供正确的总记录数查询，避免Spring Data自动生成错误的COUNT语句：

@Query(
    value = "SELECT student.oid, student.first_name, student.last_name, student.birth_date, " +
            "student.student_id, student.teacher_oid, " +
            "classes.school_oid AS class_school_oid, grade.school_oid AS grade_school_oid " +
            "FROM student " +
            " LEFT JOIN classes ON student.oid = classes.student_oid " +
            " LEFT JOIN grade ON classes.oid = grade.classes_oid " +
            " WHERE (:firstName IS NULL OR MATCH (first_name) AGAINST (:firstName IN BOOLEAN MODE)) AND " +
            "(:lastName IS NULL OR MATCH (last_name) AGAINST (:lastName IN BOOLEAN MODE)) AND  " +
            "(:birthDate IS NULL OR birth_date = :birthDate) AND " +
            "(:studentId IS NULL OR MATCH (student_id) AGAINST (:studentId IN BOOLEAN MODE)) AND " +
            "(:teacherOid IS NULL OR teacher_oid = :teacherOid) AND " +
            "(:schoolOid IS NULL OR classes.school_oid = :schoolOid) AND " +
            "(:schoolOid IS NULL OR grade.school_oid = :schoolOid) " +
            " GROUP BY student.oid ",
    countQuery = "SELECT COUNT(DISTINCT student.oid) " +
            "FROM student " +
            " LEFT JOIN classes ON student.oid = classes.student_oid " +
            " LEFT JOIN grade ON classes.oid = grade.classes_oid " +
            " WHERE (:firstName IS NULL OR MATCH (first_name) AGAINST (:firstName IN BOOLEAN MODE)) AND " +
            "(:lastName IS NULL OR MATCH (last_name) AGAINST (:lastName IN BOOLEAN MODE)) AND  " +
            "(:birthDate IS NULL OR birth_date = :birthDate) AND " +
            "(:studentId IS NULL OR MATCH (student_id) AGAINST (:studentId IN BOOLEAN MODE)) AND " +
            "(:teacherOid IS NULL OR teacher_oid = :teacherOid) AND " +
            "(:schoolOid IS NULL OR classes.school_oid = :schoolOid) AND " +
            "(:schoolOid IS NULL OR grade.school_oid = :schoolOid)",
    nativeQuery = true
)
Page<Patient> getPatientsByFirstNameAndLastNameAndIssuerAndFacilityMaria(
    @Param("firstName") String firstName,
    @Param("lastName") String lastName,
    @Param("birthDate") Date birthDate,
    @Param("studentId") String patientId,
    @Param("teacherOid") Long issuerOid,
    @Param("schoolOid") Long schoolOid,
    Pageable page
);

4. 验证全文索引 #

确保student.first_name、student.last_name、student.student_id已经创建全文索引，否则MATCH AGAINST会触发运行时错误：

CREATE FULLTEXT INDEX idx_student_fulltext ON student(first_name, last_name, student_id);

测试步骤 #

1. 先不带分页参数调用接口，确认数据返回正常
2. 添加分页参数（如PageRequest.of(0,10)），检查是否正常分页
3. 开启SQL日志（在application.properties中添加logging.level.org.hibernate.SQL=DEBUG），查看生成的最终SQL是否合法

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