MySQL快速對(duì)比數(shù)據(jù)技巧

在MySQL運(yùn)維中，研發(fā)同事想對(duì)比下兩個(gè)不同實(shí)例上的數(shù)據(jù)并找出差異，除主鍵外還需要對(duì)比每一個(gè)字段，如何做呢？

第一種方案，寫程序?qū)蓚€(gè)實(shí)例上的每一行數(shù)據(jù)取出來(lái)進(jìn)行對(duì)比，理論可行，但是對(duì)比時(shí)間較長(zhǎng)。

第二種方案，對(duì)每一行數(shù)據(jù)所有字段合并起來(lái)，取checksum值，再按照checksum值對(duì)比，看著可行，嘗試下。

首先要合并所有字段的值，選用MySQL提供的CONCAT函數(shù)，如果CONCAT函數(shù)中包含NULL值，會(huì)導(dǎo)致最終結(jié)果為NULL，因此需要使用IFNULL函數(shù)來(lái)替換NULL值，如:

CONCAT(IFNULL(C1,''),IFNULL(C2,''))

加入表有很多行，手動(dòng)拼個(gè)腳本比較累，別急，可以使用information_schema.COLUMNS來(lái)處理：

## 獲取列名的拼接串
SELECT
GROUP_CONCAT('IFNULL(',COLUMN_NAME,','''')')
FROM information_schema.COLUMNS 
WHERE TABLE_NAME='table_name';

假設(shè)我們有測(cè)試表：

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

在兩個(gè)實(shí)例上執(zhí)行下，然后把結(jié)果使用beyond compare對(duì)比下，就很容易找出不相同的行以及主鍵ID

對(duì)于數(shù)據(jù)量較大的表，執(zhí)行出來(lái)的結(jié)果集也很大，對(duì)比起來(lái)比較費(fèi)勁，那就先嘗試縮小結(jié)果集，可以將多行記錄的md5值合并起來(lái)求MD5值，如果最后MD5值相同，則這些行相同，如果不同，則證明存在差異，再按照這些行進(jìn)行逐行對(duì)比。

假設(shè)我們按照1000行一組來(lái)進(jìn)行對(duì)比，如果需要將分組后的結(jié)果合并，需要使用GROUP_CONCAT函數(shù)，注意在GROUP_CONCAT函數(shù)中添加排序保證合并數(shù)據(jù)的順序， 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)

執(zhí)行結(jié)果為：

min_id  max_id  row_count  md5_value
0    999    1000     7d49def23611f610849ef559677fec0c
1000   1999    1000     95d61931aa5d3b48f1e38b3550daee08
2000   2999    1000     b02612548fae8a4455418365b3ae611a
3000   3999    1000     fe798602ab9dd1c69b36a0da568b6dbb 

當(dāng)差異數(shù)據(jù)較少時(shí)，即使需要對(duì)比上千萬(wàn)數(shù)據(jù)，我們可以輕松根據(jù)根據(jù)min_id和max_id來(lái)快速定位到哪1000條數(shù)據(jù)里存在差異，再進(jìn)行逐行MD5值對(duì)比，最終找到差異行。

最終對(duì)比圖：

PS:

在使用GROUP_CONCAT時(shí)，需要配置MySQL變量group_concat_max_len，默認(rèn)值為1024，超出部分會(huì)被階段。