MySQL —— 如何快速对比数据？

admin

我们在MySql中想要对比下两个不同的实例上的数据并且找出差异，除了主键之外我们还要对比每一个字段，应该怎么做呢？
方案一：写一个程序将两个实例里面的每一行数据都分别取出来对比，但是耗时我们无法估计，大概天荒地老吧。
方案二：对每一行数据所有字段合并起来，取checksum值，再按照checksum值对比，看着可行，尝试下。
我们可以先用MySql提供的CONCAT函数来合并字段的值，但是如果CONCAT中含有null值，那么就会导致最终的结果为NULL，所以我们要先用IFNULL函数来替换NULL值，比如：

[SQL] 纯文本查看 复制代码

CONCAT(IFNULL(C1,''),IFNULL(C2,''))

由于加入表有很多行，手动拼接脚本比较累，所以我们可以使用information_schema.COLUMNS来处理：
## 获取列名的拼接串SELECTGROUP_CONCAT('IFNULL(',COLUMN_NAME,','''')')FROM information_schema.COLUMNS WHERE TABLE_NAME='table_name';
假设我们有测试表：

[SQL] 纯文本查看 复制代码

CREATE TABLE t_test01(    id INT AUTO_INCREMENT PRIMARY KEY,    C1 INT,    C2 INT)

我们便可以拼接出下面的SQL:

[SQL] 纯文本查看 复制代码

SELECTid,MD5(CONCAT(IFNULL(id,''),IFNULL(c1,''),IFNULL(c2,''),)) AS md5_valueFROM t_test01

我们在这里执行了之后，把结果使用beyond compare对比下，很容易就能找出不相同的行以及主键ID。
但是数据量小一点还行，如果数据量大了，那么执行出来的结果集也是很大的，要想对比就相当费劲，我们就可以尝试缩小结果集，可以将多行记录的MD5值合并起来求MD5值，如果最后MD5的值相同，说明这些行也是相同的，如果不同的话肯定是有差异的，我们再对比这些行就可以了。
假设我们按照1000行一组来进行对比，如果需要将分组后的结果合并，需要使用GROUP_CONCAT函数，注意在GROUP_CONCAT函数中添加排序保证合并数据的顺序， SQL如下：

[SQL] 纯文本查看 复制代码

SELECT min(id) as min_id,max(id) as max_id,count(1) as row_count,MD5(GROUP_CONCAT(MD5(CONCAT(IFNULL(id,''),IFNULL(c1,''),IFNULL(c2,''),)) ORDER BY id))AS md5_valueFROM t_test01GROUP BY (id div 1000)

执行结果为：

[Actionscript3] 纯文本查看 复制代码

min_id    max_id    row_count    md5_value0         999       1000         7d49def23611f610849ef559677fec0c1000      1999      1000         95d61931aa5d3b48f1e38b3550daee082000      2999      1000         b02612548fae8a4455418365b3ae611a3000      3999      1000         fe798602ab9dd1c69b36a0da568b6dbb

当差异数据较少时，即使需要对比上千万数据，我们可以轻松根据根据min_id和max_id来快速定位到哪1000条数据里存在差异，再进行逐行MD5值对比，最终找到差异行。
最终对比图：