How to compare two database table structures in mysql

During the development and debugging process, it is necessary to compare the differences between the new and old codes. We can use version control tools such as git/svn for comparison. There are also differences in the database table structures of different versions. We also need to compare the differences and obtain the SQL statements for updating the structure.

For example, the same set of code works normally in the development environment but has problems in the test environment. In this case, in addition to checking the server settings, you also need to compare whether there are any differences in the database table structures between the development environment and the test environment. After finding the differences, you need to update the test environment database table structure until the development and test environment database table structures are consistent.

We can use the mysqldiff tool to compare the database table structure and obtain the SQL statement for updating the structure.

1.mysqldiff installation method

The mysqldiff tool is in the mysql-utilities package, and running mysql-utilities requires installing the 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/

Since I am using a Mac system, I can directly use brew to install it.

brew install caskroom/cask/mysql-connector-python
brew install caskroom/cask/mysql-utilities

After installation, execute the command to view the version. If the version is displayed, it means the installation is successful.

mysqldiff --version
MySQL Utilities mysqldiff version 1.6.5 
License type: GPLv2

2. How to use mysqldiff

Order:

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

Parameter Description:

--server1 specifies database 1
--server2 specifies database 2

The comparison can be performed on a single database. Specifying only the server1 option can compare different table structures in the same database.

--difftype Display difference information

unified (default)
Display unified format output

context
Display context format output

differ
Display output in different formats

sql
Display SQL conversion statement output

If you want to obtain SQL conversion statements, the SQL display mode is the most suitable.

--character-set specifies the character set

--changes-for is used to specify the object to be converted, that is, the direction of generating differences. The default is server1

--changes-for=server1 means that server1 will be converted to server2, with server2 as the primary server.

--changes-for=server2 means that server2 will be converted to the structure of server1, with server1 as the primary.

--skip-table-options Ignore differences in AUTO_INCREMENT, ENGINE, CHARSET.

--version View version

For more information about how to use mysqldiff parameters, please refer to the official documentation:
https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqldiff.html

3. Examples

Create test database tables and data

create database testa;
create database testb;

use testa;

CREATE TABLE `tba` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(25) NOT NULL,
 `age` int(10) unsigned NOT NULL,
 `addtime` int(10) unsigned NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;

insert into `tba`(name,age,addtime) values('fdipzone',18,1514089188);

use testb;

CREATE TABLE `tbb` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(20) NOT NULL,
 `age` int(10) NOT NULL,
 `addtime` int(10) NOT NULL,
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into `tbb`(name,age,addtime) values('fdipzone',19,1514089188);

Perform a difference comparison, set server1 as the main server, and server2 should be converted to the database table structure of server1

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(10) unsigned NOT NULL, 
 CHANGE COLUMN name name varchar(25) NOT NULL, 
RENAME TO testa.tba 
, AUTO_INCREMENT=1002;

# Compare failed. One or more differences found.

Execute the update SQL statement returned by mysqldiff

mysql> ALTER TABLE `testb`.`tbb` 
  -> CHANGE COLUMN addtime addtime int(10) unsigned NOT NULL, 
  -> CHANGE COLUMN age age int(10) unsigned NOT NULL, 
  -> CHANGE COLUMN name name varchar(25) NOT NULL;
Query OK, 0 rows affected (0.03 sec)

Run mysqldiff again to compare. There is no difference in structure, only AUTO_INCREMENT is different.

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 to ignore AUTO_INCREMENT and then compare the differences. The comparison passes.

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 are the same.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.