首页 计算机科学 框架 数据库版本控制

# 数据库版本控制 - liqiubase

# 简介

LiquiBase 是一个用于数据库重构和迁移的开源工具，通过日志文件的形式记录数据库的变更，然后执行日志文件中的修改，将数据库更新或回滚到一致的状态。

  • LiquiBase 的主要特点有：
    支持几乎所有主流的数据库，如 MySQL, PostgreSQL, Oracle, Sql Server, DB2 等
  • 支持多开发者的协作维护
  • 日志文件支持多种格式，如 XML, YAML, JSON, SQL 等
  • 支持多种运行方式，如命令行、Spring 集成、Maven 插件、Gradle 插件等

# 同类型其他产品

liquibase 主要特性

  • 灵活的 Schema 变更：支持 SQL, XML, JSON, and YAML 语言
  • 自动生成可 review 的 SQL 脚本
  • 支持可重复和不可重复执行的变更
  • 集成和扩展能力
  • 自动或者通过自定义回滚 SQL 撤销数据库的变更
  • 使用上下文和前提条件来微调脚本的执行
  • Liquibase hub 实时监控和可观测能力

Flyway 主要特性

  • 基于 SQL/JAVA 变更
  • 重复变更
  • 撤销变更
  • Callback
  • 批量变更
  • ....

这里为什么使用 liquibase 是因为我在用集成 flyway 对旧版本的 sqlserver 需要社区版才支持，而 liquibase 对 sql 变更更灵活

# 说明

  1. changelog 是 Liquibase 版本控制的核心，Liquibase 通过有序的 changelog 罗列你对数据库的更改，你可以把它想象成为一个账本，包含你对数据库所有操作的文件
  2. LiquiBase 在执行 changelog 时，会在数据库中插入 DATABASECHANGELOG 和 DATABASECHANGELOGLOCK 两张表。在执行 changelog 中的 changeSet 时，会首先查看 DATABASECHANGELOG 表，如果已经执行过，则会跳过，如果没有执行过，则执行并记录 changelog 日志。
  3. DATABASECHANGELOG 表：数据表，用于记录已经应用的变更集的信息，包括变更集 ID、应用时间、应用用户等信息。这个表格是 Liquibase 用来判断数据库的变更状态，避免重复应用变更集的关键
  4. DATABASECHANGELOGLOCK 表：数据表，用于控制 Liquibase 在应用变更时的并发访问。当 Liquibase 应用变更时，它会自动在表中创建一行记录，防止其他进程或线程同时修改数据库。当变更应用完成后，Liquibase 会删除这个表格中的记录，以允许其他进程或线程修改数据库。

# 整合 spring boot

本章 liquibase 整合 springboot 是以组件的方式封装的，通过自定义配置项目是否需要启用数据库版本控制。

本项目是一个用 mybatis 实现可动态添加多数据源的项目，所以 liquibase 也需要动态的为多数据源添加版本控制

要实现多数据源的版本控制需要先实现单个的，下面是单个数据源的整合步骤：

# 单数据源整合

  1. pox.xml 文件

    <dependency>
                <groupId>org.liquibase</groupId>
                <artifactId>liquibase-core</artifactId>
            </dependency>

  2. 配置 application.yml

    spring:
      liquibase:
        change-log: classpath:liquibase/master.xml #指定 master.xml 文件的位置

  3. 新建 master.xml（用于配置你的增量脚本）

    master.xml 配置如下（示例）

    <?xml version="1.0" encoding="UTF-8"?>
    <databaseChangeLog
            xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
            http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
        <!-- id：唯一 id author：修改人 -->
        <changeSet id="1.0.0-20240318-001" author="Tz">
            <!-- 使用 sql 脚本 一些建表语句和插入语句 -->
            <sqlFile path="classpath:db/mysql/master/scaffold_init.sql"/>
        </changeSet>
    </databaseChangeLog>
  4. 注册 liquibase bean
    @Configuration
    public class LiquibaseConfiguration() {
        
        @Bean
        @ConfigurationProperties(prefix = "spring.liquibase")
        public LiquibaseProperties getLiquibaseProperties() {
            return new LiquibaseProperties();
        }
        @Bean
        public SpringLiquibase liquibase(DataSource dataSource) {
            LiquibaseProperties liquibaseProperties = getLiquibaseProperties();
            SpringLiquibase liquibase = new SpringLiquibase();
            liquibase.setChangeLog(liquibaseProperties.getChangeLog());
            liquibase.setDataSource(dataSource);       
            liquibase.setShouldRun(true);
            return liquibase;
        }
    }
  5. 到此就实现了单数据源的数据库版本控制了，思考两个问题
    一般项目基本会有多个数据源，而且是不同的数据库，这个时候怎么办
