MySql development of automatic synchronization table structure

MySql development of automatic synchronization table structure

Development Pain Points

During the development process, due to frequent modifications to database fields, the database tables in the RD and QA environments are often inconsistent.

However, since these database modification operations may be performed by multiple RDs, it is difficult to collect all of them at once. It is very tedious to manually check the fields in the QA environment, and it is easy to miss them.

Solution

So I wrote a program that can automatically compare the table structures of two databases and generate alter statements. It can also be configured to automatically execute these alter statements. See github for details

principle

Synchronize the newly added table

If a new table is added in the rd environment but not in the qa environment, this program can directly output the create table statement. Here’s how it works:

The sql used are mainly:

show table from rd_db;
show create table added_table_name;

Synchronization table structure

If the rd table structure has been changed, but the qa environment has not, this program can directly output the alter statement. The principle is as follows:

The sql used are:

select 
 COLUMN_NAME,COLUMN_TYPE,IS_NULLABLE,COLUMN_DEFAULT,COLUMN_COMMENT,EXTRA 
from 
 information_schema.columns
where 
  TABLE_SCHEMA='rd_db'
  and TABLE_NAME = 'rd_table';

Code for comparing table structures:

        for (Column column : sourceTable.getColumns().values()) {
            if (targetTable.getColumns().get(column.getName()) == null) {
                // If the corresponding target does not have this field, directly alter
                String sql = "alter table " + target.getSchema() + "." + targetTable.getTableName() + " add " + column
                        .getName() + " ";
                sql += column.getType() + " ";
                if (column.getIsNull().equals("NO")) {
                    sql += "NOT NULL ";
                } else {
                    sql += "NULL";
                }
                if (column.getDefaultValue() != null) {
                    sql += "DEFAULT " + SqlUtil.getDbString(column.getDefaultValue()) + " ";
                }
                if (column.getComment() != null) {
                    sql += "COMMENT " + SqlUtil.getDbString(column.getComment()) + " ";
                }
                if (after != null) {
                    sql += "after " + after;
                }
                changeSql.add(sql+";");
            } else {
                // Check the corresponding source and target attributes String sql =
                        "alter table " + target.getSchema() + "." + targetTable.getTableName() + " change " + column
                                .getName() + " ";
                Column sourceColumn = column;
                Column targetColumn = targetTable.getColumns().get(sourceColumn.getName());
                // Compare the two fields. If null is returned, it indicates consistency. String sqlExtend = compareSingleColumn(sourceColumn, targetColumn);
                if (sqlExtend != null) {
                    changeSql.add(sql + sqlExtend+";");
                }
            }
            after = column.getName();
        }

Synchronize index structure

If the index of the rd table has changed but the qa environment has not, this program can directly output the modification index statement. The principle is similar to the above and will not be repeated here.

Configuration

sourceHost=127.0.0.1:3306
sourceUser=root
sourcePass=123123123
sourceSchema=mystique_db
sourceCharset=utf8

targetHost=127.0.0.1:3306
targetUser=root
targetPass=123123123
targetSchema=mystique_test
targetCharset=utf8

autoExecute=YES //This indicates automatic synchronization

run

Configure according to the template above and open it with IDE. Find

alchemystar.runner.ShellRunner 

Just run the main method

Generated effect display

alter table mystique_test.t_test_3 change id id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ''
alter table mystique_test.t_test_3 add index (name)
alter table mystique_test.t_test_3 drop index name_id
alter table mystique_test.t_test_3 add id_2 varchar(50) NULL DEFAULT '' COMMENT '' after name

If auto-execution is turned on, these statements will be executed automatically

github link

https://github.com/alchemystar/Lancer

Code Cloud Link

https://git.oschina.net/alchemystar/Lancer

Summarize

This is the end of this article about MySql development and automatic synchronization of table structure. For more relevant MySql automatic synchronization of table structure content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • MySQL realizes the synchronization of data between two tables
  • Detailed explanation of the implementation process of dual-master synchronization of partial tables in MySQL 5.7

<<:  Learn how to deploy and start multiple tomcats and migrate projects in one article

>>:  Copy and paste is the enemy of packaging

Recommend

jQuery realizes the sliding effect of drop-down menu

When we make a web page, sometimes we want to hav...

Linux common text processing commands and vim text editor

Today, let's introduce several common text pr...

Vue implements countdown function

This article example shares the specific code of ...

Detailed explanation of mysql user variables and set statement examples

Table of contents 1 Introduction to user variable...

The use of setState in React and the use of synchronous and asynchronous

In react, if you modify the state directly using ...

Solution to MySQL error code 1862 your password has expired

The blogger hasn't used MySQL for a month or ...

Two practical ways to enable proxy in React

Two ways to enable proxy React does not have enca...

Solution to the problem that the docker container cannot be stopped

The solution is as follows: 1. Force delete conta...

Solve the problem of IDEA configuring tomcat startup error

The following two errors were encountered when co...

Analyze the compilation and burning of Linux kernel and device tree

Table of contents 1. Prepare materials 2. Downloa...

mysql backup script and keep it for 7 days

Script requirements: Back up the MySQL database e...

Detailed explanation of webpage screenshot function in Vue

Recently, there is a requirement for uploading pi...

Detailed explanation of docker entrypoint file

When writing a Dockerfile, include an entrypoint ...

Complete steps to use samba to share folders in CentOS 7

Preface Samba is a free software that implements ...

MySQL 5.7.18 winx64 installation and configuration method graphic tutorial

The installation of compressed packages has chang...