作者：楊濤濤
正好最近在幫客戶從達夢資料庫遷移到 MySQL。我也來簡單說說重複數據的處理。
存放在資料庫中的數據分為三種：
1. 一種是經過嚴格意義過濾出來的數據。比如程序端過濾數據源、資料庫端在表欄位上設置 check 標記過濾數據源、設置觸發器過濾、調用存儲過程過濾等等；
2. 另一種是原始的沒有經過任何處理的數據。比如程序端代碼異常導致產生非正常的想要的數據、資料庫端沒有設置任何過濾規則的數據保留等等。這樣會產生一系列垃圾數據，當然也包含了我今天要說的重複的數據。
3. 最後一種是 SQL 語句在執行過程中可能產生的重複數據。比如兩表外聯，總會產生一系列 NULL。
今天我要說的重複數據，不包含 SQL 語句在執行中產生的重複數據，只包含了原始重複數據的處理。接下來，用幾個經典的場景來說下。
第一種，記錄完全重複，這其實是最最簡單的去重場景。
比如無主鍵的表 d1
mysql-(ytt/3305)->show create table d1\G*************************** 1. row *************************** Table: d1Create Table: CREATE TABLE `d1` ( `r1` int(11) DEFAULT NULL, `r2` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)
記錄數總共為四百萬。
mysql-(ytt/3305)->select count(*) from d1 limit 2;+----------+| count(*) |+----------+| 4000000 |+----------+1 row in set (0.18 sec)
可以看到足足有四分之三的記錄是重複的。
mysql-(ytt/3305)->select count(distinct r1,r2) from d1 ;+-----------------------+| count(distinct r1,r2) |+-----------------------+| 1000000 |+-----------------------+1 row in set (2.68 sec)
比如記錄（1，1）就有四條。
mysql-(ytt/3305)-> select * from db1 order by r1,r2 limit 5;+------+------+| r1 | r2 |+------+------+| 1 | 1 || 1 | 1 || 1 | 1 || 1 | 1 || 2 | 2 |+------+------+5 rows in set (1.65 sec)
這種去重非常簡單，要麼在資料庫層做，要麼把數據導出來篩選好在導到資料庫里來。
在資料庫里做，無非就是新建一張克隆表，完了把正常數據篩選出來，再重新命名後，刪掉原來的表，步驟也不是非常繁瑣，例子如下：
mysql-(ytt/3305)->create table d2 like d1;Query OK, 0 rows affected (0.01 sec)
時間主要耗費在去重並且插入新表這裡
mysql-(ytt/3305)->insert into d2 select distinct r1,r2 from d1;Query OK, 1000000 rows affected (19.40 sec)Records: 1000000 Duplicates: 0 Warnings: 0mysql-(ytt/3305)->alter table d1 rename to d1_bak;Query OK, 0 rows affected (0.00 sec)mysql-(ytt/3305)->alter table d2 rename to d1;Query OK, 0 rows affected (0.00 sec)mysql-(ytt/3305)->drop table d1_bak;Query OK, 0 rows affected (0.00 sec)
上面總共花了大概 20 秒的樣子，再來看看在系統層面上去重，先導出數據，
mysql-(ytt/3305)->select * from db1 into outfile '/var/lib/mysql-files/d1.txt';Query OK, 4000000 rows affected (1.84 sec)
系統層面去重，用 OS 自帶的工具 sort 和 uniq。
root@ytt-pc:/var/lib/mysql-files# time cat d1.txt |sort -g |uniq > d1_uniq.txtreal 0m7.345suser 0m7.528ssys 0m0.272s
導入到原表，
mysql-(ytt/3305)->truncate table d1;Query OK, 0 rows affected (0.05 sec)root@ytt-pc:/var/lib/mysql-files# mv d1_uniq.txt d1.txt
把處理好的數據直接導入到資料庫
root@ytt-pc:/home/ytt/scripts# time mysqlimport -uytt -pytt -P3305 -h 127.0.0.1 --use-threads=2 -vvv ytt /var/lib/mysql-files/d1.txtmysqlimport: [Warning] Using a password on the command line interface can be insecure.Connecting to 127.0.0.1Selecting database yttLoading data from SERVER file: /var/lib/mysql-files/d1.txt into d1ytt.d1: Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 0Disconnecting from 127.0.0.1real 0m3.272suser 0m0.012ssys 0m0.008s
看下處理好的記錄，
mysql-(ytt/3305)->select * from d1 where 1 order by r1,r2 limit 2;+------+------+| r1 | r2 |+------+------+| 1 | 1 || 2 | 2 |+------+------+2 rows in set (0.40 sec)
OS 層面稍微效率高些，總體包括數據導出，數據去重，數據導入，差不多是資料庫層時間的一半。
第二種，其實和第一種類似，不同的是表有主鍵，但是其他的欄位記錄值是重複的。
舉個例子，表 d4 除了加了主鍵，其他的記錄和之前的一模一樣。記錄如下：
mysql-(ytt/3305)->select * from d4 order by r1,r2 limit 5;+---------+------+------+| id | r1 | r2 |+---------+------+------+| 1 | 1 | 1 || 3000001 | 1 | 1 || 2000001 | 1 | 1 || 1000001 | 1 | 1 || 2 | 2 | 2 |+---------+------+------+5 rows in set (1.08 sec)
但是這種一般就得需要和具體的業務商量了，比如我需要留下重複記錄的最大主鍵值，比如上面這個，留下最大的 id 為 3000001 這條記錄。這樣的去重一條 sql 就搞定了，
mysql-(ytt/3305)->delete a from d4 a left join (select max(id) id from d4 group by r1, r2) b using(id) where b.id is null;Query OK, 3000000 rows affected (23.29 sec)
去掉了 300W 行重複記錄，剩下四分之一的正常數據。
mysql-(ytt/3305)->select count(*) from d4;+----------+| count(*) |+----------+| 1000000 |+----------+1 row in set (0.06 sec)
來看下效果，保留了最大值，其他的刪掉了。
mysql-(ytt/3305)->select * from d4 order by r1,r2 limit 5;+---------+------+------+| id | r1 | r2 |+---------+------+------+| 3000001 | 1 | 1 || 3000002 | 2 | 2 || 3000003 | 3 | 3 || 3000004 | 4 | 4 || 3000005 | 5 | 5 |+---------+------+------+5 rows in set (0.25 sec)
第三種，不同於前面兩種，這種體現在欄位值里多餘的字符，比如空格，多餘的換行符等。依然看看幾個例子：
1. 去掉欄位值內前後的空白字符，是這類里最簡單的。這種 MySQL 有現成的函數，一條基礎的 SQL 即可。
表y11 有500W行示例數據mysql-(ytt/3305)->select count(*) from y11;+----------+| count(*) |+----------+| 5242880 |+----------+1 row in set (0.30 sec)利用trim函數。mysql-(ytt/3305)->update y11 set r1 = trim(r1), r2 = trim(r2);Query OK, 5242880 rows affected (2 min 1.56 sec)Rows matched: 5242880 Changed: 5242880 Warnings: 0mysql-(ytt/3305)->select * from y11 limit 5;+----+------------------------+------------------------+| id | r1 | r2 |+----+------------------------+------------------------+| 1 | sql server | sql server || 2 | sql server | sql server || 3 | sql server | sql server || 6 | db2 mysql oracle mysql | db2 mysql oracle mysql || 7 | db2 mysql oracle mysql | db2 mysql oracle mysql |+----+------------------------+------------------------+5 rows in set (0.00 sec)
2. 去掉單詞中間的各種空白字符（空格，換行，制表符等)；單詞前後，中間都有空格的場景。
依然是表 y11，從結果來看，各種換行符，空格已經讓結果無法正常顯示了。
mysql-(ytt/3305)->select * from y11 limit 5;+----+-----------------------------------------------------+------------------------------------------------------+| id | r1 | r2 |+----+-----------------------------------------------------+------------------------------------------------------+| 1 | sql server | sql server || 2 | sql server | sql server |server | sql server | | mysql | db2 mysql oracle | 7 | db2 mysql oracle mysql | db2 mysql oracle mysql+----+-----------------------------------------------------+------------------------------------------------------+5 rows in set (0.00 sec)
可能最先想到的方法是把數據導出為文本文件，完了用 linux 上的各種工具處理完了再導進去，比如：
mysql-(ytt/3305)->select * from y11 into outfile '/var/lib/mysql-files/y11.txt' fields terminated by ',' enclosed by '"';Query OK, 5242880 rows affected (3.54 sec)mysql-(ytt/3305)->truncate y11;Query OK, 0 rows affected (0.23 sec)
用 sed 處理下，替換掉所有的空白字符。
root@ytt-pc:/var/lib/mysql-files# time sed -i 's/\s\+/ /g' y11.txtreal 0m27.476suser 0m20.105ssys 0m7.233s
導入到表 y11
mysql-(ytt/3305)->load data infile '/var/lib/mysql-files/y11.txt' into table y11 fields terminated by ',' enclosed by '"';Query OK, 5242880 rows affected (30.25 sec)Records: 5242880 Deleted: 0 Skipped: 0 Warnings: 0
上面雖然達成了目的，但是過程過於繁瑣，如果 MySQL 層實在解決不了再考慮。
完全可以利用 MySQL 的正則替換功能直接替換掉多餘的字符為一個空格，也是一條簡單的 SQL。
mysql-(ytt/3305)->update y11 set r1 = regexp_replace(r1,'[[:space:]]+',' '), r2 = regexp_replace(r2,'[[:space:]]+',' ');Query OK, 4194304 rows affected (1 min 32.05 sec)Rows matched: 5242880 Changed: 4194304 Warnings: 0
只是時間上稍微長些，不過也影響不是很大。
mysql-(ytt/3305)->select * from y11 limit 5;+----+------------------------+-------------------------+| id | r1 | r2 |+----+------------------------+-------------------------+| 1 | sql server | sql server || 2 | sql server | sql server || 3 | sql server | sql server || 6 | db2 mysql oracle mysql | db2 mysql oracle mysql || 7 | db2 mysql oracle mysql | db2 mysql oracle mysql |+----+------------------------+-------------------------+5 rows in set (0.00 sec)
我想可能日常數據處理難免會有數據去重的場景，希望這部分內容對大家有幫助。