mysql-資料一致性檢測

標籤：mysql   資料庫一致性檢測   

一般來說呢，如何檢測兩張表的內容是否一致，這樣的需求大多在從機上體現，以保證資料一致性。方法無非有兩個，第一呢就是從資料庫著手，第二呢就是從應用程式端著手。 我這裡羅列了些如何從資料庫層面來解決此類問題的方法。
當然第一步就是檢查記錄數是否一致，否則不用想任何其他方法了。

這裡我們用兩張表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: 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> 所以在這點上，這個方法等於是無效。方法二： 用減法來歸零。由於<a href="http://www.it165.net/database/dbmy/" target="_blank" class="keylink">MySQL</a> 沒有提供減法操作符，這裡我們換做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 msOK，這裡檢測出來結果不對，那麼就直接給出不一致的結論。第三種： 用全表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 mst_girl=# 結果為0，證明內容一致。第四種： 用checksum校正。比如在<a href="http://www.it165.net/database/dbmy/" target="_blank" class="keylink">MySQL</a> 裡面，如果兩張表的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: 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)所以從上面幾種資料庫提供的方法來看，用減法來歸零相對來說比較可靠，其他的方法比較適合在特定的情形下來檢測。

本文出自 “Mr_Computer” 部落格，請務必保留此出處http://caochun.blog.51cto.com/4497308/1547078

mysql-資料一致性檢測