项目场景：

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

package com.xuyw.test.export;import com.alibaba.druid.pool.DruidDataSource;import mons.collections4.MapUtils;import mons.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 /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_INCREMENTprivate 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());}}

对比效果如下

如果觉得《mysql 表结构差异对比小工具》对你有帮助，请点赞、收藏，并留下你的观点哦！