MySQL 如何尋找重複的文章 (文字類型資料)

網路文章資料，有時候可能會包含重複的文章資料，可以使用 MySQL SQL 查詢語法的 MD5 函數來解決。

Photo by Ben Cheung on StockSnap

問題狀況

下圖範例資料的編號 (id 欄位) 3 與 4 有重複的文章內容 (content 欄位)，當資料筆數很多時，如何找到重複資料？

問題解決方式

如果資料量約數萬筆以內，可以使用 MD5 演算法，關於 MD5 (資料來源：維基百科)

MD5訊息摘要演算法（MD5 Message-Digest Algorithm），一種被廣泛使用的密碼雜湊函數，可以產生出一個128位元（16個字元(BYTES)）的雜湊值（hash value），用於確保資訊傳輸完整一致。

方法1：找到文章內容完全重複的 SQL 查詢

MySQL 的 MD5 函數的 SQL 查詢語法

SELECT id, content, MD5(content)

FROM `docs`;

查詢結果：可以看到重複內容的編號 (id 欄位) 3 與 4 文章內容的 MD5 雜湊值是一樣的。

修改查詢語法，只要列出重複內容的資料

SELECT `id`, `content`, MD5(`content`) AS md5

FROM `docs`

WHERE MD5(`content`) IN (

  SELECT tmp.md5

  FROM

  (

    SELECT COUNT(*) AS count, MD5(`content`) AS md5

    FROM `docs`

    GROUP BY MD5(`content`)

    HAVING count > 1

  ) AS tmp

);

查詢結果：

方法2：找到文章部分內容重複的 SQL 查詢

真實資料比較複雜，有時候希望可以提高容錯度。文章主要內容（通常是文章前面段落）不要重複，而允許文章後面可以有點不一樣。就可以改取文章的前面數十字來計算 MD5 雜湊值。

MySQL 查詢語法 (取文章前面 5 個字來作比較，可自行調整字數)

SELECT `id`, `content`, MD5(LEFT(`content`, 5)) AS md5

FROM `docs`

WHERE MD5(LEFT(`content`, 5)) IN (

  SELECT tmp.md5

  FROM

  (

    SELECT COUNT(*) AS count, MD5(LEFT(`content`, 5)) AS md5

    FROM `docs`

    GROUP BY MD5(LEFT(`content`, 5))

    HAVING count > 1

  ) AS tmp

)

;

查詢結果：

👉 如果想要直接動手試試看，請前往 SQL Fiddle

參考資料

• MySQL :: MySQL 8.0 Reference Manual :: 12.13 Encryption and Compression Functions
• MySQL :: MySQL 8.0 Reference Manual :: 12.8 String Functions and Operators

附錄：建立測試資料

CREATE TABLE IF NOT EXISTS `docs` (

  `id` int(6) unsigned NOT NULL,

  `content` varchar(200) NOT NULL,

  PRIMARY KEY (`id`)

) DEFAULT CHARSET=utf8;

INSERT INTO `docs` (`id`, `content`) VALUES

  ('1', '地球是平的'),

  ('2', '一百個天使可以在針尖上跳舞'),

  ('3', '地球是平的且支撐在牛角上'),

  ('4', '地球是平的且支撐在牛角上'),

  ('5', '地球像一個球。');