mysql 同步 表结构_mysql表结构同步

同步两张表之间的表结构。 模仿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 changeSqlDeque = new LinkedBlockingDeque<>();

/**

* @param source

* @param target

*/

public CompareUtil(MetaData source, MetaData target) {

this.sourceMetaData = source;

this.targetMetaData = target;

}

public void com