# 多数据源整合
需要达到的效果：
开始：
  1. 项目结构
    1. pom.xml
      <dependencies>
              <dependency>
                  <groupId>org.liquibase</groupId>
                  <artifactId>liquibase-core</artifactId>
              </dependency>
              <dependency>
                  <groupId>com.baomidou</groupId>
                  <artifactId>mybatis-plus-boot-starter</artifactId>
              </dependency>
              <!-- DB 相关 -->
              <dependency>
                  <groupId>com.mysql</groupId>
                  <artifactId>mysql-connector-j</artifactId>
              </dependency>
              <dependency>
                  <groupId>com.oracle.database.jdbc</groupId>
                  <artifactId>ojdbc8</artifactId>
                  <optional>true</optional>
              </dependency>
              <dependency>
                  <groupId>org.postgresql</groupId>
                  <artifactId>postgresql</artifactId>
                  <optional>true</optional>
              </dependency>
              <dependency>
                  <groupId>com.microsoft.sqlserver</groupId>
                  <artifactId>mssql-jdbc</artifactId>
              </dependency>
              <dependency>
                  <groupId>com.dameng</groupId>
                  <artifactId>DmJdbcDriver18</artifactId>
      <!--            <optional>true</optional>-->
              </dependency>
              <dependency>
                  <groupId>com.baomidou</groupId>
                  <artifactId>dynamic-datasource-spring-boot-starter</artifactId> <!-- 多数据源 -->
              </dependency>
          </dependencies>
    2. application.yml 这里不在是用原来 liquibase 的配置，而是自己定义的
      liquibase:
        changelog: changelog-master.xml
        enabled: true
      spring:
        # 数据源配置项
        autoconfigure:
          exclude:
            - com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure # 排除 Druid 的自动配置，使用 dynamic-datasource-spring-boot-starter 配置多数据源
        datasource:
          druid: # Druid 【监控】相关的全局配置
            web-stat-filter:
              enabled: true
            stat-view-servlet:
              enabled: true
              allow: # 设置白名单，不填则允许所有访问
              url-pattern: /druid/*
              login-username: # 控制台管理用户名和密码
              login-password:
            filter:
              stat:
                enabled: true
                log-slow-sql: true # 慢 SQL 记录
                slow-sql-millis: 100
                merge-sql: true
              wall:
                config:
                  multi-statement-allow: true
          dynamic: # 多数据源配置
            druid: # Druid 【连接池】相关的全局配置
              initial-size: 1 # 初始连接数
              min-idle: 1 # 最小连接池数量
              max-active: 20 # 最大连接池数量
              max-wait: 600000 # 配置获取连接等待超时的时间，单位：毫秒
              time-between-eviction-runs-millis: 60000 # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位：毫秒
              min-evictable-idle-time-millis: 300000 # 配置一个连接在池中最小生存的时间，单位：毫秒
              max-evictable-idle-time-millis: 900000 # 配置一个连接在池中最大生存的时间，单位：毫秒
              validation-query: SELECT 1 # 配置检测连接是否有效
              test-while-idle: true
              test-on-borrow: false
              test-on-return: false
            primary: master
            datasource:
              master:
                name: scaffold4
                url: jdbc:mysql://127.0.0.1:3306/${spring.datasource.dynamic.datasource.master.name}?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&nullCatalogMeansCurrent=true&createDatabaseIfNotExist=true # MySQL Connector/J 8.X 连接的示例
                username: root
                password: 123456
              slave: # 模拟从库，可根据自己需要修改
                name: scaffold4
                lazy: true # 开启懒加载，保证启动速度
                url: jdbc:sqlserver://127.0.0.1:1433;DatabaseName=${spring.datasource.dynamic.datasource.slave.name};createTablesIfNotExist=true;encrypt=false;trustServerCertificate=true; # SQLServer 连接的示例
                username: sa
                password: 123456
    3. 代码
      /**
       * <p> Project: scaffold - LiquibaseProperties </p>
       *
       * 数据库版本控制 liquibase 配置类
       * @author Tz
       * @version 1.0.0
       * @date 2024/02/29 19:13
       * @since 1.0.0
       */
      @ConfigurationProperties("liquibase")
      @Data
      @Validated
      public class LiquibaseProperties {
          /**
           * Change log configuration path.
           */
          private String changeLog = "changelog-master.yaml";
          /**
           * Whether to clear all checksums in the current changelog, so they will be
           * recalculated upon the next update.
           */
          private boolean clearChecksums;
          /**
           * Comma-separated list of runtime contexts to use.
           */
          private String contexts;
          /**
           * Default database schema.
           */
          private String defaultSchema;
          /**
           * Schema to use for Liquibase objects.
           */
          private String liquibaseSchema;
          /**
           * Tablespace to use for Liquibase objects.
           */
          private String liquibaseTablespace;
          /**
           * Name of table to use for tracking change history.
           */
          private String databaseChangeLogTable = "DATABASECHANGELOG";
          /**
           * Name of table to use for tracking concurrent Liquibase usage.
           */
          private String databaseChangeLogLockTable = "DATABASECHANGELOGLOCK";
          /**
           * Whether to first drop the database schema.
           */
          private boolean dropFirst;
          /**
           * Whether to enable Liquibase support.
           */
          private boolean enabled = true;
          /**
           * Login user of the database to migrate.
           */
          private String user;
          /**
           * Login password of the database to migrate.
           */
          private String password;
          /**
           * Fully qualified name of the JDBC driver. Auto-detected based on the URL by default.
           */
          private String driverClassName;
          /**
           * JDBC URL of the database to migrate. If not set, the primary configured data source
           * is used.
           */
          private String url;
          /**
           * Comma-separated list of runtime labels to use.
           */
          private String labels;
          /**
           * Change log parameters.
           */
          private Map<String, String> parameters;
          /**
           * File to which rollback SQL is written when an update is performed.
           */
          private File rollbackFile;
          /**
           * Whether rollback should be tested before update is performed.
           */
          private boolean testRollbackOnUpdate;
          /**
           * Tag name to use when applying database changes. Can also be used with
           * "rollbackFile" to generate a rollback script for all existing changes associated
           * with that tag.
           */
          private String tag;
      }
      // 数据库版本控制启动类
      /**
       * <p> Project: scaffold - ScaffoldLiquibaseAutoConfiguration </p>
       *
       * Liquibase 数据库版本控制启动类
       * @author Tz
       * @version 1.0.0
       * @date 2024/02/23 18:56
       * @since 1.0.0
       */
      @AutoConfiguration(
              after = {DataSourceAutoConfiguration.class}
      )
      @ConditionalOnProperty(prefix = "liquibase", value = "enabled", matchIfMissing = true)
      @EnableConfigurationProperties({LiquibaseProperties.class})
      public class ScaffoldLiquibaseAutoConfiguration {
          @Bean
          public SpringLiquibase liquibase(LiquibaseProperties liquibaseProperties, DynamicRoutingDataSource dataSource, DynamicDataSourceProperties dynamicDataSourceProperties) throws SQLException, LiquibaseException {
              // 本来想弄成返回一个 list 集合，或者用 beanfactory 但是不行
            	// 投机做法 因为这里会被 liquibase 读取到 会在动态数据源初始化完毕执行，所有在 DynamicLiquibaseSource 中动态注册多个
              return new DynamicLiquibaseSource(liquibaseProperties, dataSource, dynamicDataSourceProperties).initDynamicLiquibase();
          }
      }
      DynamicLiquibaseSource.java
      import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
      import com.baomidou.dynamic.datasource.ds.ItemDataSource;
      import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
      import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
      import com.baomidou.mybatisplus.annotation.DbType;
      import com.baomidou.mybatisplus.extension.toolkit.JdbcUtils;
      import com.tz.scaffold.framework.liquibase.config.LiquibaseProperties;
      import com.tz.scaffold.framework.liquibase.core.util.JdbcUrlMetaDataUtil;
      import liquibase.Contexts;
      import liquibase.LabelExpression;
      import liquibase.Liquibase;
      import liquibase.database.Database;
      import liquibase.database.DatabaseFactory;
      import liquibase.database.jvm.JdbcConnection;
      import liquibase.exception.LiquibaseException;
      import liquibase.integration.spring.SpringLiquibase;
      import liquibase.resource.ClassLoaderResourceAccessor;
      import lombok.AllArgsConstructor;
      import lombok.Data;
      import javax.sql.DataSource;
      import java.sql.DatabaseMetaData;
      import java.sql.SQLException;
      import java.util.Map;
      /**
       * <p> Project: scaffold - DynamicLiquibaseSource </p>
       *
       * liquibase 动态数据源数据库版本控制初始化器
       * @author Tz
       * @version 1.0.0
       * @date 2024/03/02 19:49
       * @since 1.0.0
       */
      @Data
      @AllArgsConstructor
      public class DynamicLiquibaseSource {
          private LiquibaseProperties liquibaseProperties;
          private DynamicRoutingDataSource dataSource;
          private DynamicDataSourceProperties dynamicDataSourceProperties;
          /**
           * 初始化动态数据库版本控制
           * @return liquibase 版本控制器
           * @throws SQLException
           * @throws LiquibaseException
           */
          public SpringLiquibase initDynamicLiquibase() throws SQLException, LiquibaseException {
              // 读取到多个数据源的配置
              Map<String, DataSourceProperty> dataSourcePropertyMap = dynamicDataSourceProperties.getDatasource();
              for (DataSource tempDataSource : dataSource.getDataSources().values()) {
                  String name = ((ItemDataSource) tempDataSource).getName();
                  // 根据配置替换数据类型、数据源的名称（master、slav 等）
                  String changeLogPath = "classpath:db/{JdbcType}/{relation}/" + liquibaseProperties.getChangeLog();
                  DataSourceProperty dataSourceProperty = dataSourcePropertyMap.get(name);
                	// 如果不存在数据库则自动创建
                  JdbcUrlMetaDataUtil.createDataBasesIfNotExists(dataSourceProperty.getUrl(), dataSourceProperty.getUsername(), dataSourceProperty.getPassword());
                  DatabaseMetaData metaData = tempDataSource.getConnection().getMetaData();
                  DbType dbType = JdbcUtils.getDbType(metaData.getURL());
                  if (dbType.getDb().contains("sqlserver")) {
                      changeLogPath = changeLogPath.replace("{JdbcType}", "sqlserver").replace("{relation}", name);
                  } else {
                      changeLogPath = changeLogPath.replace("{JdbcType}", dbType.getDb()).replace("{relation}", name);
                  }
                  // 手动注册 liquibase
                  Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(tempDataSource.getConnection()));
                  Liquibase liquibase = new Liquibase(changeLogPath, new ClassLoaderResourceAccessor(), database);
                  liquibase.update(new Contexts(), new LabelExpression());
              }
            	// 随便返回一个
              SpringLiquibase liquibase = new SpringLiquibase();
              String changeLogPath = "classpath:db/{JdbcType}/{relation}/" + liquibaseProperties.getChangeLog();
              DatabaseMetaData metaData = dataSource.getConnection().getMetaData();
              DbType dbType = JdbcUtils.getDbType(metaData.getURL());
              changeLogPath = changeLogPath.replace("{JdbcType}", dbType.getDb()).replace("{relation}", "master");
              liquibase.setChangeLog(changeLogPath);
              liquibase.setDataSource(dataSource);
              liquibase.setShouldRun(true);
              return liquibase;
          }
      }
JdbcInfoEnum.java
import lombok.AllArgsConstructor;
import lombok.Getter;
/**
 * <p> Project: scaffold - JdbcInfoEnum </p>
 *
 * jdbc 连接的信息
 * @author Tz
 * @version 1.0.0
 * @date 2024/03/16 19:25
 * @since 1.0.0
 */
@Getter
@AllArgsConstructor
public enum JdbcInfoEnum {
    /**
     * 数据库名称
     */
    DATABASES_NAME("databases_name", "数据库名称"),
    /**
     * 连接参数值
     */
    PARAM_VALUES("param_values", "连接参数值"),
    /**
     * 原始数据库连接
     */
    ORIGINAL_URL("original_url", "原始数据库连接"),
    /**
     * url 中是否存在连接的数据库
     */
    EXISTS_DATABASES_FOR_URL("exists_databases_for_url", "url中是否存在连接的数据库");
    private final String infoName;
    private final String explain;
}
JdbcUrlMetaDataUtil.java
package com.tz.scaffold.framework.liquibase.core.util;
import com.baomidou.mybatisplus.core.toolkit.Assert;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.tz.scaffold.framework.liquibase.core.enums.JdbcInfoEnum;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import static com.tz.scaffold.framework.liquibase.core.enums.JdbcInfoEnum.*;
/**
 * <p> Project: scaffold - JdbcUrlMetaDataUtil </p>
 *
 * 获取 jdbc 中相关信息的工具类
 * @author Tz
 * @version 1.0.0
 * @date 2024/03/16 19:47
 * @since 1.0.0
 */
