mysql 表结构差异对比小工具_mysql表结构对比工具

项目场景：

在进行慢sql评审时,发现测试环境增加了索引生产并没有。然后有些表字段长度也不一样，坑大发了。决定写一个小工具对比一下测试跟生产表结构差异，大致思路连接数据库获取建表语句进行对比，忽略主键自增id。因为测试跟生产主键id自增会有不同，如果建表语句不一致，则需要比对文本高亮显示。在网上copy了一个别人写的高亮算法，生成了一个html预览。直接上代码

package com.xuyw.test.export;
 
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.io.FileUtils;
import org.springframework.jdbc.core.JdbcTemplate;
 
import java.io.File;
import java.io.IOException;
import java.util.*;
 
 
/**
 * @author one.xu
 * @version v1.0
 * @description
 * @date 2022/1/5 17:59
 */
public class DbComparedTest {
    private static JdbcTemplate db1;
    private static JdbcTemplate db2;
 
    static {
        db1 = new JdbcTemplate();
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl("");
        dataSource.setUsername("");
        dataSource.setPassword("");
        db1.setDataSource(dataSource);
 
 
        db2 = new JdbcTemplate();
        DruidDataSource dataSource2 = new DruidDataSource();
        dataSource2.setUrl("");
        dataSource2.setUsername("");
        dataSource2.setPassword("");
        db2.setDataSource(dataSource2);
    }
    //移除AUTO_INCREMENT
    private static String handTable(String table) {
        return table.replaceAll("AUTO_INCREMENT=\\d+", "").trim();
 
    }
    //文本对比,高亮显示
    public static String getcompareStr(String char1, String char2) {
        String bcolor = "<span style='background-color:yellow;color:red;'>";
        String ecolor = "</span>";
        StringBuffer sb = new StringBuffer();
        char[] a = new char[char1.length()];
        for (int i = 0; i < char1.length(); i++) {
            a[i] = char1.charAt(i);
        }
        char[] b = new char[char2.length()];
        for (int i = 0; i < char2.length(); i++) {
            b[i] = char2.charAt(i);
        }
        // 不同字符集合
        Map<Object, Object> map1 = new HashMap<>();
        // 包含字符集合
        Map<Object, Object> map2 = new HashMap<>();
        for (int i = 0; i < a.length; i++) {
            if (i == a.length - 1) {
                if (i > 1) {
                    if (String.valueOf(b).contains(String.valueOf(a[i - 1]) + String.valueOf(a[i]))) {
                        map2.put(i - 1, a[i - 1]);
                        map2.put(i, a[i]);
                    } else {
                        map1.put(i, a[i]);
                    }
                } else {
                    map2.put(i, a[i]);
                }
            } else {
                if (String.valueOf(b).contains(String.valueOf(a[i]) + String.valueOf(a[i + 1]))) {
                    if (i > 1) {
                        if (String.valueOf(b).contains(String.valueOf(a[i - 1]) + String.valueOf(a[i]))) {
                            map2.put(i - 1, a[i - 1]);
                            map2.put(i, a[i]);
                        }
                    } else {
                        map2.put(i, a[i]);
                    }
                } else {
                    if (i > 0) {
                        if (String.valueOf(b).contains(String.valueOf(a[i - 1]) + String.valueOf(a[i]))) {
                            map2.put(i - 1, a[i - 1]);
                            map2.put(i, a[i]);
                        } else {
                            map1.put(i, a[i]);
                        }
                    } else {
                        map1.put(i, a[i]);
                    }
                }
            }
        }
        for (int i = 0; i < a.length; i++) {
            if (map1.get(i) != null) {
                sb.append(bcolor).append(map1.get(i)).append(ecolor);
            } else if (map2.get(i) != null) {
                sb.append(map2.get(i));
            }
        }
        return sb.toString();
    }
 
    public static void main(String[] args) throws IOException {
	    //需要比对的数据库名,多个逗号连接
        String dbs = "testDb1,testDb1";
        String tableSql = "select table_name,table_comment FROM information_schema.tables WHERE table_schema=?";
        //忽略比对的表名,多个逗号连接  支持正则
		String ignoreTable = "|(msg_log_\\w+)|(hand_log_\\w+)";
        String tableCreateSql = "show create table ";
        List<Map<String, Object>> tableDiffList = new ArrayList<>();
        Map<String, Object> diffMap;
        Map<String, Integer> dbTableCountMap = new HashMap<>();
        int dbTableCount = 0;
        for (String db : dbs.split(",")) {
            dbTableCount = 0;
            List<Map<String, Object>> tables = db1.queryForList(tableSql, db);
            for (int j = 0; j < tables.size(); j++) {
                String table = tables.get(j).get("table_name").toString();
                if (table.matches(ignoreTable)) {
                    continue;
                }
                diffMap = new HashMap<>();
                Map<String, Object> sourceTableMap = db1.queryForMap(tableCreateSql + db + "." + table);
                String sourceTable = handTable(MapUtils.getString(sourceTableMap, "Create Table"));
                diffMap.put("dbName", db);
                diffMap.put("sourceTable", sourceTable);
                Map<String, Object> targetTableMap;
                try {
                    targetTableMap = db2.queryForMap(tableCreateSql + db + "." + table);
                } catch (Exception e) {
                    dbTableCount = dbTableCount + 1;
                    diffMap.put("targetTable", "不存在");
                    tableDiffList.add(diffMap);
                    continue;
                }
                String targetTable = handTable(MapUtils.getString(targetTableMap, "Create Table"));
                if (sourceTable.equals(targetTable)) {
                    continue;
                }
                diffMap.put("targetTable", getcompareStr(targetTable, sourceTable));
                diffMap.put("sourceTable", getcompareStr(sourceTable, targetTable));
                tableDiffList.add(diffMap);
                dbTableCount = dbTableCount + 1;
 
            }
            dbTableCountMap.put(db, dbTableCount);
        }
        StringJoiner html = new StringJoiner("\n");
        html.add("<table style=\"font-size:12px;color:#333333;width:100%;border-width: 1px;border-color: #729ea5;border-collapse: collapse;\" border=\"1\">\n" +
                "<tr><th >数据库名</th><th>源表</th><th>对比表</th></tr>");
 
        for (Map<String, Object> m : tableDiffList) {
            String dbName=m.get("dbName").toString();
            Integer dbTabeCount=dbTableCountMap.get(dbName);
            if(dbTabeCount!=null){
               html.add("<tr> <td rowspan=\""+dbTabeCount+"\">"+dbName+"</td>");
                dbTableCountMap.remove(dbName);
            }
            html.add("<td>\n" +
                    "<pre> "+m.get("sourceTable").toString()+" <pre>\n" +
                    "</td>");
            html.add("<td>\n" +
                    "<pre> "+m.get("targetTable").toString()+" <pre>\n" +
                    "</td>");
            html.add("</tr>");
        }
        html.add("</table>");
        FileUtils.writeStringToFile(new File("d:\\DbComparedTest.html"), html.toString());
 
    }
}

对比效果如下