MySQLi - 处理重复数据

表或结果集有时包含重复记录。有时，这是允许的，但有时需要阻止重复记录。有时，需要识别重复记录并将其从表中删除。本章将介绍如何防止表中出现重复记录以及如何删除已存在的重复记录。

防止表中出现重复记录

您可以使用表中具有适当字段的主键或唯一索引来阻止重复记录。让我们举一个例子：下表不包含此类索引或主键，因此它将允许 first_name 和 last_name 的重复记录。

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

为了防止在此表中创建具有相同 first 和 last name 值的多个记录，请将其定义中添加一个主键。执行此操作时，还需要将索引列声明为 NOT NULL，因为主键不允许 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 保留一组重复项中的最后一个，并删除任何较早的重复项。

另一种强制唯一性的方法是向表添加唯一索引而不是主键。

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;

删除表中重复记录的一种简单方法是向该表添加索引或主键。即使此表已经可用，您也可以使用此技术删除重复记录，并且将来也将安全无虞。

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

打印页面