带有MySQL和JDBC的带有多个语句的触发器上的语法异常 [英] Syntax exception on trigger with multiple statements with MySQL and JDBC

问题描述

我正在尝试创建一个触发器，该触发器在InnoDB中在MySQL 5.5.28中执行多项操作.

I am trying to create a trigger that performs multiple operations in MySQL 5.5.28 with InnoDB.

我有两个表"test"和"test_watcher":在触发器的帮助下，对第一个表的更改记录在watcher表中.最后一个触发器需要在DELETE上执行2个操作，它在MySQL Workbench(带有DELIMITER)中工作，但是如果我使用JDBC创建它，则不会.

I have two tables, "test" and "test_watcher": changes to the first are recorded in the watcher table with the help of triggers. The last trigger needs to perform 2 operations on DELETE, it works in MySQL Workbench (with DELIMITER) but doesn't if I create it with JDBC.

CREATE TRIGGER `AD_test_FER` AFTER DELETE
  ON `test`
  FOR EACH ROW
      BEGIN
        -- if it's been inserted, modified and deleted but never synced, 
        -- the revision is NULL: no one needs to know about it
        DELETE FROM test_watcher WHERE pk = OLD.id AND revision IS NULL;

        -- if it has been synced already, we just update the flag
        UPDATE test_watcher SET flag = -1 WHERE pk = OLD.id;
      END;

我一直得到com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax.

我知道它可以在Workbench中与DELIMITER $$一起使用，但是JDBC不支持它.

I know it works with DELIMITER $$ in Workbench, but JDBC doesn't support it.

我已经在PostgreSQL中实现了它，并在需要时发布了代码.

I've achieved it in PostgreSQL and would post the code if necessary.

推荐答案

此行为可能是由

This behavior might be caused by the connection property allowMultiQueries=true. My guess is this property will make MySQL break up queries on the ; as a query separator and then execute those as separate queries, essentially breaking your trigger creation code.

正如您在-现在已删除-回答中所说，添加allowMultiQueries=true实际上解决了问题(与我的预期相反)，该问题实际上可能是查询中的最后一个;.因此，要检查的另一件事是，通过删除脚本中的最后一个;(在END;中)(而不使用allowMultiQueries=true)，问题是否消失了.某些数据库认为;在语句末尾无效(因为它实际上是分隔语句的分隔符).

As you said in a - now deleted - answer that adding allowMultiQueries=true actually solved the problem (contrary to my expectiation), the problem might actually be the last ; in your query. So another thing to check is if the problem goes away by removing the last ; (in END;) in your script (and not using allowMultiQueries=true). Some database don't consider ; to be valid at the end of a statement (as it is actually a delimiter to separate statements).

(此答案基于我上面的评论)

(this answer is based on my comment above)

这篇关于带有MySQL和JDBC的带有多个语句的触发器上的语法异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！