mysql脚本往不同库刷不同sql文件_Mysql根据一个基库生成其他库与其不同的库升级脚本...

public class UpgradeDataBase {

public static void main(String[] args) {

//两个不同数据库名称

List sqls = UpgradeDataBaseToStandardDataBase("database1", "database2");

for(String sql : sqls) {

System.out.println(sql);

}

}

/**

* 将基准库里有的表字段,以名称为基准,生成同步到目标库中的脚本

*/

public static List UpgradeDataBaseToStandardDataBase(String standard, String target) {

List upgradeSqls = new ArrayList<>();

List standardTables = new ArrayList<>();

List 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> standardColumns = new HashMap<>();

Map> targetColumns = new HashMap<>();

//检查每一个字段,

//1.首先先查出目标库和基准库该表的所有字段

stmtStandard.execute("show columns from " + table + " from " + standard);

rsStandard = stmtStandard.getResultSet();

while (rsStandard.next()){

Map 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 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;

}

}