Shulou(Shulou.com)05/31 Report--

This article mainly introduces mysql how to compare the structure of the two database tables, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

In the process of development and debugging, we need to compare the differences between new and old code, we can use version control tools such as git/svn to compare. There are also differences in the table structure of different versions of the database, so we also need to compare the differences and get the sql statements to update the structure.

For example, the same set of code is normal in the development environment, but there is a problem in the test environment. In addition to checking the server settings, it is also necessary to compare the database table structure between the development environment and the test environment. After finding the difference, you need to update the database table structure of the test environment until the development is consistent with the database table structure of the test environment.

We can use the mysqldiff tool to implement the sql statement to compare the database table structure and get the updated structure.

1.mysqldiff installation method

The mysqldiff tool is in the mysql-utilities package, while running mysql-utilities requires installation that depends on 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 the mac system, I can install it directly using brew.

Brew install caskroom/cask/mysql-connector-pythonbrew install caskroom/cask/mysql-utilities

Execute the view version command after installation. If the version can be displayed, the installation is successful.

Mysqldiff-- versionMySQL Utilities mysqldiff version 1.6.5 License type: GPLv2

How to use 2.mysqldiff

Command:

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

Parameter description:

-- server1 specifies database 1

-- server2 specifies database 2

Alignment can be specific to a single database, and only specifying the server1 option allows you to compare different table structures in the same library.

-- the display method of difftype difference information

Unified (default)

Display uniform format output

Context

Display context format output

Differ

Display format output of different styles

Sql

Show SQL conversion statement output

If you want to get the sql conversion statement, it is most appropriate to use sql as a display.

-- character-set specified character set

-- changes-for is used to specify the object to be converted, that is, the direction in which the difference is generated. The default is server1.

-- changes-for=server1 means that server1 will be converted to the structure of server2, dominated by server2.

-- changes-for=server2 means that server2 will be converted to the structure of server1, dominated by server1.

-- skip-table-options ignores the differences among AUTO_INCREMENT, ENGINE and CHARSET.

-- version view version

For more information on how to use mysqldiff parameters, please see the official documentation:

Https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqldiff.html

3. Example

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 difference comparison, set server1 primarily, and change 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 (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)

When mysqldiff is executed again for comparison, there is no difference in structure, only in AUTO_INCREMENT.

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, and pass the comparison.

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. Thank you for reading this article carefully. I hope the article "how to compare two database table structures with mysql" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.