我得到hibernate SQL语法错误使用本地查询查询注释(但这种语法是在控制台工作)*编辑 &g

Question

首先，我使用(mysql 5.7, jdk1,8, spring boot)

@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();

控制台消息(hibernate错误消息)是

Hibernate: set @COUNT = 0; 
UPDATE faq f SET f.id= @COUNT:=@COUNT+1 WHERE f.id LIKE '%'; 
(error maessage)
java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE faq f SET f.id= @COUNT:=@COUNT+1 WHERE f.id LIKE '%'' at line 2

但是这个查询在mysql控制台

中工作得很好

set @COUNT = 0; UPDATE faq SET id = @COUNT:=@COUNT+1 WHERE f.id like '%';

请帮帮我……我一直在寻找和尝试许多解决方案，但没有工作

... UPDATE faq f SET f.id= @COUNT\:=@COUNT+1 WHERE f.id LIKE '%';" ...)
... UPDATE faq f SET f.id= @COUNT|=@COUNT+1 WHERE f.id LIKE '%';" ...)
... UPDATE faq f SET f.id= @COUNT\:=@COUNT+1 ;" ...)
... UPDATE faq f SET f.id= @COUNT\:=(@COUNT+1) ;" ...)
... (UPDATE faq f SET f.id= @COUNT\:=@COUNT+1 ;)" ...)

Answer 1

您试图一次执行2个查询。这行不通。可以通过在更新查询中初始化@count来组合这些查询，如下所示:

UPDATE faq f 
JOIN (SELECT @count := 0) cnt
SET f.id = @COUNT := @COUNT + 1 
WHERE f.id LIKE '%'

SQLFiddle演示