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

Mobile development tutorial: Summary of pixel display issues

Preface I believe that in the process of mobile t...

Vue 2.0 Basics in Detail

Table of contents 1. Features 2. Examples 3. Opti...

How to control the startup order of docker compose services

summary Docker-compose can easily combine multipl...

MySQL series 6 users and authorization

Table of contents Tutorial Series 1. User Managem...

Detailed explanation of Vue development Sort component code

Table of contents <template> <ul class=&...

Why can't the MP4 format video embedded in HTML be played?

The following code is in my test.html. The video c...

Docker FAQ

Docker only maps ports to IPv6 but not to IPv4 St...

MySQL Database Iron Laws (Summary)

Good database specifications help reduce the comp...

Solution to the problem of session failure caused by nginx reverse proxy

A colleague asked for help: the login to the back...

JavaScript commonly used array deduplication actual combat source code

Array deduplication is usually encountered during...

VMware, nmap, burpsuite installation tutorial

Table of contents VMware BurpSuite 1. Virtual mac...

MyBatis dynamic SQL comprehensive explanation

Table of contents Preface Dynamic SQL 1. Take a l...

Solution to Apache cross-domain resource access error

In many cases, large and medium-sized websites wi...