在 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, 超出部分会被阶段
参考链接: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
来源: http://www.linuxidc.com/Linux/2018-02/150768.htm