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

In general, how to detect the content of the two tables is consistent, such requirements are mostly reflected from the machine to ensure data consistency. There are two methods, the first is to start from the database, the second is to start from 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 number of records is consistent, otherwise you don't have to think of any other way.

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.

Since the union  itself has a unique sort of record of the top and bottom two connections, it is very simple to detect. Mysql> select count (*)  from  (select * from t1_old union select  * from t1_new)  as t;+----------+| count (*)  |+----------+|       100 |+----------+1 row in set  (0.06 sec) The number of records here is 100, The preliminary evidence shows that the two tables are identical. However, there is a bug in this approach, and in some cases it is not possible to simply represent a consistent result set. such as: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.

Since MySQL does not provide a subtraction operator, here we change the PostgreSQL to detect. 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, so the contents of the two tables are identical. Then we can do the detection for the other 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 ms

OK, here The result is not detected, then directly to the inconsistent conclusion.

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.

In mysql , for example, if the checksum values of the two tables are identical, then 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 limited to the two-table structure of a touch.   For example, if I modify the field type T1_old the table below, the checksum value will be different. mysql> alter table t1_old modify id bigint; query ok, 100 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 more suitable for detection in specific situations.

This article is from "God, we don't see!" "Blog, be sure to keep this provenance http://yueliangdao0608.blog.51cto.com/397025/1533259