同步两张表之间的表结构。 模仿navict中同步数据库功能。 适合于开发和测试环境之间同步表结构使用 ，修改表结构会导致数据丢失。生产环境慎用,单纯添加表、字段、索引不受影响。
执行后生成的部分sql
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `portal_user` ADD COLUMN `address_3` varchar(32) NOT NULL after `address_2`;
ALTER TABLE `portal_user` ADD INDEX `index_address2` USING BTREE (`address_2`) comment '';
SET FOREIGN_KEY_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `portal_user` DROP INDEX `index_address2`;
ALTER TABLE `portal_user` CHANGE COLUMN `address_3` `address_3` varchar(32) CHARACTER SET utf8 NOT NULL after `user_age`;
ALTER TABLE `portal_user` CHANGE COLUMN `address_4` `address_4` varchar(32) CHARACTER SET utf8 NOT NULL DEFAULT '4' COMMENT 'address_4' after `address_3`;
ALTER TABLE `portal_user` ADD COLUMN `address_22` int(11) NOT NULL after `password`;
ALTER TABLE `portal_user` DROP COLUMN `address_2`;
ALTER TABLE `portal_user` ADD INDEX `index_address2` USING BTREE (`address_22`) comment '';
SET FOREIGN_KEY_CHECKS = 1;
基本原理
1、获取两个库所有表信息、字段信息和索引信息 -> 2、对比差异 -> 3、生成sql
部分代码
/*
* Copyright (C) 2016 alchemystar, Inc. All Rights Reserved.
*/
package com.eblly.util;
import com.eblly.meta.Column;
import com.eblly.meta.Key;
import com.eblly.meta.MetaData;
import com.eblly.meta.Table;
import com.jfinal.kit.StrKit;
import lombok.Getter;
import lombok.Setter;
import java.util.Deque;
import java.util.Map;
import java.util.concurrent.LinkedBlockingDeque;
/**
* @Author eblly
*/
@Getter
@Setter
public class CompareUtil {
private MetaData sourceMetaData;
private MetaData targetMetaData;
private Deque<String> changeSqlDeque = new LinkedBlockingDeque<>();
/**
* @param source
* @param target
*/
public CompareUtil(MetaData source, MetaData target) {
this.sourceMetaData = source;
this.targetMetaData = target;
}
public void compare() {
compareTables_Columns_Keys();
// compareKeys();
}
// ================================= table ===============================================
/**
* 对比表和表中的字段
*/
private void compareTables_Columns_Keys() {
// 删除废弃表
for (Table targetTable : targetMetaData.getTableMap().values()) {
if (sourceMetaData.getTableMap().get(targetTable.getTableName()) == null) {
// 如果对应的source没有这张表,直接drop Table
String dropTableSql = "DROP TABLE " + addQuotes(targetTable.getTableName()) + " ;";
changeSqlDeque.addLast(dropTableSql);
}
}
/**
* 创建表 & 对比字段 & 对比key
*/
for (Table table : sourceMetaData.getTableMap().values()) {
if (targetMetaData.getTableMap().get(table.getTableName()) == null) {
// 如果对应的target没有这张表,直接把create Table拿出
changeSqlDeque.addLast(table.getCreateTable() + ";");
} else {
Table tableTarget = targetMetaData.getTableMap().get(table.getTableName());
// 这样就需要比较两者的字段
compareColumns(table, tableTarget);
// 对比两个表的keys
compareKeys(table, tableTarget);
}
}
}
// ================================= column ===============================================
/**
* 对比两张表的字段
*
* @param tableSource
* @param tableTarget
*/
private void compareColumns(Table tableSource, Table tableTarget) {
// 移除废弃字段
for (Column column : tableTarget.getColumnMap().values()) {
if (tableSource.getColumnMap().get(column.getColumnName()) == null) {
String sql =
"alter table `" + tableTarget.getTableName() + "` DROP COLUMN `" + column.getColumnName() + "`; ";
changeSqlDeque.addLast(sql);
}
}
String afterColumn = null;
StringBuilder sqlStr;
for (Column columnSource : tableSource.getColumnMap().values()) {
sqlStr = new StringBuilder();
String columnName = columnSource.getColumnName();
Column columnTarget = tableTarget.getColumnMap().get(columnSource.getColumnName());
if (columnTarget == null) {
// ALTER TABLE `portal_user` ADD COLUMN `address_4` varchar(32) NOT NULL DEFAULT '4' COMMENT 'address_4' after `address_3`;
// 字段不存在 ，直接添加
sqlStr.append("ALTER TABLE ")
.append("`")
.append(tableSource.getTableName())
.append("`");
sqlStr.append(" ADD COLUMN ")
.append("`")
.append(columnName)
.append("` ");
sqlStr.append(appendColumnTail(columnSource, afterColumn));
changeSqlDeque.addLast(sqlStr.toString());
} else {
// 比较名字、顺序、是否允许为空、类型、默认值、注释等
if (columnSource.equals(columnTarget)) {
continue;
} else {
sqlStr.append("ALTER TABLE ")
.append("`")
.append(tableSource.getTableName())
.append("`");
// 修改字段
// ALTER TABLE `portal_user` CHANGE COLUMN `address_3` `address_3` varchar(32) NOT NULL after `user_age`;
sqlStr.append(" CHANGE COLUMN ")
.append("`")
.append(columnName)
.append("` ")
.append("`")
.append(columnName)
.append("` ");
sqlStr.append(appendColumnTail(columnSource, afterColumn));
changeSqlDeque.addLast(sqlStr.toString());
}
}
afterColumn = columnName;
}
}
/**
* @param columnSource
* @param afterColumn
* @return
*/
private StringBuilder appendColumnTail(Column columnSource, String afterColumn) {
StringBuilder sqlStr = new StringBuilder();
sqlStr.append(" ")
.append(columnSource.getColumnType())
.append(" ");
if (columnSource.getIsNull().equals("NO")) {
sqlStr.append(" NOT NULL ");
} else {
sqlStr.append(" NULL ");
}
if (columnSource.getExtra().toUpperCase().startsWith("AUTO_INCREMENT")) {
sqlStr.append(" AUTO_INCREMENT ");
}
if (columnSource.getColumnDefault() != null) {
sqlStr.append(" DEFAULT ")
.append(columnSource.getColumnDefault().equals("") ? "''" : columnSource.getColumnDefault())
.append(" ");
}
if (columnSource.getComment() != null) {
sqlStr.append(" COMMENT ")
.append("'")
.append(columnSource.getComment())
.append("' ");
}
if (StrKit.notBlank(afterColumn)) {
sqlStr.append(" after `")
.append(afterColumn)
.append("`");
}
sqlStr.append(";");
return sqlStr;
}
// ==================================== index ============================================
/**
* @param tableSource
* @param tableTarget
*/
private void compareKeys(Table tableSource, Table tableTarget) {
// ALTER TABLE `portal_user` DROP INDEX `index_userAge`;
// ALTER TABLE `portal_user` ADD INDEX `index_userAge` USING BTREE (`user_age`,`user_name`) comment
// 'index_userage';
//ALTER TABLE `portal_user` ADD INDEX `index_address3` USING BTREE (`address_3`) comment 'index3';
// 删除废弃 key
for (Map.Entry<String, Key> keyEntry : tableTarget.getKeyMap().entrySet()) {
String keyName = keyEntry.getKey();
Key keySource = tableSource.getKeyMap().get(keyName);
if (keySource == null) {
String sql = "ALTER TABLE `" + tableTarget.getTableName() + "` DROP INDEX `" + keyName + "`;";
changeSqlDeque.addFirst(sql);
}
}
StringBuilder sqlStr;
for (Map.Entry<String, Key> keyEntry : tableSource.getKeyMap().entrySet()) {
sqlStr = new StringBuilder();
String keyName = keyEntry.getKey();
Key key = keyEntry.getValue();
Key keyTarget = tableTarget.getKeyMap().get(keyName);
if (keyTarget == null) {
sqlStr.append(appendKey(tableSource, key));
changeSqlDeque.addLast(sqlStr.toString());
} else {
if (key.equals(keyTarget)) {
continue;
} else {
String sql = "ALTER TABLE `" + tableTarget.getTableName() + "` DROP INDEX `" + keyName + "`;";
changeSqlDeque.addFirst(sql);
sqlStr.append(appendKey(tableSource, key));
changeSqlDeque.addLast(sqlStr.toString());
}
}
}
}
/**
* @param tableSource
* @param key
* @return
*/
private String appendKey(Table tableSource, Key key) {
StringBuilder sqlStr = new StringBuilder();
sqlStr.append("ALTER TABLE `")
.append(tableSource.getTableName())
.append("` ADD INDEX `")
.append(key.getIndexName())
.append("` USING ")
.append(key.getIndexType())
.append(" (");
StringBuilder columnStr = new StringBuilder();
for (String columnName : key.getColumnList()) {
columnStr.append("`")
.append(columnName)
.append("`,");
}
// 去除最后一个","
sqlStr.append(columnStr.toString(), 0, columnStr.toString().length() - 1);
sqlStr.append(")");
if (StrKit.notBlank(key.getIndexComment())) {
sqlStr.append(" comment '")
.append(key.getIndexComment())
.append("'");
}
sqlStr.append(";");
return sqlStr.toString();
}
/**
* @param name
* @return
*/
public static String addQuotes(String name) {
return " `" + name + "` ";
}
}
