@Query注解原生SQL报Hibernate语法错误但MySQL控制台可正常执行如何解决

问题根因 #

报错由两个核心原因导致：

1. MySQL JDBC 驱动默认禁止单次请求执行多条分号分隔的SQL语句，当前SQL包含set、update两条语句，默认场景下会被拦截报语法错误
2. Spring Data JPA 中:符号默认会被识别为命名参数占位符，SQL中的:=赋值符号会被JPA错误解析，即使增加转义符也可能因版本处理逻辑差异失效

可行解决方案 #

方案1：合并为单条SQL执行（最推荐，无需修改连接配置，无安全风险） #

把变量初始化逻辑合并到UPDATE语句中，不需要分号分隔多条语句，写法如下：

@Transactional
@Modifying
@Query(value= "UPDATE faq f CROSS JOIN (SELECT @COUNT := 0) AS init " +
        "SET f.id = @COUNT := @COUNT + 1 WHERE f.id LIKE '%'", nativeQuery = true)
void update();

这种写法不需要开启多语句执行权限，也不需要处理:=的转义问题，直接就能正常执行。

方案2：开启JDBC多语句执行权限+修正转义 #

如果坚持要用多条语句的写法，先修改数据库连接配置，在JDBC URL后添加参数allowMultiQueries=true，示例配置（application.yml）：

spring:
  datasource:
    url: jdbc:mysql://你的数据库地址/库名?allowMultiQueries=true&其他原有参数

然后调整:=的转义写法，部分JPA版本下可以用\\:=转义，要是转义不生效也可以用MySQL兼容的注释写法替换:=为/*=*/，示例：

@Transactional
@Modifying
@Query(value= "set @COUNT = 0; \n"+
        "UPDATE faq f SET f.id= @COUNT/*=*/@COUNT+1 WHERE f.id LIKE '%'", nativeQuery = true)
void update();

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