首頁 > 資料庫 > mysql教程 > [原创]如何从数据库层面检测两表内容的一致性

[原创]如何从数据库层面检测两表内容的一致性

WBOY
發布: 2016-06-07 14:52:57
原創
1202 人瀏覽過

一般来说呢,如何检测两张表的内容是否一致,这样的需求大多在从机上体现,以保证数据一致性。方法无非有两个,第一呢就是从数据库着手,第二呢就是从应用程序端着手。 我这里罗列了些如何从数据库层面来解决此类问题的方法。 当然第一步就是检查记录数是否

一般来说呢,如何检测两张表的内容是否一致,这样的需求大多在从机上体现,以保证数据一致性。方法无非有两个,第一呢就是从数据库着手,第二呢就是从应用程序端着手。 我这里罗列了些如何从数据库层面来解决此类问题的方法。

当然第一步就是检查记录数是否一致,否则不用想任何其他方法了。

这里我们用两张表t1_old,t1_new来演示。

表结构:
 CREATE TABLE t1_old (
  id int(11) NOT NULL,
  log_time timestamp DEFAULT NULL
) ;
 CREATE TABLE t1_new (
  id int(11) NOT NULL,
  log_time timestamp DEFAULT NULL
) ;
两表的记录数都为100条。
mysql> select count(*) from t1_old;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.31 sec)
mysql> select count(*) from t1_new;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)
登入後複製


方法一:用加法然后去重。

由于Union 本身具备把上下两条连接的记录做唯一性排序,所以这样检测来的非常简单。
mysql> select count(*) from (select * from t1_old union select * from t1_new) as T;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.06 sec)
这里的记录数为100,初步证明两表内容一致。但是,这个方法有个BUG,在某些情形下不能简单表示结果集一致。
比如:
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: 0
mysql> insert into t1_new1 values (2),(2),(3),(5);    
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> 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> 
所以在这点上,这个方法等于是无效。
登入後複製


方法二: 用减法来归零。

由于MySQL 没有提供减法操作符,这里我们换做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
这里检测出来结果是0,那么证明两表的内容一致。 那么我们可以针对第一种方法提到的另外一种情况做检测:
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,这里检测出来结果不对,那么就直接给出不一致的结论。

第三种: 用全表JOIN,这个也是最烂的做法了,当然我这里指的是在表记录数超级多的情形下。

当然这点我也用PostgreSQL来演示
t_girl=# select count(*) from t1_old as a full outer join t1_new as b using (id,log_time) where a.id is null or b.id is null; 
 count 
-------
     0
(1 row)
Time: 5.002 ms
t_girl=# 
结果为0,证明内容一致。
登入後複製


第四种: 用checksum校验。

比如在MySQL 里面,如果两张表的checksum值一致,那么内容也就一致。
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)
但是这种方法也只局限于两表结构一摸一样。 比如,我修改下表t1_old的字段类型,那么checksum的值也就不一样了。
mysql> alter table t1_old modify id bigint;
Query OK, 100 rows affected (0.23 sec)
Records: 100  Duplicates: 0  Warnings: 0
mysql> 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)
登入後複製



所以从上面几种数据库提供的方法来看,用减法来归零相对来说比较可靠,其他的方法比较适合在特定的情形下来检测。


來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
最新問題
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板