A method of two database table structures by MySQL

In the development and debugging process, need to be compared to the old and new code differences, we can use GIT/SVN and other version control tools to do the comparison. Different versions of the database table structure also differ, and we also need to compare the differences and get the updated structure of the SQL statement.

For example, the same set of code, in the development environment is normal, in the test environment problems, in addition to check the server settings, but also to the development environment and test environment of the database table structure is different. After the differences are found, the test Environment database table structure needs to be updated until the database table structure of the test environment is developed.

We can use the Mysqldiff tool to implement SQL statements that compare to the database table structure and get the updated structure.

1.mysqldiff Installation Method

The Mysqldiff tool is in the mysql-utilities package, and running mysql-utilities requires the installation of a dependency Mysql-connector-python

Mysql-connector-python Installation

Download Address: https://dev.mysql.com/downloads/connector/python/

Mysql-utilities Installation

Download Address: https://downloads.mysql.com/archives/utilities/

Because I am using a Mac system, you can directly use the brew installation.

Brew Install Caskroom/cask/mysql-connector-pythonbrew Install Caskroom/cask/mysql-utilities

After installation, perform a view version of the command, if you can display the version to indicate successful installation

Mysqldiff--versionmysql Utilities mysqldiff version 1.6.5 License Type:gplv2

2.mysqldiff How to use

Command:

Mysqldiff--server1=root@host1--server2=root@host2--difftype=sql db1.table1:dbx.table3

Parameter description:

--server1 Specify Database 1--server2 Specify database 2

Comparison you can target a single database and specify only the SERVER1 option to compare different table structures in the same library.

--difftype How difference information is displayed

Unified (default)
Show Unified Format Output

Context
Show context Format output

differ
Show formatting output for different styles

SQL
Show SQL conversion statement output

If you want to get SQL conversion statements, use SQL to display the most appropriate display.

--character-set specifies that the character set--changes-for is used to specify the object to be converted, that is, the direction in which the variance is generated, by default Server1--changes-for=server1 represents the structure server1 to be server2. Server2 mainly. --changes-for=server2 said Server2 to Server1 structure, server1-based. --skip-table-options ignores Auto_increment, ENGINE, charset differences. --version View Version

For more information on how to use Mysqldiff, refer to the official documentation:
Https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqldiff.html

3. Example

Creating test database tables and data

Create DATABASE testa;create database Testb;use testa; CREATE TABLE ' TBA ' (' id ' int () unsigned not null auto_increment, ' name ' varchar (+) ', ' age ' int (ten) unsigned NO T null, ' addtime ' int (ten) unsigned not NULL, PRIMARY KEY (' id ')) engine=innodb auto_increment=1001 DEFAULT charset=utf8;in SERT into ' TBA ' (name,age,addtime) VALUES (' Fdipzone ', 18,1514089188); CREATE TABLE ' TBB ' (' id ' int () unsigned not null auto_increment, ' name ' varchar () ' is not null ', ' age ' int (ten) ' is not NULL ' Addtime ' int (ten) not NULL, PRIMARY KEY (' id ')) engine=innodb the DEFAULT charset=utf8;insert into ' TBB ' (name,age,addtime) Valu Es (' Fdipzone ', 19,1514089188);

Perform difference comparison, set Server1 as Main, server2 to SERVER1 database table structure

Mysqldiff--server1=root@localhost--server2=root@localhost--changes-for=server2--difftype=sql testa.tba: testb.tbb;# server1 on localhost: ... connected.# server2 on localhost: .... connected.# comparing Testa.tba to Testb.tbb
  [fail]# transformation for--changes-for=server2: #ALTER TABLE ' Testb '. "TBB" Change   COLUMN addtime addtime Int (10 ) unsigned not NULL, change column-age-age   Int (ten) unsigned NOT NULL, change   column name name varchar (+) NOT NULL , RENAME to Testa.tba, auto_increment=1002;# Compare failed. One or more differences found.

Execute update SQL statement returned by Mysqldiff

mysql> ALTER TABLE ' testb ', ' TBB '   , change COLUMN addtime addtime Int (ten) unsigned not NULL,     Change column Age age   int (ten) unsigned not NULL, and change   column name name varchar (+) not null; Query OK, 0 rows affected (0.03 sec)

Perform Mysqldiff again, structure is not different, only auto_increment differ

Mysqldiff--server1=root@localhost--server2=root@localhost--changes-for=server2--difftype=sql testa.tba: testb.tbb;# server1 on localhost: ... connected.# server2 on localhost: .... connected.# comparing Testa.tba to Testb.tbb
  [fail]# transformation for--changes-for=server2: #ALTER TABLE ' Testb '. "TBB" RENAME to Testa.tba, auto_increment=1002 ; # Compare failed. One or more differences found.

Set the Ignore auto_increment and then make the difference.

Mysqldiff--server1=root@localhost--server2=root@localhost--changes-for=server2--skip-table-options--difftype= SQL testa.tba:testb.tbb;# server1 on localhost: ... connected.# server2 on localhost: .... connected.# comparing TESTA.TBA To TESTB.TBB                                 [pass]# Success. All objects is the same.

This article explains MySQL compared to the two database table structure method, more relevant knowledge, please focus on the PHP Chinese web.