MySQL处理重复项
通常,表或结果集有时包含重复的记录。在大多数情况下,它是允许的,但有时需要停止重复的记录。需要标识重复的记录并将其从表中删除。本章将介绍如何防止表中出现重复记录,以及如何删除已经存在的重复记录。
防止表中出现重复项
你可以使用PRIMARY KEY或者UNIQUE在具有适当字段的表上建立索引以停止重复的记录。
让我们举个例子–下表不包含这样的索引或主键,因此它将允许重复的记录用于名和姓。
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);