| mysql 表结构差异对比小工具 | 您所在的位置：网站首页 › handtable › 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 = ""; String ecolor = ""; 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 map1 = new HashMap(); // 包含字符集合 Map 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 tableDiffList = new ArrayList(); Map diffMap; Map dbTableCountMap = new HashMap(); int dbTableCount = 0; for (String db : dbs.split(",")) { dbTableCount = 0; List 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 sourceTableMap = db1.queryForMap(tableCreateSql + db + "." + table); String sourceTable = handTable(MapUtils.getString(sourceTableMap, "Create Table")); diffMap.put("dbName", db); diffMap.put("sourceTable", sourceTable); Map 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("\n" + "数据库名源表对比表"); for (Map m : tableDiffList) { String dbName=m.get("dbName").toString(); Integer dbTabeCount=dbTableCountMap.get(dbName); if(dbTabeCount!=null){ html.add(" "+dbName+""); dbTableCountMap.remove(dbName); } html.add("\n" + " "+m.get("sourceTable").toString()+" \n" + ""); html.add("\n" + " "+m.get("targetTable").toString()+" \n" + ""); html.add(""); } html.add(""); FileUtils.writeStringToFile(new File("d:\\DbComparedTest.html"), html.toString()); } }对比效果如下
|
【本文地址】
公司简介
联系我们