MySQL 如何删除没有主键的重复行|极客笔记

MySQL 如何删除没有主键的重复行

在MySQL数据库中，有时候会出现没有主键的情况，这样就会出现重复行的情况。而对于这些重复行，我们想要进行删除，就需要使用一些特殊的操作方法。本文将围绕这一主题，介绍如何删除没有主键的重复行。

阅读更多：MySQL 教程

概述

在MySQL数据库中，主键是一个用于标识某个记录的唯一标识符。主键的存在可以有效地保证数据库表中的记录是唯一的，避免了出现重复数据的情况。但是，在某些情况下，出于性能或其他方面的考虑，我们可能没有设置主键。

当然，如果表中没有主键，那么删除重复行就会变得有些困难。因为删除某个记录的唯一依据就是主键，如果没有主键，我们需要根据其他方式进行判断。

删除方法

在MySQL中，我们可以使用以下两个方法来删除没有主键的重复行：

方法一：使用子查询

使用子查询的方法可以删除表中的重复行，并保留一个记录。步骤如下：

首先使用SELECT语句来查找出所有的重复记录：

 SELECT col1, col2, col3, COUNT(*) 
 FROM table1
 GROUP BY col1, col2, col3
 HAVING COUNT(*) > 1;

在这个查询语句中，我们将表名命名为“table1”。其中，col1、col2、col3是用于判断重复的字段。这些字段联合起来，应该构成一个唯一值。

通过子查询将所有的重复记录中，保留第一个记录，并删除其他记录：

DELETE FROM table1
 WHERE id NOT IN (
     SELECT id
     FROM (
         SELECT MIN(id) AS id
         FROM table1
         GROUP BY col1, col2, col3
     ) AS tblTemp
 );

在这个操作中，我们首先筛选出所有的重复记录，并通过子查询记录下来，然后通过DELETE语句将保留第一个重复记录的其他记录全部删除。

需要注意的是，上述代码中的“id”仅仅是某一个能够区分记录的字段名，该字段可以为任何一个字段名。本代码中使用“id”仅仅是为了方便演示。

方法二：使用TEMPORARY TABLE

在这种方法中，我们需要创建一个临时表，用来存储被删除的重复记录。步骤如下：

首先创建一个临时表，并将需要去重的字段插入临时表中：

 CREATE TEMPORARY TABLE tempTable1
     SELECT DISTINCT col1, col2, col3 
     FROM table1;

将原表删除，并保留临时表中仅存的唯一的记录：

TRUNCATE table1;

INSERT INTO table1 (
 SELECT * FROM tempTable1
);

在这个操作中，我们首先将需要去重的字段插入临时表中，通过DISTINCT关键字将原始表中的重复记录去掉。然后，我们通过TRUNCATE语句来清空原始表，最后将仅剩的唯一记录从临时表中插入到原始表中。

示例

为了更好地说明我们上述的操作方法，我们可以通过一个示例来演示整个操作流程。

在这个示例中，我们拥有一个名为students的表，该表代表了一个学生的信息。其中，该表没有主键，我们需要使用不同的方法来删除重复记录。students表的结构如下：

id	name	age	gender	score
1	Tom	20	M	65\|Tom \| 20 \| M \| 85
66	Lucy	18	F	95
67	Lucy	18	F	95
68	Jack	19	M	87
69	Jack	0	M	90

在这个表中，我们可以看到有两条重复记录，分别是id为67和68的记录。我们需要将这两条重复记录删除，并保留其中一条。

方法一：使用子查询

使用子查询的方法可以删除表中的重复行，并保留一个记录。在本例中，我们将name、age、score这三列合并作为判断重复的关键字。下面是删除重复记录的操作：

首先使用SELECT语句来查找出所有的重复记录：

SELECT name, age, score, COUNT(*) 
FROM students
GROUP BY name, age, score
HAVING COUNT(*) > 1;

执行结果：

name	age	score	COUNT(*)
Jack	19	87	2
Lucy	18	95	2

在这个查询语句中，我们将表名命名为“students”。其中，我们将name、age、score三列合并起来作为判断重复的关键字。

通过子查询将所有的重复记录中，保留第一个记录，并删除其他记录：

DELETE FROM students
WHERE id NOT IN (
    SELECT id
    FROM (
        SELECT MIN(id) AS id
        FROM students
        GROUP BY name, age, score
    ) AS tblTemp
);

执行结果：

id	name	age	gender	score
1	Tom	20	M	75
2	Lucy	18	F	95
65	Tom	20	M	85
67	Lucy	18	F	95
69	Jack	0	M	90

在这个操作中，我们通过子查询获取到了所有的重复记录，并保留了其中的第一条记录。因此，最终表中仅保留了一条重复记录。

方法二：使用TEMPORARY TABLE

在这种方法中，我们需要创建一个临时表，用来存储被删除的重复记录。在本例中，我们将name、age、score这三列合并作为判断重复的关键字。下面是删除重复记录的操作：

首先创建一个临时表，并将需要去重的字段插入临时表中：

CREATE TEMPORARY TABLE tempTable
SELECT DISTINCT name, age, score 
FROM students;

在这个操作中，我们将name、age、score三列合并起来，通过DISTINCT关键字将原始表中的重复记录去掉，并且将结果插入到临时表中。

将原表删除，并保留临时表中仅存的唯一的记录：

TRUNCATE TABLE students;

INSERT INTO students (
    SELECT * 
    FROM tempTable
);

在这个操作中，我们首先清空students表，并将临时表中的唯一记录插入到原始表students中。

执行结果：

id	name	age	gender	score
1	Tom	20	M	75
2	Lucy	18	F	95
65	Tom	20	M	85
67	Lucy	18	68	Jack \| 19 \| M \| 87
69	Jack	0	M	90

在本例中，我们通过TEMPORARY TABLE的方式，将原表中的重复记录删除，并保留唯一的记录。因此，最终表中仅保留了一条重复记录。

总结

在MySQL中，如果表中没有主键，那么删除重复记录会变得比较困难。但是，我们可以使用子查询或者TEMPORARY TABLE的方式来删除表中的重复记录。在使用这些方法时，我们需要根据不同的情况来选择最适合的操作方法。同时，在执行删除操作之前，我们需要非常小心，确保我们不会删除错误的记录。

MySQL 如何删除没有主键的重复行