Mysql数据库之间表结构比对更新_mysql 表结构对比更新

Mysql数据库之间表结构比对更新(不改变数据)

主要用于不同环境之间数据库变更后，表结构不一致问题的解决，废话不所说直接上代码

依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.27</version>
</dependency>
1
2
3
4
5

主程序

需要修改ip:port/db参数 一sourceDb 为基准，更新另一个库

import java.sql.*;
import java.util.*;


public class DbStructureUpdate {
   


    public static void main(String[] args) {
   
        String sourceDb = "jdbc:mysql://ip:port/db?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&useSSL=false&allowMultiQueries=true&rewriteBatchedStatements=true";
        String db1User = "root";
        String db1Password = "2Yotgqipxv";

        String db2Url = "jdbc:mysql://ip:port/db?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&useSSL=false&allowMultiQueries=true&rewriteBatchedStatements=true";
        String db2User = "root";
        String db2Password = "yDge3EZPX0";

        try (
                Connection db1Conn = DriverManager.getConnection(sourceDb, db1User, db1Password);
                Connection db2Conn = DriverManager.getConnection(db2Url, db2User, db2Password)
        ) {
   
            String db1Schema = getSchemaNameFromUrl(sourceDb);
            String db2Schema = getSchemaNameFromUrl(db2Url);

            Map<String, Map<String, Map<String, String>>> db1TableStructures = new HashMap<>();
            Map<String, Map<String, Map<String, String>>> db2TableStructures = new HashMap<>();

            for (String tableName : getTableNames(db1Conn, db1Schema)) {
   
                db1TableStructures.put(tableName, getTableStructure(db1Conn, tableName, db1Schema));
            }

            for (String tableName : getTableNames(db2Conn, db2Schema)) {
   
                db2TableStructures.put(tableName, getTableStructure(db2Conn, tableName, db1Schema));
            }

            for (Map.Entry<String, Map<String, Map<String, String>>> entry : db1TableStructures.entrySet()) {
   
                String tableName = entry.getKey();
                Map<String, Map<String, String>> db1TableStructure = entry.getValue();
                Map<String, Map<String, String>> db2TableStructure = db2TableStructures.get(tableName);

                if (db2TableStructure == null) {
   
                    createTable(db2Conn, db1Conn, tableName);
                } else {
   
                    String db1PrimaryKey = getPrimaryKey(db1Conn, db1Schema, tableName);
                    String db2PrimaryKey = getPrimaryKey(db2Conn, db2Schema, tableName);

                    Map<String, IndexInfo> db1Indexes = getIndexes(db1Conn, db1Schema, tableName);
                    Map<String, IndexInfo> db2Indexes = getIndexes(db2Conn, db2Schema, tableName);

                    updateTableStructure(db2Conn, tableName, db1TableStructure, db2TableStructure);
                    updatePrimaryKey(db2Conn, tableName, db1PrimaryKey, db2PrimaryKey);
                    updateIndexes(db2Conn, tableName, db1Indexes, db2Indexes);
                }
            }

            for (String tableName : db2TableStructures.keySet()) {
   
                if (!db1TableStructures.containsKey(tableName)) {
   
                    dropTable(db2Conn, tableName);
                }
            }
        } catch (SQLException e) {
   
            e.printStackTrace();
        }
    }

    private static List<String> getTableNames(Connection conn, String schemaName) throws SQLException {
   
        List<String> tableNames = new ArrayList<>();
        String query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ?";

        try (PreparedStatement pstmt = conn.prepareStatement(query)) {
   
            pstmt.setString(1, schemaName);
            ResultSet tables 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83