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