我們在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:
SELECTid,MD5(CONCAT(IFNULL(id,''),IFNULL(c1,''),IFNULL(c2,''),)) AS md5_valueFROM 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_valueFROM t_test01GROUP 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值對比,最終找到差異行。
最終對比圖:
小編應大家的需求,更新了MYSQL的文章,大家還需要什麼文章留言小編哦,求關注啊求收藏啊!另外,更多精彩內容詳細瞭解尚學堂!
閱讀更多 火到沒朋友的大數據 的文章