Mysql 简明教程

MySQL - Handling Duplicates

数据库中的表或结果集通常包含重复记录。虽然通常允许重复，但在某些情况下有必要防止重复。在这种情况下，识别并从数据库表中删除重复记录至关重要。

处理数据库中的重复数据变得必要的原因有多种。主要原因之一是组织数据库中存在重复数据会导致逻辑错误。此外，我们需要处理冗余数据以防止以下后果 -

您可以在表上使用 PRIMARY KEY 或 UNIQUE 索引（带有适当的字段）以防止重复记录条目进入表中。

下表不包含此类索引或主键，因此它允许 first_name 和 last_name 的重复记录。

CREATE TABLE CUSTOMERS (
   first_name CHAR(20),
   last_name CHAR(20),
   sex CHAR(10)
);

为了防止创建带有相同名字和姓氏值的多个记录，请向其定义中添加一个 PRIMARY KEY 。执行此操作时，还需要声明索引列为 NOT NULL ，因为 PRIMARY KEY 不允许 NULL 值 -

CREATE TABLE CUSTOMERS (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

Using INSERT IGNORE Query −

表中存在唯一索引通常会在尝试插入重复索引列中现有记录的记录时导致错误。

为了在不生成错误的情况下处理这种情况，您可以使用“INSERT IGNORE”命令。当记录不是重复记录时，MySQL 会像往常一样插入记录。但是，如果记录是重复的，“IGNORE”关键字会指示 MySQL 在不产生错误的情况下丢弃它。

提供的示例不会导致错误，它还确保不插入重复记录 -

INSERT IGNORE INTO CUSTOMERS (LAST_NAME, FIRST_NAME)
VALUES ('Jay', 'Thomas'), ('Jay', 'Thomas');

我们获得以下输出 −

Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 1  Warnings: 1

Using REPLACE Query −

不要使用 INSERT 命令，而应使用 REPLACE 命令。在处理新记录时，像使用 INSERT 命令一样插入。但是，如果它是重复的，则此新记录替换旧记录。

REPLACE INTO CUSTOMERS (LAST_NAME, FIRST_NAME)
VALUES ( 'Ajay', 'Kumar'), ( 'Ajay', 'Kumar');

以下是上面代码的输出： -

Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

您在 INSERT IGNORE 和 REPLACE 命令之间做出的选择应取决于您希望实现的特定重复处理行为。INSERT IGNORE 命令保留第一组重复的记录，并丢弃剩下的记录。另一方面，REPLACE 命令会保留最后一组重复的记录，并删除所有之前的实例。

Using UNIQUE Constraint −

实施唯一性的另一种方法是向表中添加 UNIQUE 索引，而不是 PRIMARY KEY −

CREATE TABLE CUSTOMERS (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   UNIQUE (last_name, first_name)
);

您可以使用 COUNT 函数和 GROUP BY 子句按特定列统计和识别重复的记录。

以下是对表中带有 first_name 和 last_name 的重复记录进行统计的查询 −

SELECT COUNT(*) as repetitions, last_name, first_name
FROM CUSTOMERS
GROUP BY last_name, first_name
HAVING repetitions > 1;

该查询将返回 CUSTOMERS 表中所有重复记录的列表。一般来说，要识别重复的值集，请按照以下步骤操作。

您可以将 DISTINCT 命令与 SELECT 语句结合使用，以找出表中可用的唯一记录。

SELECT DISTINCT last_name, first_name
FROM CUSTOMERS
ORDER BY last_name;

DISTINCT 命令的替代方法是添加一个 GROUP BY 子句，指定您正在选择的列。这种方法消除了重复项，并且仅从指定列中检索唯一值组合。

SELECT last_name, first_name
FROM CUSTOMERS
GROUP BY (last_name, first_name);

如果表中存在重复的记录，并且您想从表中删除所有重复的记录，请按照以下步骤进行操作 −

CREATE TABLE tmp AS
SELECT DISTINCT last_name, first_name, sex
FROM CUSTOMERS;

DROP TABLE CUSTOMERS;
ALTER TABLE tmp RENAME TO CUSTOMERS;