针对如何使用Liquibase和mysql创建触发器来修复SQL语法错误和mysql创建触发器报错这两个问题，本篇文章进行了详细的解答，同时本文还将给你拓展DelphiUnidacMySQL从备注代码

针对如何使用Liquibase和mysql创建触发器来修复SQL语法错误和mysql创建触发器报错这两个问题，本篇文章进行了详细的解答，同时本文还将给你拓展Delphi Unidac MySQL从备注代码语法错误创建触发器、Liquibase Windows MySQL-找不到数据库驱动程序：com.mysql.cj.jdbc.Driver、MySQL - 在第 1 行附近创建触发器错误 1064、MySQL中的SQL语法错误等相关知识，希望可以帮助到你。

• 如何使用Liquibase和mysql创建触发器来修复SQL语法错误（mysql创建触发器报错）
• Delphi Unidac MySQL从备注代码语法错误创建触发器
• Liquibase Windows MySQL-找不到数据库驱动程序：com.mysql.cj.jdbc.Driver
• MySQL - 在第 1 行附近创建触发器错误 1064
• MySQL中的SQL语法错误

如何使用Liquibase和mysql创建触发器来修复SQL语法错误（mysql创建触发器报错）

我正在为MySQL数据库设置第一个liquibase maven项目。已做好创建触发器的工作。

我相信这是liquibase和JDBC无法正确处理多语句SQL的问题，但我无法弄清缺少的内容。

这是我的pom依赖

    <dependency>      <groupId>org.liquibase</groupId>      <artifactId>liquibase-parent</artifactId>      <version>3.5.3</version>      <type>pom</type>    </dependency>    <dependency>      <groupId>org.liquibase</groupId>      <artifactId>liquibase-core</artifactId>      <version>3.5.3</version>    </dependency>    <dependency>      <groupId>mysql</groupId>      <artifactId>mysql-connector-java</artifactId>      <version>5.1.46</version>    </dependency>    <dependency>      <groupId>org.liquibase</groupId>      <artifactId>liquibase-maven-plugin</artifactId>      <version>3.6.3</version>    </dependency>

这是我的liquibase包含文件

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd">  <changeSet id="event_horizon_1_0_0" author="lmtyler" dbms="mysql">    <sql>      drop trigger if exists ai_event_approval;    </sql>  </changeSet>  <changeSet id="event_horizon_1_0_1" author="lmtyler" dbms="mysql">    <sqlFile splitStatements="false" stripComments="false" endDelimiter="DONE" path="01__ai_event_approval.sql" relativeToChangelogFile="true" />  </changeSet></databaseChangeLog>

这是我的SQL文件

CREATE DEFINER =''evclient''@''%'' TRIGGER ai_event_approval    AFTER INSERT    ON event_approval    FOR EACH rowbegin    insert into event_approval_log (rowAction,                                    actionTs,                                    event,                                    requestorEmail,                                    requestReason,                                    statusType,                                    approverUserId,                                    approverReason,                                    lastChangTs)        values (''I'',                current_timestamp(6),                new.event,                new.requestorEmail,                new.requestReason,                new.statusType,                new.approverUserId,                new.approverReason,                new.lastChangTs);end;# DONE

我期望通过设置splitStatements，stripComments和endDelimiter，我将获得liquibase来将整个SQL作为一个JDBC发送。

但是我收到这个错误

