Mysqli 简明教程
MySQLi - Handling Duplicates
表或结果集有时会包含重复记录。有时允许这样做,但有时需要停止重复记录。有时,需要识别重复记录并将其从表中删除。本章将介绍如何防止重复记录出现在表中,以及如何删除已存在的重复记录。
Preventing Duplicates from Occurring in a Table
你可以在具有适当字段的表上使用 PRIMARY KEY 或 UNIQUE 索引来停止重复记录。我们举一个例子:下表没有这样的索引或主键,因此它将允许 first_name 和 last_name 出现重复记录。
CREATE TABLE person_tbl (
first_name CHAR(20),
last_name CHAR(20),
sex CHAR(10)
);
若要防止在此表中创建具有相同 first 和 last 名称值的多个记录,请在其定义中添加 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)
);
Counting and Identifying Duplicates
以下是计算表中带有 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 来消除唯一值。
Eliminating Duplicates from a Query Result:
你可以将 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);
Removing Duplicates Using Table Replacement
如果你在表中复制记录并且希望从该表中删除所有重复记录,那么这里有一个过程 -
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 或 PRIMAY KEY。即使此表已经可用,你也可以使用此技术来删除重复记录,并且将来你也将安全。
mysql> ALTER IGNORE TABLE person_tbl
→ ADD PRIMARY KEY (last_name, first_name);