MySQL同步表结构

MySQL同步表结构

最近在工作当中遇到两个环境的数据库表结构有差异且表数量较多，人工对比需要花费比较多的时间。于是编写代码对两个库的表结构进行对比，并对缺少字段的表生成DDL。

一、 多数据源

由于对比的是两个不同的数据源需要使用到多数据源；这里我直接使用配置相对简单的Mybatis-Plus的多数据源减少编写多数据源配置的时间。

1. 引入mybatis-plus依赖

<!-- mybatis-plus-boot -->
<dependency>
	 	<groupId>com.baomidou</groupId>
		 <artifactId>mybatis-plus-boot-starter</artifactId>
</dependency>
 <!-- mybatis-plus动态数据源依赖 -->
 <dependency>
  		<groupId>com.baomidou</groupId>
  		<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
  		<version>3.5.2</version>
 </dependency>

2. 多数据源配置

   spring:
     datasource:
    dynamic:
         primary: master #设置默认的数据源或者数据源组,默认值即为master
         strict: true #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
         datasource:
           master:
             url: jdbc:mysql://IP:3306/master?characterEncoding=utf8&useSSL=true&useSSL=false&allowMultiQueries=true&serverTimezone=Asia/Shanghai
             username: master
             password: password
             driver-class-name: com.mysql.cj.jdbc.Driver # 3.2.0开始支持SPI可省略此配置
           slave_1:
             url: jdbc:mysql://IP:3306/slave_1?characterEncoding=utf8&useSSL=true&useSSL=false&allowMultiQueries=true&serverTimezone=Asia/Shanghai
             username: slave_1
             password: password
             driver-class-name: com.mysql.cj.jdbc.Driver

3. 切换数据源

   使用@DS切换数据源：@DS(“slave”)

二、MySQL查询元数据

MySQL提供查询元数据的库: information_schema

我们主要对比表结构需要查询的表是information_schema.COLUMNS;这个表包含了所有的列我们可以根据schema来查询指定库的所有的列

select
   TABLE_SCHEMA as tableSchema, # schema
   TABLE_NAME as tableName, # 表名
   COLUMN_NAME as columnName, # 列名
   COLUMN_COMMENT as columnComment, # 字段注释
   COLUMN_TYPE as columnType, # 列类型varchar(10)
   COLUMN_DEFAULT as columnDefault, # 默认值
   DATA_TYPE as dataType # 字段类型 char
from information_schema.`COLUMNS`
where TABLE_SCHEMA = 'test'

三、代码实现

1. Mapper

   <select id="getColumnsBySchema" resultType="com.yy.database.demo.entity.ColumnsEntity">
           select
           TABLE_SCHEMA as tableSchema,
           TABLE_NAME as tableName,
           COLUMN_NAME as columnName,
           COLUMN_COMMENT as columnComment,
           COLUMN_TYPE as columnType,
           COLUMN_DEFAULT as columnDefault,
           DATA_TYPE as dataType
           from information_schema.`COLUMNS`
           where TABLE_SCHEMA = #{schemaName}
   </select>
   

2. Service

   • Master

   @Service
   public class MasterServiceImpl implements MasterService {
   
       @Resource
       private ReadMapper readMapper;
   
       @Override
       public List<ColumnsEntity> getColumnsBySchema(String schemaName) {
           return readMapper.getColumnsBySchema(schemaName);
       }
   }
   

   • Slave

   @Service
   @DS("slave_1")
   public class SlaveServiceImpl implements SlaveService {
   
       @Resource
       private ReadMapper readMapper;
   
       @Override
       public List<ColumnsEntity> getColumnsBySchema(String schemaName) {
           return readMapper.getColumnsBySchema(schemaName);
       }
   }
   

3. 对比表结构并输出DDL

