import java.sql.*;
import java.util.*;
public class CompareDbMain {
//修改成自己的库信息start
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";
//修改成自己的库信息end
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("\n"+sql).append("\n").append("\n");
}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("\n"+sql).append("\n").append("\n");
}
}
}
}
//判断表的字段是否删除
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("\n"+sql).append("\n").append("\n");
}
}
}
}
//判断表是否删除
for (String s : columnMap2.keySet()) {
if(!columnMap1.containsKey(s)){
sb_more.append("-- 多余的表:"+s).append("\n");
}
}
System.out.println("-- 新增的变化:");
System.out.println(sb_insert);
System.out.println("-- 修改的变化:");
System.out.println(sb_update);
System.out.println("-- 多余的变化:");
System.out.println(sb_more);
}
}
运行结果如下: