MySQL practical skills: analysis of methods to compare whether two tables have different data

This article uses an example to describe how MySQL compares two tables to see if they 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 have no corresponding records in the other table.

For example, we have a new database with a different schema than the old one. Our task is to migrate all the data from the old database to the new database and verify that the data was migrated correctly. To check the data, we must compare two tables, one in the new database and one in the old database, and identify the records that do not match.

Assume there are two tables: t1 and t2. Use the following steps to compare the two tables and identify the unmatched records. The common idea is to check t1 first, then loop through the data result set and query the other table one by one. If the data can be found, it is correct. If not, the data is missing.

If this is true, then you are really too young and too simple. This time, let's introduce a simpler data comparison solution, which is to use union all to join two tables, and then use a temporary table or a derived table to compare the data. Let's take a look at the sql example of union all:

SELECT t1.pk, t1.c1
FROM t1
UNION ALL
SELECT t2.pk, t2.c1
FROM t2

After that, let's create two tables and insert some data. After that, we can test it. Let's look at creating the table first:

CREATE TABLE t1(
 id int auto_increment primary key,
  title varchar(255) 
);
CREATE TABLE t2(
 id int auto_increment primary key,
  title varchar(255),
  note varchar(255)
);

After that, insert data into t1:

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');

OK, let's use derived tables to compare data:

SELECT id,title
FROM (
  SELECT id, title FROM t1
  UNION ALL
  SELECT id,title FROM t2
) tbl
GROUP BY id, title
HAVING count(*) = 1
ORDER BY id;

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

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

After that, we compare the values ​​of the title column in the two tables again. Since the new row is an unmatched row, it will be returned. Let's take a look at the results:

mysql> SELECT id,title
FROM (
  SELECT id, title FROM t1
  UNION ALL
  SELECT id,title FROM t2
) tbl
GROUP BY id, title
HAVING count(*) = 1
ORDER BY id;
+----+-----------+
| id | title |
+----+-----------+
| 4 | new row 4 |
+----+-----------+
1 row in set

Okay, that’s all the tips for this time.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.