MySQL删除重复数据保留一条|极客笔记

MySQL删除重复数据保留一条

在使用MySQL进行数据操作时，有时会遇到数据中存在重复的记录，为了保持数据的一致性和有效性，我们通常需要去除这些重复数据，只保留其中一条。本文将详细介绍如何使用MySQL删除重复数据并保留一条。

1. 使用DISTINCT关键字查询并插入新表

首先，我们可以使用DISTINCT关键字查询出不重复的记录，并将结果插入到一个新的表中。具体操作步骤如下：

步骤一：创建新表

CREATE TABLE new_table LIKE old_table;

步骤二：插入不重复的记录到新表

INSERT INTO new_table
SELECT DISTINCT *
FROM old_table;

示例代码及运行结果：

-- 创建新表
CREATE TABLE new_table LIKE old_table;

-- 插入不重复的记录到新表
INSERT INTO new_table
SELECT DISTINCT *
FROM old_table;

运行结果：新表new_table中保存了去重后的记录。

2. 使用DELETE JOIN语句删除重复数据

另一种常用的方法是使用DELETE JOIN语句，在删除重复数据的同时保留一条记录。具体操作步骤如下：

步骤一：创建临时表

CREATE TABLE temp_table AS
SELECT MIN(id) AS id
FROM old_table
GROUP BY duplicate_column;

步骤二：删除重复数据

DELETE old_table
FROM old_table
JOIN temp_table ON old_table.id = temp_table.id;

示例代码及运行结果：

-- 创建临时表
CREATE TABLE temp_table AS
SELECT MIN(id) AS id
FROM old_table
GROUP BY duplicate_column;

-- 删除重复数据
DELETE old_table
FROM old_table
JOIN temp_table ON old_table.id = temp_table.id;

运行结果：old_table中的重复数据已被删除，只保留了一条记录。

3. 使用ROW_NUMBER函数删除重复数据

另一种删除重复数据的方法是使用ROW_NUMBER函数。该函数可以为每一行数据添加一个序列号，我们可以根据序列号删除重复数据。具体操作步骤如下：

步骤一：添加序列号

SELECT *
FROM (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY duplicate_column ORDER BY id) AS rn
    FROM old_table
) AS temp_table

步骤二：删除重复数据

DELETE old_table
FROM old_table
JOIN (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY duplicate_column ORDER BY id) AS rn
    FROM old_table
) AS temp_table ON old_table.id = temp_table.id
WHERE rn > 1;

示例代码及运行结果：

-- 添加序列号
SELECT *
FROM (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY duplicate_column ORDER BY id) AS rn
    FROM old_table
) AS temp_table

-- 删除重复数据
DELETE old_table
FROM old_table
JOIN (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY duplicate_column ORDER BY id) AS rn
    FROM old_table
) AS temp_table ON old_table.id = temp_table.id
WHERE rn > 1;

运行结果：old_table中的重复数据已被删除，只保留了一条记录。

4. 使用临时表删除重复数据

除了上述方法，还可以使用临时表的方式删除重复数据。具体操作步骤如下：

步骤一：创建临时表

CREATE TABLE temp_table AS
SELECT MIN(id) AS id
FROM old_table
GROUP BY duplicate_column;

步骤二：创建新表

CREATE TABLE new_table LIKE old_table;

步骤3：将保留的记录插入到新表

INSERT INTO new_table
SELECT *
FROM old_table
WHERE id IN (SELECT id FROM temp_table);

示例代码及运行结果：

-- 创建临时表
CREATE TABLE temp_table AS
SELECT MIN(id) AS id
FROM old_table
GROUP BY duplicate_column;

-- 创建新表
CREATE TABLE new_table LIKE old_table;

-- 将保留的记录插入到新表
INSERT INTO new_table
SELECT *
FROM old_table
WHERE id IN (SELECT id FROM temp_table);

运行结果：new_table中保留了去重后的记录。

5. 使用EXISTS子查询删除重复数据

最后一种方法是使用EXISTS子查询，它可以根据某个条件判断是否存在符合条件的数据。具体操作步骤如下：

步骤一：删除重复数据

DELETE FROM old_table T1
WHERE EXISTS (
    SELECT 1
    FROM old_table T2
    WHERE T2.duplicate_column = T1.duplicate_column
    AND T2.id < T1.id
);

示例代码及运行结果：

-- 删除重复数据
DELETE FROM old_table T1
WHERE EXISTS (
    SELECT 1
    FROM old_table T2
    WHERE T2.duplicate_column = T1.duplicate_column
    AND T2.id < T1.id
);

运行结果：old_table中的重复数据已被删除，只保留了一条记录。