如何从数据库层面检测两表内容的一致性，层面一致性
一般来说呢，如何检测两张表的内容是否一致，这样的需求大多在从机上体现，以保证数据一致性。方法无非有两个，第一呢就是从数据库着手，第二呢就是从应用程序端着手。 我这里罗列了些如何从数据库层面来解决此类问题的方法。当然第一步就是检查记录数是否一致，否则不用想任何其他方法了。
这里我们用两张表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，这里检测出来结果不对，那么就直接给出不一致的结论。当然这点我也用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)
所以从上面几种数据库提供的方法来看，用减法来归零相对来说比较可靠，其他的方法比较适合在特定的情形下来检测。
数据库一致性检查(dbcc)提供了一些命令用于检查数据库的逻辑和物理一致性。Dbcc主要有两个功能：
使用checkstorage 或 checktable 及 checkdb 在页一级和行一级检查页链及数据指针。
使用checkstorage, checkalloc, 或 checkverify, tablealloc, 及indexalloc
检查页分配。
在下列情况中需要使用 dbcc 命令： 作为数据库日常维护工作的一部分, 数据库内部结构的完整性决定于sa 或dbo 定期地运行
dbcc 检查。 在系统报错以后, 确定数据库是否有损坏。 在备份数据库之前, 确保备份的完整性。 如果怀疑数据库有损坏时, 例如,
使用某个表时报出表损坏的信息, 可以使用 dbcc 确定数据库中其他表是否也有损坏。
下面是dbcc的简单用法： dbcc checktable (table_name) 检查指定的表,
检查索引和数据页是否正确链接, 索引是否正确排序, 所有指针是否一致, 每页的数据信息是否合理, 页偏移是否合理。 dbcc
checkdb (database_name) 对指定数据库的所有表做和checktable 一样的检查。 dbcc
checkalloc (database_name,fix|nofix) 检查指定数据库, 是否所有页面被正确分配,
是否被分配的页面没被使用。当使用"fix"选项时，在检查数据库的同时会自动修复有问题的页面。（若数据库数据量很大，则该过程会持续很长时间。）
dbcc tablealloc (table_name,fix|nofix) 检查指定的表, 是否所有页面被正确分配,
是否被分配的页面没被使用。是 checkalloc 的缩小版本,
对指定的表做完整性检查。当使用"fix"选项时，在检查数据表的同时会自动修复数据表中有问题的页面。
关于上述命令的其它选项及详细使用方法和checkstorage, checkverify, indexalloc
的详细使用方法, 请参阅有关命令手册。 举例1： Unix平台检查pubs2数据库的一致性 单用户模式启动Server：
$SYBASE/install startserver -f RUN_server_name -m
vi dbcc_db.sqluse mastergosp_dboption pubs2,"single user",truegouse pubs2gocheckpoint go dbcc checkdb(pubs2)godbcc checkalloc(pubs2,fix)godbcc checkcatalog(pubs2)gouse mastergosp_dboption pubs2,"single user",falsegouse pubs2gocheckpointgoquit go isql -Usa -Pxxxxxx -SSYBASE dbcc_db.out
grep Msg dbcc_db.out
举例2： Unix平台检查pubs2数据库中titles表的一致性
数据库一致性检查(dbcc)提供了一些命令用于检查数据库的逻辑和物理一致性。Dbcc主要有两个功能：使用checkstorage 或 checktable 及 checkdb 在页一级和行一级检查页链及数据指针。使用checkstorage, checkalloc, 或 checkverify, tablealloc, 及indexalloc检查页分配。 在下列情况中需要使用 dbcc 命令： 作为数据库日常维护工作的一部分, 数据库内部结构的完整性决定于sa 或dbo 定期地运行dbcc 检查。 在系统报错以后, 确定数据库是否有损坏。 在备份数据库之前, 确保备份的完整性。 如果怀疑数据库有损坏时, 例如,使用某个表时报出表损坏的信息, 可以使用 dbcc 确定数据库中其他表是否也有损坏。 下面是dbcc的简单用法： dbcc checktable (table_name) 检查指定的表,检查索引和数据页是否正确链接, 索引是否正确排序, 所有指针是否一致, 每页的数据信息是否合理, 页偏移是否合理。 dbcccheckdb (database_name) 对指定数据库的所有表做和checktable 一样的检查。 dbcccheckalloc (database_name,fix|nofix) 检查指定数据库, 是否所有页面被正确分配,是否被分配的页面没被使用。当使用"fix"选项时，在检查数据库的同时会自动修复有问题的页面。（若数据库数据量很大，则该过程会持续很长时间。）dbcc tablealloc (table_name,fix|nofix) 检查指定的表, 是否所有页面被正确分配,是否被分配的页面没被使用。是 checkalloc 的缩小版本,对指定的表做完整性检查。当使用"fix"选项时，在检查数据表的同时会自动修复数据表中有问题的页面。 关于上述命令的其它选项及详细使用方法和checkstorage, checkverify, indexalloc的详细使用方法, 请参阅有关命令手册。