在 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
##=====================================================================##
来源: https://www.cnblogs.com/TeyGao/p/8414657.html