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,超出部分会被阶段。
下一篇: 微信小程序转换PDF文档的操作过程