MySQL分隔符语句错误 [英] MySQL delimiter statement error

问题描述

我正在尝试在mysql中运行以下脚本:

I am trying to run the following script in mysql :

DELIMITER $$$
DROP TRIGGER IF EXISTS invoice_line_insert
$$$
CREATE TRIGGER invoice_line_insert AFTER INSERT
ON invoice_line FOR EACH ROW
BEGIN
    IF NEW.type = "DELIVERY" THEN
        UPDATE invoice
        SET invoice.etdelivery_amount = invoice.etdelivery_amount + NEW.amount
        WHERE invoice.id_invoice = NEW.invoice_parent_id_invoice;
    ELSE
        UPDATE invoice
        SET invoice.etexpense_amount = invoice.etexpense_amount + NEW.amount
        WHERE invoice.id_invoice = NEW.invoice_parent_id_invoice;
    END IF;
    UPDATE invoice
    SET invoice.vatamount = (NEW.amount * ((
                SELECT vat.rate
                FROM vat
                WHERE vat.id_vat = NEW.vat_id_vat
    ) / 100)) + invoice.vatamount
    WHERE invoice.id_invoice = NEW.invoice_parent_id_invoice;

    UPDATE invoice
    SET invoice.itamount = invoice.vatamount +
            invoice.etdelivery_amount +
            invoice.etexpense_amount
    WHERE invoice.id_invoice = NEW.invoice_parent_id_invoice;
END
$$$

当我在mySql Workbench中运行它时，它工作正常，但是在play 2中以自发方式运行它(在名为2.sql的文件中)时，出现以下错误:

When I run it in mySql Workbench, it is working fine, but when play 2 run it authomatically (in a file called 2.sql) I get the following error :

您的SQL语法有错误；请在第1行[ERROR:1064，SQLSTATE:42000]中检查与MySQL服务器版本相对应的手册，以找到在'DELIMITER $$$ DROP TRIGGER IF EXISTS invoice_line_insert $$$ CREATE TRIGGER invo'附近使用的正确语法，同时尝试运行以下SQL脚本:

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 'DELIMITER $$$ DROP TRIGGER IF EXISTS invoice_line_insert $$$ CREATE TRIGGER invo' at line 1 [ERROR:1064, SQLSTATE:42000], while trying to run this SQL script:

我在互联网上读到，分隔符语句仅在特定的gui上起作用，而不是每次都起作用.真的吗 ?为什么 ?因为我需要定界符语句而如何解决?

I read on the internet that the delimiter statement is only working on specific gui, not every time. Is that true ? Why ? How to resolve it because I need the delimiter statement ?

感谢您的帮助.

推荐答案

这似乎是播放框架2.0演变并创建的副本触发 (请注意，我认为，更好的答案是Roger在2013年5月24日发布的答案，即上面的链接)

This seems to be a duplicate of Play framework 2.0 evolutions and create trigger (Note that, in my view, the better answer is the one posted by Roger on May 24 2013, i.e. the link above)

定界符"不能在演变脚本文本中使用；我似乎找不到任何有关为何如此的文档.但是也许与分隔符"不是SQL语句而是SQL属性有关.

"delimiter" cannot be used in the evolution script text; I can't seem to find any documentation as to why this is so. But maybe it is got to do with the fact that "delimiter" is not an SQL statement but an SQL property.

但是，在Play 2文档的 Evolutions部分中有一个解决方案:

However, there is a solution in the Evolutions section of Play 2 docs:

Play将您的.sql文件拆分为一系列用分号分隔的语句，然后对数据库逐个执行它们.因此，如果您需要在语句中使用分号，请输入;;进行转义.代替 ;.例如，将INSERT INTO标点(名称，字符)VALUES('分号'，';;');.

所以在您的情况下

1. 删除定界符"属性，然后
2. 使用";;"代替 ";" 内部 SQL语句，以防止Play 2解析器分别执行这些内部SQL语句.

1. Remove the "delimiter" property, and
2. Use ";;" instead of ";" for your inner SQL statements, so as to prevent the Play 2 parser from executing these inner SQL statements separately.

以下是我已在Play 2.3和mysql 14.14 Distrib 5.5.40(Ubuntu 12.04LTS)中成功测试的示例:

Here is an example that I have successfully tested in Play 2.3 and mysql 14.14 Distrib 5.5.40 (Ubuntu 12.04LTS):

DROP TRIGGER IF EXISTS SOFTWARE_INSERT_CT_TRIGGER;
CREATE TRIGGER SOFTWARE_INSERT_CT_TRIGGER
BEFORE INSERT ON SOFTWARE
FOR EACH ROW
BEGIN
  IF NEW.CREATED_TIME = '0000-00-00 00:00:00' THEN
    SET NEW.CREATED_TIME = NOW();;
  END IF;;
END;

对于您的SQL脚本，以下内容应适用于Play 2.1及更高版本(请注意，我尚未对其进行测试):

In the case of your SQL script, the following should work with Play 2.1 and above (Note that I have not tested it):

DROP TRIGGER IF EXISTS invoice_line_insert;
CREATE TRIGGER invoice_line_insert AFTER INSERT
ON invoice_line FOR EACH ROW
BEGIN
  IF NEW.type = "DELIVERY" THEN
    UPDATE invoice
    SET invoice.etdelivery_amount = invoice.etdelivery_amount + NEW.amount
    WHERE invoice.id_invoice = NEW.invoice_parent_id_invoice;;
  ELSE
    UPDATE invoice
    SET invoice.etexpense_amount = invoice.etexpense_amount + NEW.amount
    WHERE invoice.id_invoice = NEW.invoice_parent_id_invoice;;
  END IF;;
  UPDATE invoice
  SET invoice.vatamount = (NEW.amount * ((
    SELECT vat.rate
    FROM vat
    WHERE vat.id_vat = NEW.vat_id_vat
  ) / 100)) + invoice.vatamount
  WHERE invoice.id_invoice = NEW.invoice_parent_id_invoice;;

  UPDATE invoice
  SET invoice.itamount = invoice.vatamount +
    invoice.etdelivery_amount +
    invoice.etexpense_amount
  WHERE invoice.id_invoice = NEW.invoice_parent_id_invoice;;
END;

这篇关于MySQL分隔符语句错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！