使用Liquibase在mysql中创建触发器 [英] Using liquibase to create triggers in mysql

问题描述

我想使用liquibase在mysql中创建一个简单的触发器.以下脚本可直接从mysql运行:

I want to create a simple trigger in mysql using liquibase. The following script works directly from mysql:

delimiter $$

CREATE TRIGGER myTrigger
    BEFORE INSERT ON myTable FOR EACH ROW
BEGIN
    IF(NEW.my_timestamp IS NULL) THEN
        SET NEW.my_timestamp = now();
    END IF;
END$$

delimiter ;

因此，我想为liquibase创建一个变更集以供使用，该变更集可以使用update命令来应用此触发器，并且还将在使用updateSQL命令时创建一个合适的sql脚本.

So, I want to create a changeset for liquibase to use that can apply this trigger using the update command, and will also create a suitable sql script when using the updateSQL command.

我尝试了变更集中的各种选项，包括splitStatements和endDelimiter，但只能获得与update命令或updateSQL命令一起使用的选项.两者都不是.

I have tried a variety of options in the changeset including splitStatements and endDelimiter, but have only been able to get something that works either with the update command or with the updateSQL command. Not with both.

这是一个使用格式化的sql的示例更改集，当我使用update命令时，它可以正常工作，但是当我使用updateSQL命令时，不能创建合适的sql

here's a sample change set using formatted sql which works fine when I use the update command, but does not create suitable sql when I use the updateSQL command

-- liquibase formatted sql
-- changeset pcoates33:trigger-1 splitStatements:false

CREATE TRIGGER myTrigger
    BEFORE INSERT ON myTable FOR EACH ROW
BEGIN
    IF(NEW.my_timestamp IS NULL) THEN
        SET NEW.my_timestamp = now();
    END IF;
END

-- rollback DROP TRIGGER IF EXISTS myTrigger;

这是我想要的用于updateSQL的方法，但是对于更新却失败:

and here's one that works how I want for updateSQL, but fails for update:

-- liquibase formatted sql
-- changeset pcoates33:trigger-1 splitStatements:false

delimiter $$

CREATE TRIGGER myTrigger
    BEFORE INSERT ON myTable FOR EACH ROW
BEGIN
    IF(NEW.my_timestamp IS NULL) THEN
        SET NEW.my_timestamp = now();
    END IF;
END$$

delimiter ;

-- rollback DROP TRIGGER IF EXISTS myTrigger;

基本问题是

1. mysql脚本需要定界符$$和定界符；在里面
2. 如果liquibase进行的jdbc调用开始时带有分隔符$$，则会失败

推荐答案

我同意@Akina的观点，好的解决方案是要么不使用触发器，要么将其压缩为单个语句.

I agree with @Akina, that a good solution is either not to use triggers, or condense them into single statements.

我正在尝试将liquibase引入现有数据库，因此希望能够在最初保持相同.然后应用更改以使其更简单.

I am trying to introduce liquibase to an existing database, so wanted to be able to keep it the same initially. Then apply changes to make it simpler.

所以，基本问题是

1. mysql脚本需要定界符$$和定界符;在里面
2. 如果liquibase进行的jdbc调用开始时带有分隔符$$，则会失败

经过无数次尝试，我想出了解决方案，它依赖使用gradle插件运行liquibase updateSql命令.我自己不能用liquibase达到它.我基本上评论了liquibase无法识别的代码部分，然后对liquibase创建的脚本文件进行后处理以取消注释.

After numerous attempts the solution I came up with relied on using the gradle plugin to run the liquibase updateSql command. I couldn't acheive it with liquibase on it's own. I basically comment the parts of code that liquibase does not recognise, then post process the script file created by liquibase to uncomment the statements.

这是我的变更集，格式为sql:

Here's my changeset as formatted sql:

-- liquibase formatted sql
-- changeset pcoates33:trigger-1 splitStatements:false stripComments:false
-- delimiter $$

CREATE TRIGGER myTrigger
    BEFORE INSERT ON myTable FOR EACH ROW
BEGIN
    IF(NEW.my_timestamp IS NULL) THEN
        SET NEW.my_timestamp = now();
    END IF;
END-- $$

-- delimiter ;
-- rollback DROP TRIGGER IF EXISTS myTrigger;

我可以用它运行liquibase更新，并且正确应用了.

I can run liquibase update with that and it is correctly applied.

我将gradle配置为使用liquibase插件，还使用outputFile:"$ projectDir/update.sql"

I configured gradle to use the liquibase plugin, and also to use outputFile : "$projectDir/update.sql"

然后更新了build.gradle文件，以扩展插件添加的updateSql任务，以更改我想要的输出语句中的注释

Then updated the build.gradle file to extend the updateSql task that was added by the plugin to change the comments from the statements I want in the output

updateSQL{
  doLast {
    ant.replace(file: "$projectDir/update.sql", token: '-- delimiter', value: 'delimiter')
    ant.replace(file: "$projectDir/update.sql", token: '-- $$', value: '$$')
  }
}

这篇关于使用Liquibase在mysql中创建触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！