MySQL中处理各种重复的一些方法

数据库  /  管理员 发布于 6年前   159

有时表或结果集包含重复的记录。有时它是允许的，但有时它需要停止重复的记录。有时它需要识别重复的记录从表中删除。本章将介绍如何防止发生在一个表中重复的记录如何删除已经存在的重复记录。
防止重复表中的发生：

可以使用PRIMARY KEY或UNIQUE索引的表上相应的字段来防止重复的记录。让我们举一个例子，下面的表中不包含这样的索引或主键，所以它会允许记录first_name和last_name重复

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

可以使用PRIMARY KEY或UNIQUE索引的表上相应的字段防止重复的记录。让我们举一个例子，下面的表中不包含这样的索引或主键，所以它会允许重复first_name和last_name记录

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)

使用替换而不是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)

使用REPLACE而不是INSERT。如果记录是新的它INSERT插入。如果它是一个重复的，新的记录将取代旧的：

强制唯一性的另一种方法是添加一个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;

从一个表中删除重复记录的一个简单的方法是添加KEY，表索引或PRIMAY。如果该表已经是可用的，那么使用此方法删除重复的记录。

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