imp
  
ort java.sql.*;
import java.util.*;

public class CompareDbMain {

    private static String dataBasesName1 = "test";//源库名
    private static String dataBasesName2 = "test";//目标库名,一般跟源库名一致
    private static String db_url1 = "jdbc:mysql://localhost:3306/"+dataBasesName1+"?useUnicode=true&characterEncoding=utf8&useSSL=false";//源数据库连接
    private static String db_user1 = "root";
    private static String db_pwd1 = "root";

    private static String db_url2 = "jdbc:mysql://localhost:53306/"+dataBasesName2+"?useUnicode=true&characterEncoding=utf8&useSSL=false";//目标数据库连接
    private static String db_user2 = "readonly_user";
    private static String db_pwd2 = "readonly_user";
    private static List<String> tableList1 = new ArrayList<>();//表1所有表
    private static List<String> tableList2 = new ArrayList<>();//表2所有表
    private static Map<String,Map<String,String>> columnMap1 = new HashMap<>();//表1所有字段 map<表名,Map<字段名,字段类型+字段默认值+字段备注>>
    private static Map<String,Map<String,String>> columnMap2 = new HashMap<>();//表2所有字段 ap<表名,Map<字段名,字段类型+字段默认值+字段备注>>

    public static void main(String[] args) {
        getAllColumnList(db_url1, db_user1, db_pwd1, columnMap1,tableList1,dataBasesName1);
        getAllColumnList(db_url2, db_user2, db_pwd2, columnMap2,tableList2,dataBasesName2);
        compareDb(columnMap1,columnMap2);
    }


    public static void getAllColumnList(String db_url, String db_user, String db_pwd,Map<String,Map<String,String>> map,List<String> tableList,String dataBasesName){
        try (Connection connection = DriverManager.getConnection(db_url, db_user, db_pwd)) {
            String sql_table = "show tables";
            PreparedStatement statement_table = connection.prepareStatement(sql_table);
            ResultSet resultSet_table = statement_table.executeQuery();
            while (resultSet_table.next()) {
                tableList.add(resultSet_table.getString(1));
            }

            String sql = "select column_name,column_type,column_default,column_comment,table_name from information_schema.columns where table_schema="+"'"+dataBasesName+"'";
            PreparedStatement statement1 = connection.prepareStatement(sql);
            ResultSet resultSet1 = statement1.executeQuery();
            while (resultSet1.next()) {
                String tableName = resultSet1.getString(5);
                if(!tableList.contains(tableName)){
                    continue;
                }
                Map<String, String> maps = map.get(tableName);
                if(maps == null){
                    maps = new HashMap<>();
                }
                //&Ran;是占位标识，可以自定义用来后面拆分拼sql用
                maps.put(resultSet1.getString(1),resultSet1.getString(2)+"&Ran;"+resultSet1.getString(3)+"&Ran; "+resultSet1.getString(4));
                map.put(tableName,maps);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public static void compareDb(Map<String,Map<String,String>> columnMap1,Map<String,Map<String,String>> columnMap2) {

        StringBuilder sb_insert = new StringBuilder();//新增的
        StringBuilder sb_update = new StringBuilder();//修改的
        StringBuilder sb_more = new StringBuilder();//多余的
        //1判断表是否新增
        for (String s : columnMap1.keySet()) {
            if(!columnMap2.containsKey(s)){
                System.out.println("-- 新增的表:"+s);
            }else{
                //2判断表里的字段是否新增
                Map<String, String> stringStringMap1 = columnMap1.get(s);
                Map<String, String> stringStringMap2 = columnMap2.get(s);
                for (String s1 : stringStringMap1.keySet()) {
                    String str = stringStringMap1.get(s1);
                    if (!stringStringMap2.containsKey(s1)&&str.split("&Ran;").length>1) {
                        String sql = "alter table "+dataBasesName2+"."+s+" add"+" `"+s1+"`"+" "+str.split("&Ran;")[0]+" DEFAULT "+str.split("&Ran;")[1]+" COMMENT '"+str.split("&Ran;")[2]+"';";
                        String column = str.replaceAll("&Ran;"," ");
                        sb_insert.append("-- 表:"+s+"新增的字段:"+s1+column).append("
"+sql).append("
").append("
");
                    }else{
                        //存在，判断字段是否修改类似描述等信息
                        if(!str.equals(stringStringMap2.get(s1))&&str.split("&Ran;").length>1){
                            String sql = "alter table "+dataBasesName2+"."+s+" modify"+" `"+s1+"`"+" "+str.split("&Ran;")[0]+" DEFAULT "+str.split("&Ran;")[1]+" COMMENT '"+str.split("&Ran;")[2]+"';";
                            String content = "";
                            if(!str.split("&Ran;")[0].equals(stringStringMap2.get(s1).split("&Ran;")[0])){
                                content = "【类型】："+str.split("&Ran;")[0]+"==>"+stringStringMap2.get(s1).split("&Ran;")[0];
                            }
                            if(!str.split("&Ran;")[1].equals(stringStringMap2.get(s1).split("&Ran;")[1])){
                                content = "【默认值】："+str.split("&Ran;")[1]+"==>"+stringStringMap2.get(s1).split("&Ran;")[1];
                            }
                            if(!str.split("&Ran;")[2].trim().equals(stringStringMap2.get(s1).split("&Ran;")[2].trim())){
                                content = "【描述】："+str.split("&Ran;")[2]+"==>"+stringStringMap2.get(s1).split("&Ran;")[2];
                            }
                            if(!content.isEmpty()){
                                sb_update.append("-- 表:"+s+"修改的字段:"+s1+";修改内容:"+content).append("
"+sql).append("
").append("
");
                            }
                        }
                    }
                }
                //判断表的字段是否删除
                for (String s1 : stringStringMap2.keySet()) {
                    if (!stringStringMap1.containsKey(s1)) {
                        String sql = "alter table "+dataBasesName2+"."+s+" DROP COLUMN "+" `"+s1+"`";
                        String column = stringStringMap2.get(s1).replaceAll("&Ran;"," ");
                        sb_more.append("-- 表:"+s+"多余的字段:"+s1+column).append("
"+sql).append("
").append("
");
                    }
                }
            }
        }
        //判断表是否删除
        for (String s : columnMap2.keySet()) {
            if(!columnMap1.containsKey(s)){
                sb_more.append("-- 多余的表:"+s).append("
");
            }
        }
        System.out.println("-- 新增的变化：");
        System.out.println(sb_insert);
        System.out.println("-- 修改的变化：");
        System.out.println(sb_update);
        System.out.println("-- 多余的变化：");
        System.out.println(sb_more);
    }
}