java - Spring Boot 数据库初始化 MySQLException for Trigger

Question

我正在使用带有 schema.sql 文件的 Spring JDBC 进行 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=^;在属性文件中提到 here

但它失败了，因为

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

我想我的问题与this 相同问题，但这是在 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

当我在 application.properties 中添加 spring.datasource.separator=^; 并且程序/触发器外的每一行都应该以以下方式终止时，我的问题得到了解决^;示例如下:

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 ^;