Spring Boot数据库初始化MySQLException的触发器

Question

我正在使用Spring JDBC和schema.sql文件使用Spring Boot数据库初始化。我正在使用MYSQL

如果我按照以下方式在schema.sql中创建简单的表，则可以正常工作

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

但是当我添加一个触发器如下，它可以在MySQL Workbench中正确运行

DROP TRIGGER IF EXISTS Persons_log_update;

CREATE TRIGGER Persons_log_update 
    BEFORE UPDATE ON Persons
    FOR EACH ROW 
BEGIN

    INSERT INTO Personshistory(PersonID,LastName,FirstName,Address,City)
    values(OLD.PersonID,OLD.LastName,OLD.FirstName,OLD.Address,OLD.City);

END ^;

我已经使用spring.datasource.separator = ^;在属性文件中所提到这里

但是它失败，因为

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'CREATE TRIGGER Persons_log_update BEFORE UPDATE ON Persons FOR EACH ROW BE' at line 1

我猜我的问题和[这个问题相同，但是在postgresql中。

编辑：如果我删除spring.datasource.separator = ^; 从属性文件中并具有下面的光标

DROP TRIGGER IF EXISTS Persons_log_update;

DELIMITER $$
CREATE TRIGGER Persons_log_update 
    BEFORE UPDATE ON Persons
    FOR EACH ROW 
BEGIN

    INSERT INTO Personshistory(PersonID,LastName,FirstName,Address,City)
    values(OLD.PersonID,OLD.LastName,OLD.FirstName,OLD.Address,OLD.City);

END$$
DELIMITER ;

它给出了同样的错误

 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 $$ CREATE TRIGGER Persons_log_update BEFORE UPDATE ON Persons FO' at line 1

Answer 1

当我添加spring.datasource.separator=^;进来application.properties并且每一行都结束时，过程/触发器应通过^;
Example 终止，我的问题得到了解决，如下所示：

DROP TRIGGER IF EXISTS Persons_log_update ^;

CREATE TRIGGER Persons_log_update 
    BEFORE UPDATE ON Persons
    FOR EACH ROW 
BEGIN

    INSERT INTO Personshistory(PersonID,LastName,FirstName,Address,City)
    values(OLD.PersonID,OLD.LastName,OLD.FirstName,OLD.Address,OLD.City);

END ^;