测开工具：spring boot 实现同步数据库表结构（持续更新）

一、使用场景

一个项目，有多套开发环境。有一套标准的数据库，不同的开发环境，有各自的一套数据库。

标准数据库的表结构经常发生变化，不同的开发环境中的数据库，需要与标准数据库的表结构保持一致。当标准数据库表结构发生变化时，其他开发环境的数据库需要进行表结构的同步，与标准数据库保持一致。

二、MySQL 索引

MySQL 索引：

是一个单独的、存储在 磁盘 上的 数据库结构 ，包含着对数据表里 所有记录的 引用指针。

  

MySQL中索引的存储类型有两种：

即 BTree 和 Hash。

索引是在存储引擎中实现的：

（MySQL 的存储引擎有：InnoDB、MyISAM、Memory、Heap）

• InnoDB / MyISAM 只支持 BTree 索引
• Memory / Heap 都支持 BTree 和 Hash 索引

2.1 MYSQL 索引 的分类

1、普通索引 和 唯一索引

普通索引：MySQL 中的基本索引类型，允许在定义索引的列中插入 重复值 和 空值

唯一索引：要求索引列的值必须 唯一，但允许 有空值

如果是组合索引，则列值的组合必须 唯一
主键索引是一种特殊的唯一索引，不允许 有空值

2、单列索引 和 组合索引

单列索引：一个索引只包含单个列，一个表可以有多个单列索引
组合索引：在表的 多个字段 组合上 创建的 索引
只有在查询条件中使用了这些字段的 左边字段 时，索引才会被使用（最左前缀原则）

3、全文索引

全文索引 的类型为 fulltext
在定义索引的 列上 支持值的全文查找，允许在这些索引列中插入 重复值 和 空值
全文索引 可以在 char、varchar 和 text 类型的 列 上创建

4、空间索引

空间索引 是对 空间数据类型 的字段 建立的索引

MySQL中的空间数据类型有4种，分别是 Geometry、Point、Linestring 和 Polygon

MySQL 使用 Spatial 关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引

创建空间索引的列，不允许为空值，且只能在 MyISAM 的表中创建。

5、前缀索引

在 char、varchar 和 text 类型的 列 上创建索引时，可以指定索引 列的长度

三、information_schema库

information_schema库 ：

用于存储数据库元数据(关于数据的数据)，例如数据库名、表名、列的数据类型、访问权限等。

1、当我们对比，2个数据库是否一样时，我们只需要对比这两个数据库名称是否一致就可以了。

2、当我们对比，2个表是否一致时，我们需要去

1、表里，所有的字段属性

2、表里，索引。

我们通过这两个条件，来判断表与表是否一致，具体哪里不一致。

表information_schema.COlUMNS 记录了，某个表中，所有字段的属性。

我们在information_schema.COlUMNS，中查看表Student中，所有字段的属性。

在information_schema.statistics 表中，可以查看某个表下所有的索引。

在information_schema.SCHEMATA表中，可以查看这个实例下，都存在哪些数据库。

select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME from information_schema.SCHEMATA

这个命令，就是查看我们实例下，所有的数据库。其实就是相当于

show databases; 

在information_schema.tables表中，可以查看某个数据库下，都存在哪些表。

select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.tables where schema_name = #{dbName}

其实就相当于命令：

use chen;
show tables;

四、整体思路

如数据库A为标准数据库。数据库B为我们本地的数据库。当数据库A表结构发生变化时，数据库B的表结构需要与A保持一致。

4.1 同步规则

我们拿本地的库与标准库做比较，

1、同步库：

1.1 将标准库中存在的库，但是本地不存在的库，同步到本地

1.2 标准库与本地库，都存在的库，只需要同步库里的表就可以了

1.3 在标准库中不存在的库，但是本地有的库，这种情况我们不进行考虑

2、同步表：

2.1 将标准库中存在的表，但是本地不存在的库，同步到本地

2.2 将标准库与本地库同时存在的表，但是表的结构不一样，将标准库的表结构同步到本地

