Spring Boot数据库初始化MySQLException的触发器 [英] Spring Boot Database initialization MySQLException for Trigger
问题描述
我正在使用带有Spring.JDBC和schema.sql文件的Spring Boot数据库初始化.我正在使用MYSQL
I am using Spring Boot Database initialization using Spring JDBC with schema.sql file.I am using MYSQL
如果我按照以下方式在schema.sql中创建简单的表,则效果很好
If I have simple table creation in schema.sql as follows it works fine
CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
但是当我添加一个如下所示的触发器时,该触发器可以在MySQL Workbench中正常运行
But when I add one trigger as follows which runs correctly in 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 = ^; 此处
I have used spring.datasource.separator=^; in properties file as mentioned 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
我猜我的问题与此问题,但在postgresql中.
I guess my problem is same as this question but that is in postgresql.
如果我删除spring.datasource.separator = ^;从属性文件中,并在光标下方
If I remove spring.datasource.separator=^; from properties file and have below cursor
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
推荐答案
当我在application.properties
中添加spring.datasource.separator=^;
且过程/触发的每一行都应以^;
终止时,我的问题已解决.
示例如下:
My issue was resolved when I have added spring.datasource.separator=^;
in application.properties
and every line out side the procedure/trigger should be terminated with ^;
Example as follows:
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 Boot数据库初始化MySQLException的触发器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!