[ERROR] Failed to execute goal org.liquibase:liquibase-maven-plugin:3.6.3:update (default) on project event-horizon-mysql: Error setting up or running Liquibase: Migration failed for change set /Users/lmtyler/OneDrive - Walmart Inc/workspace/code/event-horizon/event-horizon-mysql/src/main/java/resources/liquibase/schema/triggers/02__au_event_approval.sql::event_horizon_1_0_1::lmtyler:[ERROR]      Reason: liquibase.exception.DatabaseException: 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 '''' at line 23 [Failed SQL: CREATE TRIGGER au_event_approval[ERROR]     AFTER UPDATE[ERROR]     ON event_approval[ERROR]     FOR EACH row[ERROR] begin[ERROR]     insert into event_approval_log (rowAction,[ERROR]                                     actionTs,[ERROR]                                     event,[ERROR]                                     requestorEmail,[ERROR]                                     requestReason,[ERROR]                                     statusType,[ERROR]                                     approverUserId,[ERROR]                                     approverReason,[ERROR]                                     lastChangTs)[ERROR]     values (''U'',[ERROR]             current_timestamp(6),[ERROR]             new.event,[ERROR]             new.requestorEmail,[ERROR]             new.requestReason,[ERROR]             new.statusType,[ERROR]             new.approverUserId,[ERROR]             new.approverReason,[ERROR]             new.lastChangTs)]

答案1

一夜无眠，再加上@Jens建议，我终于明白了这个问题。

首先，如果您看到我的帖子，则该错误不是我所认为的SQL错误。我以为AFTER INSERT是引发错误的原因，但这是AFTER UPDATE

关键是确保设置splitStatements:false，endDelimiter而无需设置。

这是两个有效的示例：

<changeSet id="event_horizon_1_0_1" author="lmtyler" dbms="mysql">    <sqlFile splitStatements="false" stripComments="false" path="01__ai_event_approval.sql" relativeToChangelogFile="true"/></changeSet>

与此SQL文件

CREATE DEFINER =''evclient''@''%'' TRIGGER ai_event_approval    AFTER INSERT    ON event_approval    FOR EACH rowbegin    insert into event_approval_log (rowAction,                                    actionTs,                                    event,                                    requestorEmail,                                    requestReason,                                    statusType,                                    approverUserId,                                    approverReason,                                    lastChangTs)        values (''I'',                current_timestamp(6),                new.event,                new.requestorEmail,                new.requestReason,                new.statusType,                new.approverUserId,                new.approverReason,                new.lastChangTs);end;

这是使用SQL格式

--changeset lmtyler:event_horizon_1_0_1 dbms:mysql splitStatements:falseCREATE TRIGGER au_event_approval    AFTER UPDATE    ON event_approval    FOR EACH rowbegin    insert into event_approval_log (rowAction,                                    actionTs,                                    event,                                    requestorEmail,                                    requestReason,                                    statusType,                                    approverUserId,                                    approverReason,                                    lastChangTs)    values (''U'',            current_timestamp(6),            new.event,            new.requestorEmail,            new.requestReason,            new.statusType,            new.approverUserId,            new.approverReason,            new.lastChangTs);end;

Delphi Unidac MySQL从备注代码语法错误创建触发器

正如@olivier提到的DELIMETER特定于HeidiSQL一样，我尝试删除delimeter并再次尝试但未解决问题，但是当我认为删除时，我所做的是从新代码中调用带有delimeter的旧代码。距离计，但我不是。

因此，正如第一篇评论中提到的@olivier一样，我将查询更改为此；

CREATE TRIGGER pdfdenemeu BEFORE INSERT ON denemetbl FOR EACH ROW BEGIN SET NEW.iki= CONCAT(NEW.bir,'.pdf'); END;

工作完美。 @olivier谢谢，我欠你一个。

Liquibase Windows MySQL-找不到数据库驱动程序：com.mysql.cj.jdbc.Driver

将驱动程序更改为

hamcrest

或更新您的mysql依赖项。我相信您的依存关系包含软件包mockito-kotlin，但mysql updated的库包含新软件包。

例如尝试使用this one。

MySQL - 在第 1 行附近创建触发器错误 1064

DROP TRIGGER IF EXISTS tr_set_location_engagement;
DELIMITER $$
CREATE TRIGGER tr_set_location_engagement
BEFORE INSERT ON v2_nc_nip_cap_engagement
FOR EACH ROW
BEGIN
SET NEW.water_system_id = (SELECT water_system_id FROM v2_nc_registered_water_systems WHERE NEW.system_name = water_system_name);
SET NEW.province_id = (SELECT province_id FROM v2_nc_province WHERE NEW.province_name = province_name);
SET NEW.island_id = (SELECT island_id FROM v2_nc_island WHERE NEW.island_name = island_name);
SET NEW.area_council_id = (SELECT area_council_id FROM v2_nc_area_council WHERE NEW.area_council_name = area_council_name);
END $$
DELIMITER ;

$journey = $_POST[''way''];$from = $_POST[''from''];$to = $_POST[''to''];$dpdt = $_POST[''dp_date''];$rtdt = $_POST[''rt_date''];$fare = $_POST[''fare''];$sql = "insert into tours set " .        "journey=''$journey'', from=''$from'', to=''$to'', dp_date=CAST(''$dpdt'' AS DATE), " .        "rt_date=CAST(''$rtdt'' AS DATE), fare=''$fare''";

insert into tours set journey=''round'', from=''Aurangabad'', to=''Kashmir'', dp_date=CAST(''27-08-2013'' AS DATE), rt_date=CAST(''21-08-2013'' AS DATE), fare=''2500''

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 ''from=Aurangabad, to=''Kashmir'', dp_date=CAST(''27-08-2013'' AS DATE), rt_date=CAST('' at line 1

`from`=''$from'', `to`=''$to''