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索引而不是 PRIMARY KEY。

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);