MySQL 处理重复记录

通常，表或结果集中有时会包含重复的记录。大多数情况下是允许的，但有时需要阻止重复记录。需要识别重复记录并从表中删除它们。本章将介绍如何防止表中出现重复记录以及如何删除已经存在的重复记录。

防止表中出现重复记录

您可以在具有适当字段的表上使用 PRIMARY KEY 或 UNIQUE 索引来阻止重复记录。

让我们举个例子 – 以下表格没有这样的索引或主键，因此它将允许 first_name 和 last_name 的重复记录。

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

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

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

在表中存在唯一索引通常会导致一个错误发生，如果你在表中插入一个与列或列定义索引中的现有记录重复的记录。

使用 INSERT IGNORE 命令而不是 INSERT 命令。如果记录不重复现有记录，则MySQL会像往常一样插入它。如果记录是重复的，那么 IGNORE 关键字告诉MySQL将它静默丢弃而不生成错误。

以下示例不会出错，同时也不会插入重复的记录。

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

使用 REPLACE 命令而不是INSERT命令。如果记录是新的，它会像INSERT一样被插入。如果是重复的记录，新的记录替换旧的记录。

mysql> REPLACE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Ajay', 'Kumar');
Query OK, 1 row affected (0.00 sec)

mysql> REPLACE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Ajay', 'Kumar');
Query OK, 2 rows affected (0.00 sec)

INSERT IGNORE和REPLACE命令应根据您想要实现的去重处理行为进行选择。INSERT IGNORE命令保留第一组重复记录并丢弃其余的。REPLACE命令保留最后一组重复记录并删除之前的任何记录。

另一种实现唯一性的方式是向表添加一个 UNIQUE 索引，而不是主键索引。

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

计算和识别重复项

以下是在表中使用 first_name 和 last_name 计算重复记录的查询。

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

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

• 确定可能重复的列中包含的值。

• 在列选择列表中列出这些列，以及 COUNT(*) 。

• 在 GROUP BY 子句中列出这些列。

• 通过添加 HAVING 子句来消除唯一值，要求组计数大于1。

从查询结果中消除重复项

您可以使用 DISTINCT 命令以及SELECT语句来查找表中的唯一记录。

mysql> SELECT DISTINCT last_name, first_name
   -> FROM person_tbl
   -> ORDER BY last_name;

与DISTINCT命令相对应的另一种方法是添加一个GROUP BY子句来命名您选择的列。这将去除重复项并仅选择指定列中唯一值的组合。

mysql> SELECT last_name, first_name
   -> FROM person_tbl
   -> GROUP BY (last_name, first_name);

使用表格替换移除重复项

如果您在表格中有重复记录，并且希望从该表格中移除所有重复记录，请按照以下步骤进行。

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
   -> FROM person_tbl;
   -> GROUP BY (last_name, first_name);

mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

从表中删除重复记录的一种简单方法是将INDEX或PRIMARY KEY添加到该表。即使该表已经存在，您仍然可以使用此技术来删除重复记录，并且在将来也是安全的。

mysql> ALTER IGNORE TABLE person_tbl
   -> ADD PRIMARY KEY (last_name, first_name);