2.3 同一个表中，本地表比标准表多余的字段，我们不考虑。

4.2 同步库

举例：

标准数据库A

 这个数据库实例下，一共有9个库。其中有5个库：

"information_schema","performance_schema","sys","mysql","test"

属于系统自带的库，我们进行同步时，不需要进行同步。我们只同步除了这5个系统之外的所有库。

数据库的属性：

1、只有数据库的名称。

所以，在对比标准库与本地库时，只需要比较数据库的名称就可以了。

标准库中有的库名，本地库没有该库名，我们在本地新增该数据库。

标准库与本地库，同时存在的库名，我们需要去校验同步库里面的表。

4.3 同步表

1 将标准库中存在的表，但是本地不存在的库，同步到本地

2 将标准库与本地库同时存在的表，但是表的结构不一样，将标准库的表结构同步到本地

3 同一个表中，本地表比标准表多余的字段，我们不考虑。

判断表与表是否一致时，我们需要比较2个条件。

1、表里，所有的字段属性

2、表里，索引。

我们通过这两个条件，来判断表与表是否一致，具体哪里不一致。

4.3.1 字段的属性：

 查看表information_schema.COlUMNS

每个列，对应一条数据。

每个列数据，我们需要取哪些属性值？

    private String tableSchema;
    private String tableName;
    private String columnName;
    private String columnType;
    private String columnComment;
    private String dataType;
    private String columnDefault;
    private String isNullable;

 4.3.2 mysql索引

 在information_schema.statistics 表中，可以查看某个表下所有的索引。

一个索引，就是一条数据。一个索引，都有哪些属性值呢？

    private String tableSchema;
    private String tableName;
    private String indexName;
    private int seqInIndex;
    private String columnName;
    private int nonUnique;
    private String indexType;

注意：

索引不能进行修改，只能先删除，后增加。

4.4 整体思路

1、我们拿本地的库与标准库做比较

2、先同步库

2.1 找到库与库的区别，生成对应的sql

2.2 去执行同步sql

3、再同步表

3.1 找到表与表的区别，生成对应的sql

3.2  去执行同步sql

4.5 最终效果

我们使用spring boot最终提供三个接口。

/instance 接口，同步两个实例。

/db 接口，只同步相应的数据库

/table 接口，只同步指定的表

五、 技术点

1、区分表与表，哪里不一致。哪些字段是新增的，哪些字段是修改的。

对索引的判断，哪些索引是新增的。这里技术难度不大，但是琐碎。

2、对于数据库的连接，我们使用的是mybatis。不同的是，以往spring boot 应用mybatis

在项目配置文件application.yml中，配置数据库的连接信息。

但是我们本次数据库连接信息需要用户传入，就不能使用application.yml配置。

六、项目代码、 项目整体结构

七、 pom依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.chenshuia.study</groupId>
    <artifactId>mysql-sync</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mysql-sync</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.20</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.6</version>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>29.0-jre</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

八、 controller层

对外提供的三个同步接口。

/instance 接口，同步两个实例。

/db 接口，只同步相应的数据库

/table 接口，只同步指定的表

package com.chenshuia.study.mysqlsync.controller;

import com.chenshuia.study.mysqlsync.bean.DBSyncInfo;
import com.chenshuia.study.mysqlsync.bean.ResultMsg;
import com.chenshuia.study.mysqlsync.bean.SyncInfo;
import com.chenshuia.study.mysqlsync.bean.TableSyncInfo;
import com.chenshuia.study.mysqlsync.service.SyncService;
import org.apache.logging.log4j.util.Strings;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import java.util.Objects;

@RequestMapping("/sync")
@RestController
public class SyncController {

    @Autowired
    private SyncService syncService;

    // 同步实例
    @RequestMapping(value = "/instance",method = RequestMethod.POST)
    public ResultMsg sync(@RequestBody SyncInfo syncInfo){
        // 参数校验
        try {
            syncInfo.verify();
            syncService.syncInstance(syncInfo);
            return ResultMsg.success();
        } catch (Exception e){
            e.printStackTrace();
            return ResultMsg.fail(ResultMsg.FAILED_CODE,e.getMessage());
        }

    }

