主页 / dba / 问题 / 5859
Accepted
Gary Lindahl
Asked: 2011-09-17 05:22:13 +0800 CST

删除所有重复项

  • 772

我正在尝试删除所有重复项，但仅保留单个记录（较短的 ID）。以下查询会删除重复项，但需要进行大量迭代才能删除所有副本并保留原始副本。

DELETE FROM emailTable WHERE id IN (
 SELECT * FROM (
    SELECT id FROM emailTable GROUP BY email HAVING ( COUNT(email) > 1 )
 ) AS q
)

它的MySQL。

DDL

CREATE TABLE `emailTable` (
 `id` mediumint(9) NOT NULL auto_increment,
 `email` varchar(200) NOT NULL default '',
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=298872 DEFAULT CHARSET=latin1
mysql delete

3 个回答

  1. Best Answer

    尝试这个：

    DELETE FROM emailTable WHERE NOT EXISTS (
 SELECT * FROM (
    SELECT MIN(id) minID FROM emailTable    
    GROUP BY email HAVING COUNT(*) > 0
  ) AS q
  WHERE minID=id
)

    以上内容适用于我对 50 封电子邮件的测试（5 封不同的电子邮件重复了 10 次）。

    您可能需要在“电子邮件”列上添加索引：

    ALTER TABLE emailTable ADD INDEX ind_email (email);

    250,000 行可能有点慢。在一个有 150 万行（正确索引）的表上，这对我来说很慢，这就是我想出这个策略的方式：

    /* CREATE MEMORY TABLE TO HOUSE IDs of the MIN */
CREATE TABLE email_min (minID INT, PRIMARY KEY(minID)) ENGINE=Memory;

/* INSERT THE MINIMUM IDs */
INSERT INTO email_min SELECT id FROM email
    GROUP BY email HAVING MIN(id);

/* MAKE SURE YOU HAVE RIGHT INFO */
SELECT * FROM email 
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* DELETE FROM EMAIL */
DELETE FROM email 
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* IF ALL IS WELL, DROP MEMORY TABLE */
DROP TABLE email_min;

    内存表的好处是使用了一个索引（minID 上的主键），它比普通临时表加快了进程。

    • 8

  2. 这是一个更简化的删除过程：

    CREATE TABLE emailUnique LIKE emailTable;
ALTER TABLE emailUnique ADD UNIQUE INDEX (email);
INSERT IGNORE INTO emailUnique SELECT * FROM emailTable;
SELECT * FROM emailUnique;
ALTER TABLE emailTable  RENAME emailTable_old;
ALTER TABLE emailUnique RENAME emailTable;
DROP TABLE emailTable_old;

    以下是一些示例数据：

    use test
DROP TABLE IF EXISTS emailTable;
CREATE TABLE `emailTable` (
 `id` mediumint(9) NOT NULL auto_increment,
 `email` varchar(200) NOT NULL default '',
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM;
INSERT INTO emailTable (email) VALUES
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]');
SELECT * FROM emailTable;

    我跑了他们。结果如下：

    mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS emailTable;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `emailTable` (
    ->  `id` mediumint(9) NOT NULL auto_increment,
    ->  `email` varchar(200) NOT NULL default '',
    ->  PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO emailTable (email) VALUES
    -> ('[email protected]'),
    -> ('[email protected]'),
    -> ('[email protected]'),
    -> ('[email protected]'),
    -> ('[email protected]'),
('[email protected]');
SELECT * FROM emailTable;
    -> ('[email protected]'),
    -> ('[email protected]'),
    -> ('[email protected]'),
    -> ('[email protected]'),
    -> ('[email protected]'),
    -> ('[email protected]'),
    -> ('[email protected]'),
    -> ('[email protected]'),
    -> ('[email protected]'),
    -> ('[email protected]');
Query OK, 15 rows affected (0.00 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM emailTable;
+----+----------------------------+
| id | email                      |
+----+----------------------------+
|  1 | [email protected]         |
|  2 | [email protected]         |
|  3 | [email protected]         |
|  4 | [email protected]         |
|  5 | [email protected]   |
|  6 | [email protected]   |
|  7 | [email protected]   |
|  8 | [email protected]              |
|  9 | [email protected]              |
| 10 | [email protected]              |
| 11 | [email protected]   |
| 12 | [email protected]   |
| 13 | [email protected] |
| 14 | [email protected] |
| 15 | [email protected] |
+----+----------------------------+
15 rows in set (0.00 sec)

mysql> CREATE TABLE emailUnique LIKE emailTable;
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE emailUnique ADD UNIQUE INDEX (email);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT IGNORE INTO emailUnique SELECT * FROM emailTable;
Query OK, 4 rows affected (0.01 sec)
Records: 15  Duplicates: 11  Warnings: 0

mysql> SELECT * FROM emailUnique;
+----+----------------------------+
| id | email                      |
+----+----------------------------+
|  1 | [email protected]         |
|  5 | [email protected]   |
|  8 | [email protected]              |
| 13 | [email protected] |
+----+----------------------------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE emailTable  RENAME emailTable_old;
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE emailUnique RENAME emailTable;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE emailTable_old;
Query OK, 0 rows affected (0.00 sec)

mysql>

    如图所示，emailTable 将包含每个电子邮件地址的第一次出现和相应的原始 id。对于这个例子：

    CAVEAT：我通过临时表方法回答了一个类似的关于表删除的问题。

    试试看 ！！！

    • 4

  3. 这是一个真正快速的 Itzik 解决方案。这将适用于 SQL 2005 及更高版本。

    WITH Dups AS
(
  SELECT *,
    ROW_NUMBER()
      OVER(PARTITION BY email ORDER BY id) AS rn
  FROM dbo.emailTable
)
DELETE FROM Dups
WHERE rn > 1;
    • 1

相关问题