How to detect the consistency of two-table content from the database level

Generally speaking. How to check whether the contents of the two tables are consistent, this demand is mostly reflected from the machine, to ensure data consistency. There are two methods, the first one is to start with the database. The second is to start with the application side.

I've got a list of ways to solve this kind of problem from the database level.
The first step, of course, is to check that the records are consistent, otherwise you don't have to think of any other method.
Here we use two table t1_old,t1_new to demonstrate.

Table structure: CREATE table t1_old (  ID int (one) not NULL,  log_time timestamp DEFAULT NULL); The CREATE table t1_new (  ID int (one) is not NULL,  log_time timestamp DEFAULT NULL), and both tables have a record count of 100. Mysql> Select COUNT (*) from t1_old;+----------+| COUNT (*) |+----------+|      |+----------+1 Row in Set (0.31 sec) mysql> Select COUNT (*) from t1_new;+----------+| COUNT (*) |+----------+|      |+----------+1 Row in Set (0.00 sec)

method One: with addition and then go heavy.

Because the union itself has the ability to sort the top and bottom two connected records, it is easy to detect. Mysql> Select COUNT (*) from (SELECT * FROM T1_old Union SELECT * from T1_new) as t;+----------+|      COUNT (*) |+----------+| |+----------+1 Row in Set (0.06 sec) here the number of records is 100, the preliminary proof of the two table content is consistent. However, there is a bug in this approach, and in some cases it is not possible to simply represent a consistent result set.
For example:mysql> CREATE TABLE t1_old1 (id int); Query OK, 0 rows affected (0.27 sec) mysql> CREATE TABLE t1_new1 (id int); Query OK, 0 rows affected (0.09 sec) mysql> insert into t1_old1 values (1), (2), (3), (5); Query OK, 4 rows affected (0.15 sec) records:4 duplicates:0 warnings:0mysql> insert INTO t1_new1 values (2), (2), (3)    , (5); Query OK, 4 rows Affected (0.02 sec) records:4 duplicates:0 warnings:0mysql> SELECT * FROM t1_old1;+------+|    ID |+------+|    1 | |    2 | |    3 | | 5 |+------+4 rows in Set (0.00 sec) mysql> SELECT * FROM t1_new1;+------+|    ID |+------+|    2 | |    2 | |    3 | | 5 |+------+4 rows in Set (0.00 sec) mysql> Select COUNT (*) from (SELECT * FROM T1_old1 Union SELECT * from T1_new1) as T ;+----------+|        COUNT (*) |+----------+| 4 |+----------+1 row in Set (0.00 sec) mysql> so at this point. This method is equal to invalid.

method Two: use subtraction to zero.

Because MySQL does not provide a subtraction operator. Here we'll do a test for PostgreSQL.
t_girl=# Select COUNT (*) from (SELECT * from T1_old except select * from T1_new) as T; Count-------     0 (1 row) time:1.809 Ms Here the result is 0. Then it proves that the contents of the two tables are identical. Then we can detect the second case mentioned in the first method: t_girl=# Select COUNT (*) from (SELECT * from T1_old1 except select * from T1_new1) as T; Count-------     1 (1 row) time:9.837 Msok, where the results are not correct, then give an inconsistent conclusion directly.

Third: With the full table join, this is the worst practice, of course, I mean in the case of the table record number of super.

Of course, I also use PostgreSQL to demonstrate t_girl=# select COUNT (*) from t1_old as a full outer join t1_new as B using (id,log_time) where a.id I s null or b.id is null;  Count-------     0 (1 row) time:5.002 mst_girl=# result is 0, the proof content is consistent.

The fourth kind: with checksum check.

For example, inside MySQL. Assuming that the checksum values of the two tables are consistent, the content is consistent.
mysql> Checksum table t1_old;+---------------+----------+| Table | Checksum |+---------------+----------+| T_girl.t1_old | 60614552 |+---------------+----------+1 row in Set (0.00 sec) mysql> Checksum table t1_new;+---------------+--------- -+| Table | Checksum |+---------------+----------+| T_girl.t1_new | 60614552 |+---------------+----------+1 row in Set (0.00 sec) But this approach is only confined to the two-table structure of a touch. For example, if I change the field type T1_old the table below, then the checksum value will be different.
Mysql> ALTER TABLE t1_old modify ID bigint; Query OK, rows affected (0.23 sec) records:100 duplicates:0 warnings:0mysql> checksum table t1_old;+----------- ----+------------+| Table | Checksum |+---------------+------------+| T_girl.t1_old | 3211623989 |+---------------+------------+1 row in Set (0.00 sec) mysql> Checksum table t1_new;+---------------+----- -----+| Table | Checksum |+---------------+----------+| T_girl.t1_new | 60614552 |+---------------+----------+1 row in Set (0.00 sec)

So from the methods provided in the above several databases, it is relatively reliable to use subtraction to zero, and other methods are better suited to detect in a particular situation.

How to detect the consistency of two-table content from the database level