SpringBoot中初始话和迁移数据库的轻量级方法

Tags: /Java/ Date Created:

初始化和迁移数据库的轻量方法

Java/SpringBoot中初始化和迁移Migrate数据库的方法，以及运行MySQL脚本支持Delimiter的方法

ⓘ

我们可以使用 Flyway 或 Liquibase 等组件来进行复杂的数据库版本控制，如果不想使用厚重的解决方案，本文介绍了一种无需借助任何第三方组件、轻量级初始化数据库(database init)和迁移数据库(database migrate)的方法。

在SpringBoot中，我们可能需要运行MySQL脚本 .sql 文件来初始化数据库表结构，初始化数据字典数据等，如果脚本中，有创建函数、存储过程等复杂的处理逻辑，那么很可能会报错。

在MySQL Workbench或者其他SQL客户端当中，会有 delimiter xx 这样的语句来处理SQL语句分段的问题。如果你直接在 SpringBoot 中运行相应的 .sql 脚本文件，是会提示 delimiter 语句错误，因为 delimiter 本身就不是一个合法的SQL 语句，它只是一个预处理定义，让MySQL客户端能准确分割完整的 SQL 语句。
默认情况下，MySQL是使用分号(;) 作为语句的分隔符，碰到 ; 就立刻把之前的语句运行，但如果 .sql 文件中，有存储过程、函数等情况，就会碰到中间也使用 ; 的情况，此时 SpringBoot 就无法正确分隔完整的 SQL 语句了。例如如果我们需要在程序启动的时候，运行某些脚本，如初始化数据库、迁移数据库脚本，那么很可能碰到类似的情况。

解决方法：

✔

使用 ResourceDatabasePopulator.setSeparator 指定分隔符。

例如：

在 Resource\db\目录下，放置以下文件
- schema.sql - 为数据库表结构 dump
- data.sql - 为数据库初始化数据 dump
- migrate.sql - 为数据库迁移更新语句，本例中以 $ 作为分隔符

由于没有对是否运行过 migrate.sql 做检测，因此你需要自己保证 migrate.sql 里面代码可以支持重复运行的情况。你也可以更改下面的代码逻辑，比如用一个表保存是否运行过。

package tacos.config;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.core.io.Resource;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.init.DataSourceInitializer;
import org.springframework.jdbc.datasource.init.DatabasePopulator;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;

import javax.sql.DataSource;

/**
 * Automatically init the database and data if needed
 *
 * @since 1.0
 */
//@Configuration
@Slf4j
public class DataSourceInit {
    @Value("classpath:db/schema.sql")
    private Resource ddl;

        @Value("classpath:db/data.sql")
    private Resource dml;

    @Value("classpath:db/migrate.sql")
    private Resource migrate;

    @Bean
    public DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
        final DataSourceInitializer initializer = new DataSourceInitializer();
        // 设置数据源
        initializer.setDataSource(dataSource);
        initializer.setDatabasePopulator(databasePopulator(dataSource));
        return initializer;
    }

    private DatabasePopulator databasePopulator(DataSource dataSource) {
        final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        try {
            jdbcTemplate.queryForMap("select count(1) from 标志性表名");
            // Migrate script must use $ as SQL statement separator
            populator.setSeparator("$");
            populator.addScripts(migrate);
        } catch (DataAccessException e) {
            log.warn("初始化脚本报错:" + e.getMessage());
            // 报错,表不存在,初次导入，执行相关脚本
            populator.addScripts(ddl);
            populator.addScripts(dml);
        } catch (Exception e) {
            log.warn("初始化脚本报错:" + e.getMessage());
        }
        return populator;
    }

}

migrate.sql 中，SQL语句必须以 $ 分隔即可:

-- Migrate SQL must use $ as separator

drop procedure if exists sp_demo $
create procedure sp_demo()
begin
    declare continue handler for SQLEXECEPTION begin end;
    alter table tablename
      add column 'column_name' INT NULL default 0 comment 'demo to add column when migrate';
end $

call sp_demo $

-- update your_tabe set field = 'value' where id = 1 $
-- other migrate SQL

当然，如果你不想用不同的分隔符，也可以使用动态SQL语句：

ddl - MySQL: ALTER TABLE if column not exists - Stack Overflow

alter table - MySQL: How to add a column if it doesn't already exist? - Stack Overflow

    SELECT count(*)
    INTO @exist
    FROM information_schema.columns
    WHERE table_schema = 'mydatabase'
    and COLUMN_NAME = 'mycolumn'
    AND table_name = 'mytable' LIMIT 1;

    set @query = IF(@exist <= 0, 'ALTER TABLE mydatabase.`mytable`  ADD COLUMN `mycolumn` MEDIUMTEXT NULL',
    'select \'Column Exists\' status');
    prepare stmt from @query;
    EXECUTE stmt;