参考博客: https://www.jianshu.com/p/88125f1cf91c
1. 启动时执行
当有在项目启动时先执行指定的sql语句的需求时,可以在resources
文件夹下添加需要执行的sql
文件,文件中的sql
语句可以是DDL
脚本或DML
脚本,然后在配置加入相应的配置即可,如下:
spring:
datasource:
schema: classpath:schema.sql # schema.sql中一般存放的是DDL脚本,即通常为创建或更新库表的脚本 data: classpath:data.sql # data.sql中一般是DML脚本,即通常为数据插入脚本
2. 执行多个sql文件
spring.datasource.schema
和spring.datasource.data
都是支持接收一个列表,所以当需要执行多个sql文件时,可以使用如下配置:
spring:
datasource:
schema: classpath:schema_1.sql, classpath:schema_2.sql data: classpath:data_1.sql, classpath:data_2.sql 或 spring: datasource: schema: - classpath:schema_1.sql - classpath:schema_2.sql data: - classpath:data_1.sql - classpath:data_2.sql
3. 不同运行环境执行不同脚本
一般情况下,都会有多个运行环境,比如开发、测试、生产等。而不同运行环境通常需要执行的sql
会有所不同。为解决这个问题,可以使用通配符来实现。
创建不同环境的文件夹
在resources文件夹创建不同环境对应的文件夹,如dev/
、sit/
、prod/
。
配置
application.yml
spring:
datasource:
schema: classpath:${spring.profiles.active:dev}/schema.sql
data: classpath:${spring.profiles.active:dev}/data.sql
注:
${}
通配符支持缺省值。如上面的配置中的${spring.profiles.active:dev}
,其中分号前是取属性spring.profiles.active
的值,而当该属性的值不存在,则使用分号后面的值,即dev
。
bootstrap.yml
spring:
profiles:
active: dev # dev/sit/prod等。分别对应开发、测试、生产等不同运行环境。
提醒:
spring.profiles.active
属性一般在bootstrap.yml
或bootstrap.properties
中配置。
4. 支持不同数据库
因为不同数据库的语法有所差异,所以要实现同样的功能,不同数据库的sql
语句可能会不一样,所以可能会有多份不同的sql
文件。当需要支持不同数据库时,可以使用如下配置:
spring:
datasource:
schema: classpath:${spring.profiles.active:dev}/schema-${spring.datasource.platform}.sql
data: classpath:${spring.profiles.active:dev}/data-${spring.datasource.platform}.sql
platform: mysql
提醒:
platform
属性的默认值是'all'
,所以当有在不同数据库切换的情况下才使用如上配置,因为默认值的情况下,spring boot
会自动检测当前使用的数据库。
注:此时,以
dev
允许环境为例,resources/dev/
文件夹下必须存在如下文件:schema-mysql.sql
和data-mysql.sql
。
5. 避坑
5.1 坑
当在执行的sql
文件中存在存储过程或函数时,在启动项目时会报错。
比如现在有这样的需求:项目启动时,扫描某张表,当表记录数为0时,插入多条记录;大于0时,跳过。
schema.sql
文件脚本如下:
-- 当存储过程`p1`存在时,删除。
drop procedure if exists p1;
-- 创建存储过程`p1`
create procedure p1()
begin declare row_num int; select count(*) into row_num from `t_user`; if row_num = 0 then INSERT INTO `t_user`(`username`, `password`) VALUES ('zhangsan', '123456'); end if; end; -- 调用存储过程`p1` call p1(); drop procedure if exists p1;
启动项目,报错,原因如下:
Caused by: 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 procedure p1() begin declare row_num int' at line 1
大致的意思是:'create procedure p1() begin declare row_num int'
这一句出现语法错误。刚看到这一句,我一开始是懵逼的,吓得我赶紧去比对mysql
存储过程的写法,对了好久都发现没错,最后看到一篇讲解spring boot
配置启动时执行sql
脚本的文章,发现其中多了一项配置:spring.datasource.separator=$$
。然后看源码发现,spring boot
在解析sql
脚本时,默认是以';'
作为断句的分隔符的。看到这里,不难看出报错的原因,即:spring boot
把'create procedure p1() begin declare row_num int'
当成是一条普通的sql
语句。而我们需要的是创建一个存储过程。
5.2 解决方案
修改sql
脚本的断句分隔符。如:spring.datasource.separator=$$
。然后把脚本改成:
-- 当存储过程`p1`存在时,删除。
drop procedure if exists p1;$$
-- 创建存储过程`p1`
create procedure p1()
begin declare row_num int; select count(*) into row_num from `t_user`; if row_num = 0 then INSERT INTO `t_user`(`username`, `password`) VALUES ('zhangsan', '123456'); end if; end;$$ -- 调用存储过程`p1` call p1();$$ drop procedure if exists p1;$$
5.3 不足
因为sql
脚本的断句分隔符从';'
变成'$$'
,所以可能需要在DDL
、DML
语句的';'
后加'$$'
,不然可能会出现将整个脚本当成一条sql
语句来执行的情况。比如:
-- DDL
CREATE TABLE `table_name` (
-- 字段定义
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;$$
-- DML
INSERT INTO `table_name` VALUE(...);$$