MySQL快速对比数据技巧

在mysql运维中，研发同事想对比下两个不同实例上的数据并找出差异，除主键外还需要对比每一个字段，如何做呢？

第一种方案，写程序将两个实例上的每一行数据取出来进行对比，理论可行，但是对比时间较长。

第二种方案，对每一行数据所有字段合并起来，取checksum值，再按照checksum值对比，看着可行，尝试下。

首先要合并所有字段的值，选用mysql提供的concat函数，如果concat函数中包含null值，会导致最终结果为null，因此需要使用ifnull函数来替换null值，如:

concat(ifnull(c1,''),ifnull(c2,''))

加入表有很多行，手动拼个脚本比较累，别急，可以使用information_schema.columns来处理：

## 获取列名的拼接串
select
group_concat('ifnull(',column_name,','''')')
from information_schema.columns 
where table_name='table_name';

假设我们有测试表：

create table t_test01
(
 id int auto_increment primary key,
 c1 int,
 c2 int
)

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

select
id,
md5(concat(
ifnull(id,''),
ifnull(c1,''),
ifnull(c2,''),
)) as md5_value
from t_test01

在两个实例上执行下，然后把结果使用beyond compare对比下，就很容易找出不相同的行以及主键id

对于数据量较大的表，执行出来的结果集也很大，对比起来比较费劲，那就先尝试缩小结果集，可以将多行记录的md5值合并起来求md5值，如果最后md5值相同，则这些行相同，如果不同，则证明存在差异，再按照这些行进行逐行对比。

假设我们按照1000行一组来进行对比，如果需要将分组后的结果合并，需要使用group_concat函数，注意在group_concat函数中添加排序保证合并数据的顺序， 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_value
from t_test01
group by (id div 1000)

执行结果为：

min_id  max_id  row_count  md5_value
0    999    1000     7d49def23611f610849ef559677fec0c
1000   1999    1000     95d61931aa5d3b48f1e38b3550daee08
2000   2999    1000     b02612548fae8a4455418365b3ae611a
3000   3999    1000     fe798602ab9dd1c69b36a0da568b6dbb 

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

最终对比图：

ps:

在使用group_concat时，需要配置mysql变量group_concat_max_len，默认值为1024，超出部分会被阶段。