Mysql根据一个基库生成其他库与其不同的库升级脚本

今天研究了一下不同数据库之间如何做同步。弄了一个升级工具类，希望以后还能有所帮助。

 

public class UpgradeDataBase {

    public static void main(String[] args) {
        //两个不同数据库名称
        List<String> sqls = UpgradeDataBaseToStandardDataBase("database1", "database2");
        for(String sql : sqls) {
            System.out.println(sql);
        }
    }

    /**
     * 将基准库里有的表字段,以名称为基准,生成同步到目标库中的脚本
     */
    public static List<String> UpgradeDataBaseToStandardDataBase(String standard, String target) {

        List<String> upgradeSqls = new ArrayList<>();

        List<String> standardTables = new ArrayList<>();
        List<String> targetTables = new ArrayList<>();

        Connection connStandard = null;
        Connection connTarget = null;

        Statement stmtStandard = null;
        Statement stmtTarget = null;

        ResultSet rsStandard = null;
        ResultSet rsTarget = null;

        try {
            Properties props = Resources.getResourceAsProperties("jdbc.properties");
            String url = props.getProperty("url");
            String driver = props.getProperty("driver");
            String username = props.getProperty("username");
            String password = props.getProperty("password");

            Class.forName(driver).newInstance();
            //拿到2个库的链接
            connStandard = DriverManager.getConnection(url, username, password);
            connStandard.setCatalog(standard);

            connTarget = DriverManager.getConnection(url, username, password);
            connTarget.setCatalog(target);


            stmtStandard = connStandard.createStatement();
            stmtTarget = connTarget.createStatement();

            //先把2个库所有的表查出来
            stmtStandard.execute("show tables");
            stmtTarget.execute("show tables");

            rsStandard = stmtStandard.getResultSet();
            rsTarget = stmtTarget.getResultSet();

            while (rsStandard.next()){
                standardTables.add(rsStandard.getString(1));
            }

            while (rsTarget.next()){
                targetTables.add(rsTarget.getString(1));
            }
            //循环基准库中每一张表
            for(String table : standardTables) {
                if("SM_USER".equals(table)) {
                    continue;
                }
                if(targetTables.contains(table)) {
                    Map<String, Map<String, String>> standardColumns = new HashMap<>();
                    Map<String, Map<String, String>> targetColumns = new HashMap<>();
                    //检查每一个字段,
                    //1.首先先查出目标库和基准库该表的所有字段
                    stmtStandard.execute("show columns from " + table + " from " + standard);
                    rsStandard = stmtStandard.getResultSet();
                    while (rsStandard.next()){

                        Map<String, String> map = new HashMap<>();
                        map.put("Field", rsStandard.getString("Field"));//列名
                        map.put("Type", rsStandard.getString("Type"));//类型+长度
                        map.put("Null", rsStandard.getString("Null"));//是否可为空
                        map.put("Key", rsStandard.getString("Key"));//是否主键
                        map.put("Default", rsStandard.getString("Default"));//默认值
                        map.put("Extra", rsStandard.getString("Extra"));//其他(自增列,触发器等)
                        standardColumns.put(rsStandard.getString("Field"), map);
                    }

                    stmtTarget.execute("show columns from " + table + " from " + target);
                    rsTarget = stmtTarget.getResultSet();
                    while (rsTarget.next()){

                        Map<String, String> map = new HashMap<>();
                        map.put("Field", rsTarget.getString("Field"));//列名
                        map.put("Type", rsTarget.getString("Type"));//类型+长度
                        map.put("Null", rsTarget.getString("Null"));//是否可为空
                        map.put("Key", rsTarget.getString("Key"));//是否主键
                        map.put("Default", rsTarget.getString("Default"));//默认值
                        map.put("Extra", rsTarget.getString("Extra"));//其他(自增列,触发器等)
                        targetColumns.put(rsTarget.getString("Field"), map);
                    }

                    //2.以基准库为准,逐个列比较
                    //TODO 没有处理Key(没有做主键、自增处理)
                    for(String column : standardColumns.keySet()) {
                        if(targetColumns.containsKey(column)) {//存在这一列
                            boolean needGeneSql = false;
                            StringBuffer buffer = new StringBuffer();
                            //类型有变化, 但是不管类型有没有变化,后续的语句都需要
//                            if(standardColumns.get(column).get("Type") != null && !standardColumns.get(column).get("Type").equals(targetColumns.get(column).get("Type"))) {
//                                buffer.append(standardColumns.get(column).get("Type"));
//                            }
                            buffer.append(standardColumns.get(column).get("Type"));
                            //默认值有变
                            if(standardColumns.get(column).get("Default") != null && !standardColumns.get(column).get("Default").equals(targetColumns.get(column).get("Default"))) {
                                buffer.append(" default " + standardColumns.get(column).get("Default"));
                                needGeneSql = true;
                            }
                            //是否可空有变
                            if(standardColumns.get(column).get("Null") != null && !standardColumns.get(column).get("Null").equals(targetColumns.get(column).get("Null"))) {
                                buffer.append(("NO".equals(standardColumns.get(column).get("Null")) ? " not null " : " null "));
                                needGeneSql = true;
                            }
                            //处理自增长等
                            if(standardColumns.get(column).get("Extra") != null && !standardColumns.get(column).get("Extra").equals(targetColumns.get(column).get("Extra"))) {
                                buffer.append(" ").append(standardColumns.get(column).get("Extra"));
                                needGeneSql = true;
                            }
                            if(needGeneSql) {
                                String changeColumnSql = "alter table " + table + " change " + column + " " + column + " " + buffer.toString() + ";";
                                upgradeSqls.add(changeColumnSql);
                            }
                        }
                        else{
                            String addColumnSql = "alter table " + table +
                                    " add column " + column + " " +
                                    standardColumns.get(column).get("Type") + " default " + standardColumns.get(column).get("Default") +
                                    ("NO".equals(standardColumns.get(column).get("Null")) ? " not null " : " null ") + ";";
                            upgradeSqls.add(addColumnSql);
                        }
                    }
                }
                else{//目标库中,没有基准库的表
                    stmtStandard.execute("show create table " + table);
                    rsStandard = stmtStandard.getResultSet();
                    String createSql = null;
                    while (rsStandard.next()){
                        //第2列是建表语句
                        createSql = rsStandard.getString(2);
                        upgradeSqls.add(createSql + ";");
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                rsStandard.close();
                rsTarget.close();
                stmtStandard.close();
                stmtTarget.close();
                connStandard.close();
                connTarget.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return upgradeSqls;

    }
}