@Slf4j
public class JdbcUrlMetaDataUtil {
    /**
     * 如果 jdbcUrl 中的连接的数据库不存在则创建
     * @param jdbcUrl       数据库连接
     * @param username      用户名
     * @param password      密码
     * @throws SQLException 连接失败异常
     */
    public static void createDataBasesIfNotExists(String jdbcUrl, String username, String password) throws SQLException {
        Map<String, String> jdbcUrlInfo = getJdbcUrlInfo(jdbcUrl);
        String existsDatabasesForUrlFlag = jdbcUrlInfo.get(EXISTS_DATABASES_FOR_URL.getInfoName());
        String databaseName = jdbcUrlInfo.get(DATABASES_NAME.getInfoName());
        String existsDatabaseSql = getExistsDatabasesSql(jdbcUrl, databaseName);
        // 如果如果是连接默认数据库 则不执行自动创建数据库
        if ("false".equalsIgnoreCase(existsDatabasesForUrlFlag)) {
            return;
        }
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(getDriverNameFromUrl(jdbcUrl));
        // 设置去除数据库参数后的 jdbcUrl
        dataSource.setUrl(jdbcUrlInfo.get(ORIGINAL_URL.getInfoName()) + jdbcUrlInfo.get(PARAM_VALUES.getInfoName()));
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        // 检查数据库是否存在，如果不存在则创建数据库
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(existsDatabaseSql)) {
            // 如果不存在则新增数据库
            if (!resultSet.next()) {
                statement.executeUpdate("CREATE DATABASE " + databaseName);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
    }
    /**
     * 通过 jdbcUrl 和数据库名称 返回对应数据库是否存在的查询语句
     * @param url          jdbc 连接
     * @param databaseName 数据库名称
     * @return             对应数据库是否存在的查询语句
     */
    public static String getExistsDatabasesSql(String url, String databaseName) {
        String existsDatabaseSql = "";
        if (url.startsWith("jdbc:mysql")) {
            existsDatabaseSql = "SELECT 1 FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '" + databaseName + "';";
        } else if (url.startsWith("jdbc:cobar")) {
            existsDatabaseSql = "SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '" + databaseName + "';";
        } else if (url.startsWith("jdbc:mariadb")) {
            existsDatabaseSql = "SELECT 1 FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '" + databaseName + "';";
        } else if (url.startsWith("jdbc:oracle")) {
            existsDatabaseSql = "SELECT 1 FROM dual";
        } else if (url.startsWith("jdbc:sqlserver")) {
            existsDatabaseSql = "SELECT 1 FROM sys.databases WHERE name = '" + databaseName + "';";
        } else if (url.startsWith("jdbc:postgresql")) {
            existsDatabaseSql = "SELECT 1 FROM pg_database WHERE datname = '" + databaseName + "';";
        } else if (url.startsWith("jdbc:hsqldb")) {
            existsDatabaseSql = "SELECT 1 FROM INFORMATION_SCHEMA.SYSTEM_SCHEMAS WHERE SCHEMA_NAME = '" + databaseName + "';";
        } else if (url.startsWith("jdbc:db2")) {
            existsDatabaseSql = "SELECT 1 FROM syscat.databases WHERE name = '" + databaseName + "';";
        } else if (url.startsWith("jdbc:sqlite")) {
            existsDatabaseSql = "SELECT 1 FROM sqlite_master WHERE type='table' AND name='" + databaseName + "';";
        } else if (url.startsWith("jdbc:h2")) {
            existsDatabaseSql = "SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = '" + databaseName + "';";
        } else if (url.startsWith("jdbc:lealone")) {
            existsDatabaseSql = "SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = '" + databaseName + "';";
        } else if (url.startsWith("jdbc:dm")) {
            existsDatabaseSql = "select 1 table_schema from all_objects where object_type='SCH' and object_name = '" + databaseName + "';";
        } else if (url.startsWith("jdbc:xugu")) {
            existsDatabaseSql = "SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = '" + databaseName + "';";
        } else if (url.startsWith("jdbc:kingbase")) {
            existsDatabaseSql = "SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = '" + databaseName + "';";
        } else if (url.startsWith("jdbc:phoenix")) {
            existsDatabaseSql = "SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = '" + databaseName + "';";
        } else if (url.startsWith("jdbc:zenith")) {
            existsDatabaseSql = "SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = '" + databaseName + "';";
        } else if (url.startsWith("jdbc:gbase")) {
            existsDatabaseSql = "SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = '" + databaseName + "';";
        }
        else {
            // 处理未知数据库类型的逻辑
            System.out.println("Unknown database type: " + url);
        }
        return existsDatabaseSql;
    }
    /**
     * 根据 jdbc url 获取驱动名称
     * @param url jdbc 连接
     * @return    jdbc 连接对应的驱动名称
     */
    public static String getDriverNameFromUrl(String url) {
        String driverName = null;
        if (url.startsWith("jdbc:mysql")) {
            driverName = "com.mysql.cj.jdbc.Driver";
        } else if (url.startsWith("jdbc:cobar")) {
            driverName = "cobar.jdbc.driver.CobarDriver";
        } else if (url.startsWith("jdbc:mariadb")) {
            driverName = "org.mariadb.jdbc.Driver";
        } else if (url.startsWith("jdbc:oracle")) {
            driverName = "oracle.jdbc.driver.OracleDriver";
        } else if (url.startsWith("jdbc:sqlserver")) {
            driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        } else if (url.startsWith("jdbc:sqlserver2012")) {
            driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        } else if (url.startsWith("jdbc:postgresql")) {
            driverName = "org.postgresql.Driver";
        } else if (url.startsWith("jdbc:hsqldb")) {
            driverName = "org.hsqldb.jdbc.JDBCDriver";
        } else if (url.startsWith("jdbc:db2")) {
            driverName = "com.ibm.db2.jcc.DB2Driver";
        } else if (url.startsWith("jdbc:sqlite")) {
            driverName = "org.sqlite.JDBC";
        } else if (url.startsWith("jdbc:h2")) {
            driverName = "org.h2.Driver";
        } else if (url.startsWith("jdbc:lealone")) {
            driverName = "org.lealone.driver.LealoneDriver";
        } else if (url.startsWith("jdbc:dm")) {
            driverName = "dm.jdbc.driver.DmDriver";
        } else if (url.startsWith("jdbc:xugu")) {
            driverName = "xugu.jdbc.XuguDriver";
        } else if (url.startsWith("jdbc:kingbase")) {
            driverName = "kingbase.Driver";
        } else if (url.startsWith("jdbc:phoenix")) {
            driverName = "org.apache.phoenix.jdbc.PhoenixDriver";
        } else if (url.startsWith("jdbc:zenith")) {
            driverName = "com.huawei.gauss.jdbc.ZenithDriver";
        } else if (url.startsWith("jdbc:gbase")) {
            driverName = "com.gbase.jdbc.Driver";
        } else if (url.startsWith("jdbc:gbaseDBT-SQLI")) {
            driverName = "com.gbasedbt.jdbc.IfxDriver";
        } else if (url.startsWith("jdbc:informix-sqli")) {
            driverName = "com.informix.jdbc.IfxDriver";
        } else {
            driverName = "Unknown";
        }
        return driverName;
    }
    /**
     * 通过 jdbc 连接获取相关的信息的集合
     * @param jdbcUrl jdbc 连接
     * @return        返回相关信息的集合 具体值：{@link JdbcInfoEnum}
     */
    public static Map<String, String> getJdbcUrlInfo(String jdbcUrl) {
        Assert.isFalse(StringUtils.isBlank(jdbcUrl), "Error: The jdbcUrl is Null, Cannot read database type");
        String url = jdbcUrl.toLowerCase();
        Map<String, String> jdbcInfo = new HashMap();
        if (url.contains(":mysql:") || url.contains(":cobar:")) {
            Pattern pattern = Pattern.compile("jdbc:(.*?):\\/\\/([^:/]+)(:[0-9]+)?\\/([^?]+)\\??(.*)");
            Matcher matcher = pattern.matcher(jdbcUrl);
            if (matcher.find()) {
                jdbcInfo.put(DATABASES_NAME.getInfoName(), matcher.group(4));
                jdbcInfo.put(PARAM_VALUES.getInfoName(), matcher.group(5));
                jdbcInfo.put(ORIGINAL_URL.getInfoName(), "jdbc:" + matcher.group(1) + "://" + matcher.group(2) + matcher.group(3) + "?");
            } else {
                jdbcInfo.put(EXISTS_DATABASES_FOR_URL.getInfoName(), "false");
            }
        } else if (url.contains(":mariadb:")) {
            Pattern pattern = Pattern.compile("jdbc:mariadb://([^:/]+)(:[0-9]+)?/([^?]+)\\??(.*)");
            Matcher matcher = pattern.matcher(jdbcUrl);
            if (matcher.find()) {
                jdbcInfo.put(DATABASES_NAME.getInfoName(), matcher.group(3));
                jdbcInfo.put(PARAM_VALUES.getInfoName(), matcher.group(4));
                jdbcInfo.put(ORIGINAL_URL.getInfoName(), "jdbc:mariadb://" + matcher.group(1) + matcher.group(2));
            } else {
                jdbcInfo.put(EXISTS_DATABASES_FOR_URL.getInfoName(), "false");
            }
        } else if (url.contains(":oracle:")) {
            Pattern pattern = Pattern.compile("jdbc:oracle:thin:@([^:/]+)(:[0-9]+)?/([^;]+);?(.*)");
            Matcher matcher = pattern.matcher(jdbcUrl);
            if (matcher.find()) {
                jdbcInfo.put(DATABASES_NAME.getInfoName(), matcher.group(3));
                jdbcInfo.put(PARAM_VALUES.getInfoName(), matcher.group(4));
                jdbcInfo.put(ORIGINAL_URL.getInfoName(), "jdbc:oracle:thin:@" + matcher.group(1) + matcher.group(2));
            } else {
                jdbcInfo.put(EXISTS_DATABASES_FOR_URL.getInfoName(), "false");
            }
        } else if (url.contains(":sqlserver:") || url.contains(":microsoft:") || url.contains(":sqlserver2012:")) {
            Pattern pattern = Pattern.compile("jdbc:sqlserver://([^:/]+)(:[0-9]+)?;DatabaseName=([^;]+);?(.*)");
            Matcher matcher = pattern.matcher(jdbcUrl);
            if (matcher.find()) {
                jdbcInfo.put(DATABASES_NAME.getInfoName(), matcher.group(3));
                jdbcInfo.put(PARAM_VALUES.getInfoName(), matcher.group(4));
                jdbcInfo.put(ORIGINAL_URL.getInfoName(), "jdbc:sqlserver://" + matcher.group(1) + matcher.group(2) + ";");
            } else {
                jdbcInfo.put(EXISTS_DATABASES_FOR_URL.getInfoName(), "false");
            }
        } else if (url.contains(":postgresql:")) {
            Pattern pattern = Pattern.compile("jdbc:postgresql://([^:/]+)(:[0-9]+)?/([^?]+)\\??(.*)");
            Matcher matcher = pattern.matcher(jdbcUrl);
            if (matcher.find()) {
                jdbcInfo.put(DATABASES_NAME.getInfoName(), matcher.group(3));
                jdbcInfo.put(PARAM_VALUES.getInfoName(), matcher.group(4));
                jdbcInfo.put(ORIGINAL_URL.getInfoName(), "jdbc:postgresql://" + matcher.group(1) + matcher.group(2));
            } else {
                jdbcInfo.put(EXISTS_DATABASES_FOR_URL.getInfoName(), "false");
            }
        } else if (url.contains(":hsqldb:")) {
            Pattern pattern = Pattern.compile("jdbc:hsqldb:(file:|mem:|res:)([^;]+);?(.*)");
            Matcher matcher = pattern.matcher(jdbcUrl);
            if (matcher.find()) {
                jdbcInfo.put(DATABASES_NAME.getInfoName(), matcher.group(2));
                jdbcInfo.put(PARAM_VALUES.getInfoName(), matcher.group(3));
                jdbcInfo.put(ORIGINAL_URL.getInfoName(), "jdbc:hsqldb:" + matcher.group(1) + matcher.group(2));
            } else {
                jdbcInfo.put(EXISTS_DATABASES_FOR_URL.getInfoName(), "false");
            }
        } else if (url.contains(":db2:")) {
            Pattern pattern = Pattern.compile("jdbc:db2://([^:/]+)(:[0-9]+)?/([^:]+):([^;]+);?(.*)");
            Matcher matcher = pattern.matcher(jdbcUrl);
            if (matcher.find()) {
                jdbcInfo.put(DATABASES_NAME.getInfoName(), matcher.group(4));
                jdbcInfo.put(PARAM_VALUES.getInfoName(), matcher.group(5));
                jdbcInfo.put(ORIGINAL_URL.getInfoName(), "jdbc:db2://" + matcher.group(1) + matcher.group(2) + "/" + matcher.group(3));
            } else {
                jdbcInfo.put(EXISTS_DATABASES_FOR_URL.getInfoName(), "false");
            }
        } else if (url.contains(":sqlite:")) {
            Pattern pattern = Pattern.compile("jdbc:sqlite:(.+)");
            Matcher matcher = pattern.matcher(jdbcUrl);
            if (matcher.find()) {
                jdbcInfo.put(DATABASES_NAME.getInfoName(), matcher.group(1));
                jdbcInfo.put(PARAM_VALUES.getInfoName(), "");
                jdbcInfo.put(ORIGINAL_URL.getInfoName(), "jdbc:sqlite:" + matcher.group(1));
            } else {
                jdbcInfo.put(EXISTS_DATABASES_FOR_URL.getInfoName(), "false");
            }
        } else if (url.contains(":h2:")) {
        } else if (url.contains(":lealone:")) {
        } else if (regexFind(":dm\\d*:", url)) {
            Pattern pattern = Pattern.compile("jdbc:dm://([^:/]+)(:[0-9]+)?/([^;]+);?(.*)");
            Matcher matcher = pattern.matcher(jdbcUrl);
            if (matcher.find()) {
                jdbcInfo.put(DATABASES_NAME.getInfoName(), matcher.group(3));
                jdbcInfo.put(PARAM_VALUES.getInfoName(), matcher.group(4));
                jdbcInfo.put(ORIGINAL_URL.getInfoName(), "jdbc:dm://" + matcher.group(1) + matcher.group(2));
            } else {
                jdbcInfo.put(EXISTS_DATABASES_FOR_URL.getInfoName(), "false");
            }
        } else if (url.contains(":xugu:")) {
            Pattern  pattern = Pattern.compile("jdbc:xugu://([^:/]+)(:[0-9]+)?/([^?]+)\\??(.*)");
            Matcher matcher = pattern.matcher(jdbcUrl);
            if (matcher.find()) {
                jdbcInfo.put(DATABASES_NAME.getInfoName(), matcher.group(3));
                jdbcInfo.put(PARAM_VALUES.getInfoName(), matcher.group(4));
                jdbcInfo.put(ORIGINAL_URL.getInfoName(), "jdbc:dm://" + matcher.group(1) + matcher.group(2));
            } else {
                jdbcInfo.put(EXISTS_DATABASES_FOR_URL.getInfoName(), "false");
            }
        } else if (regexFind(":kingbase\\d*:", url)) {
            Pattern pattern = Pattern.compile("jdbc:kingbase://([^:/]+)(:[0-9]+)?/([^?]+)\\??(.*)");
            Matcher matcher = pattern.matcher(jdbcUrl);
            if (matcher.find()) {
                jdbcInfo.put(DATABASES_NAME.getInfoName(), matcher.group(3));
                jdbcInfo.put(PARAM_VALUES.getInfoName(), matcher.group(4));
                jdbcInfo.put(ORIGINAL_URL.getInfoName(), "jdbc:dm://" + matcher.group(1) + matcher.group(2));
            } else {
                jdbcInfo.put(EXISTS_DATABASES_FOR_URL.getInfoName(), "false");
            }
        } else if (url.contains(":phoenix:")) {
            Pattern pattern = Pattern.compile("jdbc:kingbase://([^:/]+)(:[0-9]+)?/([^?]+)\\??(.*)");
            Matcher matcher = pattern.matcher(jdbcUrl);
            if (matcher.find()) {
                jdbcInfo.put(DATABASES_NAME.getInfoName(), matcher.group(3));
                jdbcInfo.put(PARAM_VALUES.getInfoName(), matcher.group(4));
                jdbcInfo.put(ORIGINAL_URL.getInfoName(), "jdbc:dm://" + matcher.group(1) + matcher.group(2));
            } else {
                jdbcInfo.put(EXISTS_DATABASES_FOR_URL.getInfoName(), "false");
            }
        } else if (url.contains(":zenith:")) {
        } else if (url.contains(":gbase:")) {
        } else if (url.contains(":gbasedbt-sqli:") || url.contains(":informix-sqli:")) {
            Pattern pattern = Pattern.compile("jdbc:informix-sqli://([^:/]+)(:[0-9]+)?/([^:]+):informixServer=([^;]+);?(.*)");
            Matcher matcher = pattern.matcher(jdbcUrl);
            if (matcher.find()) {
                jdbcInfo.put(DATABASES_NAME.getInfoName(), matcher.group(3));
                jdbcInfo.put(PARAM_VALUES.getInfoName(), matcher.group(5));
                jdbcInfo.put(ORIGINAL_URL.getInfoName(), "jdbc:informix-sqli://" + matcher.group(1) + matcher.group(2) + "/" + matcher.group(3) + ":informixServer=" + matcher.group(4));
            } else {
                jdbcInfo.put(EXISTS_DATABASES_FOR_URL.getInfoName(), "false");
            }
        } else if (url.contains(":ch:") || url.contains(":clickhouse:")) {
        } else if (url.contains(":oscar:")) {
        } else if (url.contains(":sybase:")) {
            Pattern pattern = Pattern.compile("jdbc:sybase:Tds:(.+):(\\d+)/(\\w+);?(.*)");
            Matcher matcher = pattern.matcher(jdbcUrl);
            if (matcher.find()) {
                jdbcInfo.put(DATABASES_NAME.getInfoName(), matcher.group(3));
                jdbcInfo.put(PARAM_VALUES.getInfoName(), matcher.group(4));
                jdbcInfo.put(ORIGINAL_URL.getInfoName(), "jdbc:sybase:Tds:" + matcher.group(1) + ":" + matcher.group(2) + "/" + matcher.group(3));
            } else {
                jdbcInfo.put(EXISTS_DATABASES_FOR_URL.getInfoName(), "false");
            }
        } else if (url.contains(":oceanbase:")) {
        } else if (url.contains(":highgo:")) {
        } else if (url.contains(":cubrid:")) {
            Pattern pattern = Pattern.compile("jdbc:cubrid:([^:]+):(\\d+):(\\w+):(\\w+);?(.*)");
            Matcher matcher = pattern.matcher(jdbcUrl);
            if (matcher.find()) {
                jdbcInfo.put(DATABASES_NAME.getInfoName(), matcher.group(3));
                jdbcInfo.put(PARAM_VALUES.getInfoName(), matcher.group(5));
                jdbcInfo.put(ORIGINAL_URL.getInfoName(), "jdbc:cubrid:" + matcher.group(1) + ":" + matcher.group(2) + ":" + matcher.group(3) + ":" + matcher.group(4));
            } else {
                jdbcInfo.put(EXISTS_DATABASES_FOR_URL.getInfoName(), "false");
            }
        } else if (url.contains(":goldilocks:")) {
        } else if (url.contains(":csiidb:")) {
        } else if (url.contains(":sap:")) {
        } else if (url.contains(":impala:")) {
        } else if (url.contains(":vertica:")) {
        } else if (url.contains(":xcloud:")) {
        } else if (url.contains(":firebirdsql:")) {
        } else if (url.contains(":redshift:")) {
        } else if (url.contains(":opengauss:")) {
        } else if (url.contains(":taos:") || url.contains(":taos-rs:")) {
        } else if (url.contains(":informix")) {
        } else if (url.contains(":sinodb")) {
        } else if (url.contains(":uxdb:")) {
        } else {
            log.warn("The jdbcUrl is " + jdbcUrl + ", Mybatis Plus Cannot Read Database type or The Database's Not Supported!");
        }
        return jdbcInfo;
    }
    /**
     * 正则匹配
     *
     * @param regex 正则
     * @param input 字符串
     * @return      验证成功返回 true，验证失败返回 false
     */
    public static boolean regexFind(String regex, CharSequence input) {
        if (null == input) {
            return false;
        }
        return Pattern.compile(regex).matcher(input).find();
    }
}
以上就是实现的全部代码
# 使用
从上面的 application.yml 配置文件中可以看到 数据源连接的数据库
mysql -> scaffold4
sqlserver -> scaffold3
mysql
SELECT 1 FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'scaffold4'
null
sqlserver
SELECT 1 FROM sys.databases WHERE name = 'scaffold3'
null
得到的效果是自动创建数据库，并初始化数据库执行 sql 语句建表插入数据
启动的日志：
2024-04-23 19:57:31.338 |  INFO 34392 | main [TID: N/A] liquibase.database                       | Set default schema name to dbo
2024-04-23 19:57:31.501 |  INFO 34392 | main [TID: N/A] liquibase.lockservice                    | Successfully acquired change log lock
2024-04-23 19:57:33.257 |  INFO 34392 | main [TID: N/A] liquibase.changelog                      | Creating database history table with name: DATABASECHANGELOG
2024-04-23 19:57:33.261 |  INFO 34392 | main [TID: N/A] liquibase.changelog                      | Reading from DATABASECHANGELOG
Running Changeset: db/sqlserver/slave/changelog-master.xml::1.0.1-20240318-001::Tz
2024-04-23 19:57:36.718 |  INFO 34392 | main [TID: N/A] liquibase.changelog                      | SQL in file classpath:db/sqlserver/slave/scaffold_init.sql executed
2024-04-23 19:57:36.721 |  INFO 34392 | main [TID: N/A] liquibase.changelog                      | ChangeSet db/sqlserver/slave/changelog-master.xml::1.0.1-20240318-001::Tz ran successfully in 3400ms
2024-04-23 19:57:36.729 |  INFO 34392 | main [TID: N/A] liquibase.lockservice                    | Successfully released change log lock
2024-04-23 19:57:36.774 |  INFO 34392 | main [TID: N/A] liquibase.lockservice                    | Successfully acquired change log lock
2024-04-23 19:57:37.565 |  INFO 34392 | main [TID: N/A] liquibase.changelog                      | Creating database history table with name: scaffold4.DATABASECHANGELOG
2024-04-23 19:57:37.580 |  INFO 34392 | main [TID: N/A] liquibase.changelog                      | Reading from scaffold4.DATABASECHANGELOG
Running Changeset: db/mysql/master/changelog-master.xml::1.0.0-20240318-001::Tz
2024-04-23 19:57:40.119 |  INFO 34392 | main [TID: N/A] liquibase.changelog                      | SQL in file classpath:db/mysql/master/scaffold_init.sql executed
2024-04-23 19:57:40.119 |  INFO 34392 | main [TID: N/A] liquibase.changelog                      | ChangeSet db/mysql/master/changelog-master.xml::1.0.0-20240318-001::Tz ran successfully in 2494ms
Running Changeset: db/mysql/master/changelog-master.xml::1.0.0-20240318-002::Tz
2024-04-23 19:57:40.291 |  INFO 34392 | main [TID: N/A] liquibase.changelog                      | SQL in file classpath:db/mysql/master/pay_wallet.sql executed
2024-04-23 19:57:40.291 |  INFO 34392 | main [TID: N/A] liquibase.changelog                      | ChangeSet db/mysql/master/changelog-master.xml::1.0.0-20240318-002::Tz ran successfully in 169ms
2024-04-23 19:57:40.295 |  INFO 34392 | main [TID: N/A] liquibase.lockservice                    | Successfully released change log lock
2024-04-23 19:57:40.330 |  INFO 34392 | main [TID: N/A] liquibase.lockservice                    | Successfully acquired change log lock
2024-04-23 19:57:40.394 |  INFO 34392 | main [TID: N/A] liquibase.changelog                      | Reading from scaffold4.DATABASECHANGELOG
2024-04-23 19:57:40.423 |  INFO 34392 | main [TID: N/A] liquibase.lockservice                    | Successfully released change log lock
2024-04-23 19:57:44.034 |  INFO 34392 | main [TID: N/A] org.redisson.Version                     | Redisson 3.18.0
2024-04-23 19:57:47.644 |  INFO 34392 | redisson-netty-2-5 [TID: N/A] o.r.c.pool.MasterPubSubConnectionPool    | 1 connections initialized for 127.0.0.1/127.0.0.1:6379
2024-04-23 19:57:47.674 |  INFO 34392 | redisson-netty-2-19 [TID: N/A] o.r.c.pool.MasterConnectionPool          | 24 connections initialized for 127.0.0.1/127.0.0.1:6379


可以看到成功执行了初始化sql

# 查看结果
liquibase 更新数据库会有执行更新语句的记录
mysql
SELECT 1 FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'scaffold4'
1
select * from DATABASECHANGELOG
ID	AUTHOR	FILENAME	DATEEXECUTED	ORDEREXECUTED	EXECTYPE	MD5SUM	DESCRIPTION	COMMENTS	TAG	LIQUIBASE	CONTEXTS	LABELS	DEPLOYMENT_ID
1.0.0-20240318-001	Tz	db/mysql/master/changelog-master.xml	2024-03-18 19:46:15	1	EXECUTED	8:2bbd175ecbb0c52cddaa817632a1d55f	sqlFile			4.9.1			0762373118
1.0.0-20240318-002	Tz	db/mysql/master/changelog-master.xml	2024-03-18 19:46:16	2	EXECUTED	8:a9390b23c590523c591203a644bf6ed0	sqlFile			4.9.1			0762373118
select * from databasechangeloglock
ID	LOCKED	LOCKGRANTED	LOCKEDBY
1	0		
sqlserver
SELECT 1 FROM sys.databases WHERE name = 'scaffold4'
1
select * from scaffold4.dbo.DATABASECHANGELOG
ID	AUTHOR	FILENAME	DATEEXECUTED	ORDEREXECUTED	EXECTYPE	MD5SUM	DESCRIPTION	COMMENTS	TAG	LIQUIBASE	CONTEXTS	LABELS	DEPLOYMENT_ID
1.0.1-20240318-001	Tz	db/sqlserver/slave/changelog-master.xml	2024-04-23 19:57:36.723	1	EXECUTED	8:b7e4c6627ab3bbb057fc499e75ea8f00	sqlFile		NULL	4.9.1	NULL	NULL	3873453275
select * from scaffold4.dbo.DATABASECHANGELOGLOCK
ID	LOCKED	LOCKGRANTED	LOCKEDBY
1	0	NULL	NULL
通过上面的查询结果可以知道执行成功了
# 扩展
  1. 如果遇到要创建存储过程或函数的语句改如何呢，比如 mysql 创建存储过程，如果用普通的方式会报如下错误：
    Caused by: java.sql.SQLSyntaxErrorException: 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 PROCEDURE proc_demo (IN pageNo INT, IN pageSize INT)
    BEGI' at line 1
    表示不支持 DELIMITER；
    如果去除 DELIMITER 也无法创建
    解决办法：
    使用 createProcedure 来表示指定的 sql 文件是存储过程 如下
    <changeSet id="1.0.0-20240625-005" author="Tz">
    	<createProcedure path="classpath:db/mysql/master/jimureport_proc.sql"/>
    </changeSet>
    然后实际的内容就可以去除 DELIMITER
    CREATE PROCEDURE `proc_demo`(IN pageNo INT,IN pageSize INT)
    BEGIN
    		DECLARE begin_no INT;
    		SET begin_no = (pageNo-1)*pageSize;
    		-- 分页查询列表
    		SELECT * FROM rep_demo
    		WHERE id >= (
    			SELECT id FROM rep_demo
    			ORDER BY id ASC
    			LIMIT begin_no,1
    		)
    		ORDER BY id ASC
    		LIMIT pageSize;
    END