    // 同步库
    @RequestMapping(value = "/db",method = RequestMethod.POST)
    public ResultMsg sync(@RequestBody DBSyncInfo dbSyncInfo){
        try {
            dbSyncInfo.verify();
            syncService.syncDB(dbSyncInfo);
            return ResultMsg.success();

        } catch (Exception e){
            e.printStackTrace();
            return ResultMsg.fail(ResultMsg.FAILED_CODE,e.getMessage());
        }
        
    }

    // 同步表
    @RequestMapping(value = "/table",method = RequestMethod.POST)
    public ResultMsg sync(@RequestBody TableSyncInfo tableSyncInfo){
        try {
            // 校验参数
            tableSyncInfo.verify();
            // 调用业务，处理数据
            syncService.syncTable(tableSyncInfo);
            return ResultMsg.success();
        } catch (Exception e){
            e.printStackTrace();
            return ResultMsg.fail(ResultMsg.FAILED_CODE,e.getMessage());
        }

    }

}

1、三个接口，都是post方法

2、具体的同步方法，都在

    @Autowired
    private SyncService syncService;

通过spring注入

3、同步实例时，需要传入所有的信息。本地库与标准库的连接信息。

同步库，需要传入2个库的连接信息，与数据库的名称。

同步表，需要传入2个库的连接信息，与数据库的名称、表的名称。

4、我们在同步数据库之前，先对用户传参进行了一个校验。

九、 bean层

 bean层存放了整个项目，需要的所有实例。

9.1 返回结果的实例ResultMsg<T> 

定义了一个返回结果的实例，用到了范型。（没有什么逻辑）

package com.chenshuia.study.mysqlsync.bean;

import lombok.AllArgsConstructor;
import lombok.Data;
import org.omg.CORBA.PUBLIC_MEMBER;

@Data
@AllArgsConstructor
public class ResultMsg<T> {
    private String code;
    private String msg;
    private T data;

    public static final String SUCCESS_CODE = "200";
    public static final String FAILED_CODE = "999";
    public static final String SUCCESS_MSG = "success";
    public static final String FAILED_MSG = "failed";

    public ResultMsg(String code, String msg) {
        this.code = code;
        this.msg = msg;
    }

    public static <T> ResultMsg<T> success(T t){
        return new ResultMsg<>(SUCCESS_CODE,SUCCESS_MSG,t);
    }

    public static ResultMsg success(){
        return new ResultMsg<>(SUCCESS_CODE,SUCCESS_MSG);
    }

    public static ResultMsg fail(){
        return new ResultMsg<>(FAILED_CODE,FAILED_MSG);
    }

    public static ResultMsg fail(String code,String msg){
        return new ResultMsg<>(code,msg);
    }
}

9.2 数据库连接信息（用户传入信息）

一共有4个实体类。ConnectInfo、SyncInfo 

ConnectInfo

package com.chenshuia.study.mysqlsync.bean;

import com.chenshuia.study.mysqlsync.util.VerifyUtil;
import com.mysql.cj.jdbc.Driver;
import lombok.Data;

@Data
public class ConnectInfo {
    /*
    数据库链接信息
     */
    private String driver = Driver.class.getName();
    private String url ;
    private String userName ;
    private String password ;

    public void verify(){
        VerifyUtil.verifyString(url,userName,password,driver);
    }

}

SyncInfo 

package com.chenshuia.study.mysqlsync.bean;

import lombok.Data;

import java.util.Objects;

@Data
public class SyncInfo {
    /*
    进行同步的信息。2个数据库的链接方式，同步的库和表
     */

    private ConnectInfo src;
    private ConnectInfo dst;

    public void verify(){
        // 判断属性src与dst 是否为空
        Objects.requireNonNull(src);
        Objects.requireNonNull(dst);
        // 判断对象src的属性是否为空
        src.verify();
        dst.verify();
    }
}