/**
 * @author yuchangrong
 * @date 2023/03/07 10:37
 * @description
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class TableStructureComparisonTest {

    @Resource
    private MasterService masterService;

    @Resource
    private SlaveService slaveService;

    /**
     * ALTER TABLE table_name
     * ADD COLUMN column_1 char(1) DEFAULT NULL COMMENT '字段1' ,
     * ADD COLUMN column_2 tinyint DEFAULT 1 COMMENT '字段2',
     * ADD COLUMN column_3 int DEFAULT '' COMMENT '字段3';
     */
    @Test
    public void getDifference() {
        List<ColumnsEntity> slnb = masterService.getColumnsBySchema("master");
        List<ColumnsEntity> ant = slaveService.getColumnsBySchema("slave");
        Map<String, List<ColumnsEntity>> masterColumnMap = slnb.stream().collect(Collectors.groupingBy(ColumnsEntity::getTableName));
        Map<String, List<ColumnsEntity>> antColumnMap = ant.stream().collect(Collectors.groupingBy(ColumnsEntity::getTableName));
        Map<String, List<ColumnsEntity>> map = new HashMap<>();
        List<String> addTables = new ArrayList<>();
        for (Map.Entry<String, List<ColumnsEntity>> entry : masterColumnMap.entrySet()) {
            String key = entry.getKey();
            List<ColumnsEntity> value = entry.getValue();
            List<ColumnsEntity> antColumns = antColumnMap.get(key);
            if (antColumns == null || antColumns.size() == 0) {
                addTables.add(key);
                continue;
            }
            if (value.size() != antColumns.size()) {
                List<ColumnsEntity> result = getMasterMoreField(value, antColumns);
                map.put(key, result);
            }
        }
        generateSql(map);
        System.out.println(JSON.toJSONString(map));
        System.out.println("--------------");
        System.out.println(JSON.toJSONString(addTables));
    }

    private List<ColumnsEntity> getMasterMoreField(List<ColumnsEntity> listA, List<ColumnsEntity> listB) {
        List<String> collect = listB.stream().map(ColumnsEntity::getColumnName).collect(Collectors.toList());
        if (listA.size() > listB.size()) {
            return listA.stream().filter(temp -> !collect.contains(temp.getColumnName())).collect(Collectors.toList());
        }
        return null;
    }

    private void generateSql(Map<String, List<ColumnsEntity>> map) {
        StringBuilder sql = new StringBuilder();
        for (Map.Entry<String, List<ColumnsEntity>> entry : map.entrySet()) {
            StringBuilder singleSql = new StringBuilder();
            String tableName = entry.getKey();
            List<ColumnsEntity> value = entry.getValue();
            if (value != null && value.size() > 0) {
                singleSql.append("ALTER TABLE ")
                        .append(tableName)
                        .append("\n");
                for (int i = 0; i < value.size(); i++) {
                    ColumnsEntity columnsEntity = value.get(i);
                    singleSql.append("ADD COLUMN ")
                            .append(columnsEntity.getColumnName())
                            .append(" ")
                            .append(columnsEntity.getColumnType())
                            .append(" ");
                    if (columnsEntity.getColumnDefault() == null) {
                        singleSql.append("DEFAULT NULL ");
                    } else {
                        singleSql.append("DEFAULT ")
                                .append(getDefaultValue(columnsEntity.getDataType(), columnsEntity.getColumnDefault()))
                                .append(" ");
                    }
                    singleSql.append("COMMENT ")
                            .append("'")
                            .append(columnsEntity.getColumnComment())
                            .append("'")
                    ;
                    if (i == value.size() - 1) {
                        singleSql.append(";")
                                .append("\n\n");
                    } else {
                        singleSql.append(",")
                                .append("\n");
                    }
                }
            }
            sql.append(singleSql);
        }
        try (FileWriter fileWriter = new FileWriter("D:/out/diff.sql")) {
            fileWriter.write(sql.toString());
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        System.out.println(sql);
    }

    private Object getDefaultValue(String dataType, String defaultValue) {
        Object result = null;
        DataTypeEnum typeEnum = DataTypeEnum.getTypeEnum(dataType);
        if (defaultValue != null) {
            switch (typeEnum) {
                case TINYINT:
                case INT:
                case DATETIME:
                    result = defaultValue;
                    break;
                case CHAR:
                case VARCHAR:
                    result = "'" + defaultValue + "'";
                    break;
                default:
                    break;
            }
        }
        return result;
    }

}

/**
 * @author yuchangrong
 * @date 2023/03/08 10:50
 * @description
 */
public enum DataTypeEnum {
    /*null*/
    NULL("null"),

    /*tinyint*/
    TINYINT("tinyint"),

    /*varchar*/
    VARCHAR("varchar"),

    /*int*/
    INT("int"),

    /*datetime*/
    DATETIME("datetime"),

    /*char*/
    CHAR("char");

    private final String dataType;

    public String getDataType() {
        return dataType;
    }

    DataTypeEnum(String dataType) {
        this.dataType = dataType;
    }

    public static DataTypeEnum getTypeEnum(String dataType) {
        if (null == dataType) {
            return NULL;
        }
        DataTypeEnum[] values = DataTypeEnum.values();
        for (DataTypeEnum value : values) {
            if (value.dataType.equals(dataType)) {
                return value;
            }
        }
        return NULL;
    }
}

这里只是对比了slave比master都有的表，表结构少了哪些字段也是我现在所需要的功能；还可以在此基础上进行对比缺少的表生产建表语句等功能。