共计 1577 个字符,预计需要花费 4 分钟才能阅读完成。
丸趣 TV 小编给大家分享一下 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
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,超出部分会被阶段。
看完了这篇文章,相信你对“MySQL 快速对比数据技巧的示例分析”有了一定的了解,如果想了解更多相关知识,欢迎关注丸趣 TV 行业资讯频道,感谢各位的阅读!