MySQL刪除重複記錄並保留第一條

問題描述：

有一張表因為沒有加重複判斷，導致會有些重複資料存在，現在需要根據重複規則去掉重複的記錄，只保留第一條。

解決方案：

1、兩表自關聯後按照主鍵刪除，類似於Oracle的rowid

-- 刪除主表 col是本表的主鍵                  
DELETE FROM tmp101 A
 WHERE col NOT IN (SELECT MIN(col)
                   FROM tmp101 b
                  WHERE A.col1 = b.col1
                    AND A.col2 = b.col2
                    AND A.col3 = b.col3
                    AND A.col4 = b.col4);

2、採用inner join的方式找出需要刪掉的資料

DELETE FROM tmp101 WHERE col1 in(
SELECT DISTINCT(a.col1) FROM tmp101 a
INNER JOIN tmp101 b
ON a.col1 = b.col1 AND a.col2 = b.col2 
AND a.col3 = b.col3 AND a.col4 = b.col4
AND a.col > b.col);

[Err] 1093 - You can't specify target table 'tmp101' for update in FROM clause

MySQL就有一個很尷尬的地方，就是必須要把表包起來，才不報錯。如下

DELETE FROM tmp101 WHERE col1 in(
SELECT * FROM(
SELECT DISTINCT(a.col1) FROM tmp101 a
INNER JOIN tmp101 b
ON a.col1 = b.col1 AND a.col2 = b.col2 
AND a.col3 = b.col3 AND a.col4 = b.col4
AND a.col > b.col)a);

題外話：

在mysql8.0下是不支援row_number()over(partition by  order by)的。

實現思路如下：

SELECT
IF(t1.id = @id and t1.name = @name,@rownum := @rownum + 1,@rownum := 1)AS rownum,
t1.*,
@id := t1.id,
@name := t1.name
FROM
(SELECT @rownum := 0,@id := NULL,@name := NULL) r,
test1 t1
ORDER BY t1.id,t1.name,t1.starttime DESC;

語法解析：

以id和starttime進行排序可以理解為id分組內starttime排序；

test1表關聯@rownum ：=0，@id ：=null,在select中if判斷@id是否等於當前行的id，因為初始為null，所以@rownum ：=1，當id變化的時候，@id還是上一個id的值，此時@rownum重置為1，注意@id ：=t1.id一定要放在if判斷後面。

方案三：實現row_number

DELETE FROM tmp101 WHERE col1 in(
select col from(
select a.*,@rownum:=@rownum+1 rownum,
IF(@col1 = a.col1 AND @col2 = a.col2 AND @col3 = a.col3 AND @col4 = a.col4 ,
@rank := @rank + 1,
@rank := 1) AS rank,
@col1 := a.col1 , @col2 := a.col2 , @col3 := a.col3 , @col4 := a.col4
from (SELECT * FROM tmp101 ORDER BY col1,col2,col3,col4) a,
(select @col1 := null,@col2 := null,@col3 := null,@col4 := null,
@rownum:=null,@rank := null) tmp
) a WHERE rank <> 1) a;

小故事：

初次改寫這個語句的時候，因為沒有分清MySQL的=和:=導致查詢出來解決不對。

下面簡單科普下 MySQL的=和:=的區別。

=只有在set和update時才是和:=一樣，賦值的作用，其它都是等於的作用。鑑於此，用變數實現行號時，必須用:=

不只在set和update時起到賦值的作用，在select也是賦值的作用。

簡單來說就是=是等於的作用，判斷相等。 :=是賦值的作用。

如果用混了會導致結果不正確！