Spring Data JPA中MySQL原生查询报错：WorkBench正常但JPA报错

当使用Pageable参数时，Spring Data JPA会自动为原生查询追加分页相关的SQL语句（如MySQL的LIMIT和OFFSET）。你原查询外层的一对括号会导致JPA拼接分页语句后，整体SQL结构混乱，触发语法错误。

方案1：移除外层多余括号 #

修改Repository中的查询语句，去掉最外层的括号，让JPA能正确拼接分页逻辑：

@Query(value = "SELECT e2.* "
        + " FROM (select ph1.profile_id, max(ph1.last_updated_on) as last_updated_on2 "
        + "     FROM profile_history as ph1 "
        + "     GROUP BY ph1.profile_id ) as e1, profile_history as e2 "
        + " WHERE e2.profile_id = e1.profile_id and e2.last_updated_on = e1.last_updated_on2 ", nativeQuery = true)
Page<ProfileHistory> getAllProfileHistoryByLastestRow(Pageable paging);

方案2：改用显式JOIN写法（推荐） #

将隐式连接改为显式JOIN，让SQL结构更清晰，也能避免潜在的语法兼容问题：

@Query(value = "SELECT e2.* "
        + " FROM (SELECT ph1.profile_id, MAX(ph1.last_updated_on) AS last_updated_on2 "
        + "       FROM profile_history ph1 "
        + "       GROUP BY ph1.profile_id) e1 "
        + " JOIN profile_history e2 ON e2.profile_id = e1.profile_id AND e2.last_updated_on = e1.last_updated_on2 ", nativeQuery = true)
Page<ProfileHistory> getAllProfileHistoryByLastestRow(Pageable paging);

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