Shulou(Shulou.com)06/01 Report--

This paper gives an example of how mysql compares whether two tables have different data. Share with you for your reference, the details are as follows:

In data migration, we often need to compare two tables to identify records in one table that do not have corresponding records in the other table.

For example, we have a new database whose architecture is different from the old database. Our task is to migrate all data from the old database to the new database and verify that the data is migrated correctly. To check the data, we must compare two tables, one in the new database and one in the old database, and identify mismatched records.

Suppose you have two tables: T1 and T2. Use the following steps to compare the two tables and determine the mismatched records, according to the common idea is, we first check T1, finish, take the data result set to cycle, one by one to another table query, can find the data, is correct, the query can not be found, it is the phenomenon of data loss.

If that's the case, you can really be too young too simple. This time, let's introduce a relatively simple data comparison scheme, that is, use union all to associate two tables, and then use temporary tables or derived tables to compare data. Let's first take a look at the sql example of union all:

SELECT t1.pk, t1.c1FROM t1UNION ALLSELECT t2.pk, t2.c1FROM t2

When we're done, let's first create two tables, then insert some data, and then we can test it. Let's first look at the table:

CREATE TABLE T1 (id int auto_increment primary key, title varchar); CREATE TABLE T2 (id int auto_increment primary key, title varchar, note varchar)

After inserting data into T1 in advance:

INSERT INTO T1 (title) VALUES ('row 1'), (' row 2'), ('row 3')

Let's insert data into T2:

INSERT INTO T2 (title) VALUES ('row 1'), (' row 2'), ('row 3')

Well, the next thing we need to do is compare the data using derived tables:

SELECT id,titleFROM (SELECT id,titleFROM T1 UNION ALL SELECT id,titleFROM T2) tblGROUP BY id, titleHAVING count (*) = 1ORDER BY id

Of course, no data is returned after running, because there is no difference between them. Don't worry, let's insert a row of data into the T2 table:

INSERT INTO T2 (title,note) VALUES ('new row 4')

When we're done, we compare the values of the title column in the two tables again, because the new rows that are mismatched will be returned. Let's take a look at the results:

Mysql > SELECT id,titleFROM (SELECT id,titleFROM T1 UNION ALL SELECT id,titleFROM T2) tblGROUP BY id, titleHAVING count (*) = 1ORDER BY id;+----+-+ | id | title | +-- +-+ | 4 | new row 4 | +-+-- + 1 row in set

All right, that's all for this tip this time.

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL Common function Summary", "MySQL Log Operation skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

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.