带有存储过程的Spring Boot MySQL数据库初始化错误 [英] Spring Boot MySQL Database Initialization Error with Stored Procedures

问题描述

在Spring Boot应用程序中，我尝试通过运行

In a Spring Boot application I am attempting to initialize some MySQL database tables and stored procedures before running integration tests by placing a schema.sql file in my resources directory as recommended in the documentation.

创建表语句可以工作，但是创建过程语句会引发异常.导致异常的示例schema.sql文件语句如下所示:

The create table statements work but the create procedure statements throw an exception. A sample schema.sql file statement that causes the exception is shown below:

DROP PROCEDURE IF EXISTS `database`.FOO;
CREATE PROCEDURE `database`.FOO()

BEGIN
  SELECT * from `database`.employees;
END;

问题是Spring解析了存储过程中的;字符

The issue is that the ; character within the stored procedure is being parsed out by the Spring ScriptUtils class that parses the schema.sql file before executing it, which then causes MySQL to throw a syntax error on the script.

我看过

I have looked at the ScriptUtils class and have not been able to find a way to escape the ; characters with the procedures. Using \\ and \ as escape characters did not work either, as well as the MySQL DELIMITER command.

有人能够通过Spring Boot使用schema.sql文件创建MySQL存储过程吗?如果可以，他们可以举个例子吗?

Has anyone been able to create MySQL stored procedures using the schema.sql file with Spring Boot? If so could they show an example?

有关其他信息，以下春季JIRA问题解决了同一主题，但用不会修复标签关闭.

For some additional information, the following Spring JIRA issue addresses the same topic but was closed with a Won't Fix label.

推荐答案

答案很简单. Spring Boot具有DataSource分隔符属性，可以在application.properties文件中进行设置:

The answer turned out to be very simple. Spring Boot has a DataSource separator property that can be set in the application.properties file:

spring.datasource.separator=^;

然后在schema.sql文件中，不在存储过程中的所有;语句都需要使用新的分隔符进行更新.

Then in the schema.sql file all ; statements not within the stored procedure need to be updated with the new separator.

DROP PROCEDURE IF EXISTS `database`.FOO;
CREATE PROCEDURE `database`.FOO()

BEGIN
  SELECT * from `database`.employees;
END ^;

这篇关于带有存储过程的Spring Boot MySQL数据库初始化错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！