PHP开发教程

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忽略保持第一套重复记录,并丢弃剩下的。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的记录重复

从查询结果消除重记录

可以使用SELECT语句以及DISTINCT一起在一个表中找出可用唯一记录。

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) 或 主键(PRIMAY KEY)到该表。即使该表已经提供,可以使用此技术来删除重复的记录。

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


Powered by PHP二次开发 ©2008-2017