MySQL--如何快速对比数据

最新推荐文章于 2024-07-08 21:31:16 发布

weixin_34380948

最新推荐文章于 2024-07-08 21:31:16 发布

阅读量1k

点赞数

文章标签：数据库运维

本文介绍了一种高效的方法来对比两个MySQL实例上的数据差异。通过使用CONCAT和MD5函数组合，可以快速找出不同实例间的数据差异，特别是针对大量数据的情况。文章还提供了具体的SQL语句示例，并解释了如何通过分组进一步缩小对比范围。

摘要由CSDN通过智能技术生成

在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