Spring Boot schema.sql文件中执行过程的问题 [英] Issue with executing procedure in spring boot schema.sql file

问题描述

我正在使用schema.sql文件在我的Spring Boot应用程序中创建/删除表，并且工作正常.

I am using schema.sql file to create/drop tables in my Spring boot application and it works fine.

但是当我添加了更改表的过程时:

But when I have added procedure for altering table:

DELIMITER $$
CREATE PROCEDURE Alter_Table()
BEGIN
 IF NOT EXISTS( SELECT NULL
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = 'test_table'
             AND table_schema = 'test'
             AND column_name = 'cc_test_id')  THEN

  alter table test_table add cc_test_id VARCHAR(128) NOT NULL;

END IF;
END $$

call Alter_Table;

我收到com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException异常.

I received com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException exception.

但是，此过程在MySQL工作台中的执行成功完成.

However, execution of this procedure in MySQL workbench finishes with successful result.

所以，如果有人知道这个问题的原因，请告诉我.

So, should anyone know what is the reason of this issue, let me know.

谢谢.

推荐答案

这是我发现的效果很好的解决方案，尽管它不理想，因为您必须更改SQL脚本.

Here is the solution I found that works well enough, though it is not ideal as you have to change your SQL script.

在您的application.properties文件中，更改数据源分隔符属性:

In your application.properties file change the DataSource separator property:

spring.datasource.separator=^;

然后更新您的schema.sql文件，如下所示:

Then update your schema.sql file to look as follows:

CREATE PROCEDURE Alter_Table()
BEGIN
 IF NOT EXISTS( SELECT NULL
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = 'test_table'
             AND table_schema = 'test'
             AND column_name = 'cc_test_id')  THEN

  alter table test_table add cc_test_id VARCHAR(128) NOT NULL;

END IF;
END ^;

call Alter_Table ^;

DELIMITER命令仅适用于MySQL CLI客户端和Workbench，不适用于Spring Boot数据库初始化.删除DELIMITER命令后，Spring Boot仍然会引发异常，因为它将无法理解存储过程中的;字符不是单独的语句，因此您必须更改数据源分隔符属性作为解决方法.

The DELIMITER command only works with the MySQL CLI client and Workbench and will not work for Spring Boot database initialization. Once you have removed the DELIMITER commands, Spring Boot will still throw an exception as it will not understand the ; characters in the stored procedures are not separate statements, so you have to change the datasource separator property as a workaround.

这篇关于Spring Boot schema.sql文件中执行过程的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！