Mysql 比较两个数据库的表，表字段是否匹配，升级更新找sql有用

import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;import java.util.ArrayList;import java.util.Collection;import java.util.HashMap;import java.util.HashSet;import java.util.List;import java.util.Map;import java.util.Set;import org.apache.commons.collections.CollectionUtils;public class DataBaseUtil {public static void main(String[] args) {try {String url1 = "jdbc:mysql://ddd?characterEncoding=utf-8&noAccessToProcedureBodies=true&useUnicode=true";String url2 = "jdbc:mysql://xxx?characterEncoding=utf-8&noAccessToProcedureBodies=true&useUnicode=true";Set<String> set1 = getTableNames(getConnection(url1));Set<String> set2 = getTableNames(getConnection(url2));//Collection c = CollectionUtils.disjunction(set1,set2);Collection c = CollectionUtils.subtract(set1,set2);for (Object object : c) {System.out.println("不同的表有："+object);}Map<String,List<String>> map1 = new HashMap<String, List<String>>();for (String s : set1) {map1.put(s, getFieldName(getConnection(url1), s));}Map<String,List<String>> map2 = new HashMap<String, List<String>>();for (String s : set2) {map2.put(s,getFieldName(getConnection(url2), s));}//比较 两个mapSet<String> keyMap2 = map2.keySet();for (String keyMap21 : keyMap2) {Set<String> keyMap1 = map1.keySet();for (String keyMap11 : keyMap1) {if(keyMap21.equalsIgnoreCase(keyMap11)){if(map1.get(keyMap11).size()!=map2.get(keyMap21).size()){System.out.println("表字段不同的表："+keyMap11);}difrentList(keyMap11,keyMap21,map1.get(keyMap11),map2.get(keyMap21));}}}} catch (Exception e1) {e1.printStackTrace();} }static Connection getConnection(String url){Connection conn = null;try {Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection(url, "db_admin", "123456");} catch (Exception e) {}return conn;}public static Set<String> getTableNames(Connection conn){Set<String> set1 = new HashSet<String>();try {Statement st = conn.createStatement();ResultSet rs = st.executeQuery("show   tables");while (rs.next()) {String tableName = rs.getString(1);//System.out.println(tableName);set1.add(tableName);}return set1;} catch (Exception e) {e.printStackTrace();}return set1;}public static List<String> getFieldName(Connection conn,String tableName){List<String> list = new ArrayList<String>();try {Statement st = conn.createStatement();ResultSet rs = st.executeQuery("describe  "+tableName);while (rs.next()) {String tableNameR = rs.getString(1);list.add(tableNameR);//System.out.println(tableNameR);}return list;} catch (Exception e) {e.printStackTrace();}return list;}private static void difrentList(String table1,String table2,List<String> list1,List<String> list2){//Collection<String> c = CollectionUtils.disjunction(list1,list2);//集合A B 不同的字段Collection<String> c = CollectionUtils.subtract(list1,list2);//集合AB中A有B没有的字段if(c!=null && c.size()>0){System.out.println();System.out.println(table1+" 不同的字段");for (String object : c) {System.out.println(object);}//System.out.println(table2+" 不同的字段");}}}