上一节:
下一节:

  MySQL 处理重复项

  • 重复项

    通常，表或结果集有时包含重复的记录。在大多数情况下，它是允许的，但有时需要停止重复的记录。需要标识重复的记录并将其从表中删除。本章将描述如何防止表中出现重复记录，以及如何删除已经存在的重复记录。

  • 防止表中出现重复项

    您可以在具有适当字段的表上使用PRIMARY KEY或UNIQUE索引来停止重复记录。让我们举个例子–下表不包含此类索引或主键，因此它将允许重复记录first_name和last_name。
    例-试试下面的例子。这将创建表，然后将在该表中插入几行，由于MySQL自动将其递增，因此无需提供记录ID。 
    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);
上一节:
下一节: