使用php编程从Mysql数据库删除重复的主键 - Delete duplicate primary keys from a Mysql database using php programming

Delete duplicate primary keys from a Mysql database using php programming

我的数据库中有三个字段:名字，姓氏和电子邮件。电子邮件是我的主键。我的数据库中没有其他字段。

我需要找到一个mysql查询，可以从数据库中删除重复的主键和它们的值，在数据库中只留下一个唯一的电子邮件。

我使用以下命令显示所有重复的主键。它工作了，但我需要删除所有其他重复的条目，只保留一个。我正在使用php编程。

SELECT *
FROM table_name
WHERE primarykey IN (
SELECT primarykey
FROM table_name
GROUP BY primarykey
HAVING count(primarykey) > 1
)
ORDER BY primarykey

用您想要保留的表填充临时表，在其他列上使用GROUP BY、HAVING和MAX。然后运行删除太多内容的查询，然后将复制的内容放回去。然后让它成为实际的PK，这样就不会再发生了

您不能只使用一个查询，因为您将需要使用limit，并且需要对其进行硬设置。(例如，你不能说limit someColumn。)

$query = "select primarykey, count(primarykey) as count from table_name group by primarykey having count(primarykey) > 1"
$result = $mysqli->query($query);
while ($row = $result->fetch_assoc()) {
    $query = "delete from table_name where primarykey = ? limit " . $row['count'];
    $stmt = $mysqli->prepare($query);
    $stmt->bind_param('s', $row['primarykey']);
    $stmt->execute();
}

Email是不是你的主键。主键在不允许重复的地方有约束。顺便说一下，您的问题只是一个示例，说明为什么您希望在所有表上都有一个自动递增的数字主键。似乎太晚了。

解决这个问题的一个方法是使用临时表。这个想法是复制数据，然后重新插入:

create temporary table tmp_emails as
    select email, firstname, lastname
    from emails
    group by email;
truncate table emails;
insert into emails(email, firstname, lastname)
   select email, firstname, lastname
    from tmp_emails;

你能试试吗?

DELETE t1 FROM test t1, test t2
WHERE t1.email  = t2.email
    AND t1.fn > t2.fn
    AND t1.ln > t2.ln;

以下是测试:

CREATE TABLE test
(
    email varchar(100),
    fn varchar(100),
    ln varchar(100)
);
INSERT INTO test VALUES('a@b', 'f1', 'l1');
INSERT INTO test VALUES('a@b', 'f2', 'l2');
INSERT INTO test VALUES('c@d', 'f2', 'l2');
mysql> SELECT * FROM test;
+-------+------+------+
| email | fn   | ln   |
+-------+------+------+
| a@b   | f1   | l1   |
| a@b   | f2   | l2   |
| c@d   | f2   | l2   |
+-------+------+------+
3 rows in set (0.00 sec)
mysql>     DELETE t1 FROM test t1, test t2
    ->     WHERE t1.email  = t2.email
    ->         AND t1.fn > t2.fn
    ->         AND t1.ln > t2.ln;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM test;
+-------+------+------+
| email | fn   | ln   |
+-------+------+------+
| a@b   | f1   | l1   |
| c@d   | f2   | l2   |
+-------+------+------+
2 rows in set (0.00 sec)

ALTER TABLE table_name RENAME TO table_name_bak;
CREATE TABLE table_name AS SELECT * FROM table_name_bak LIMIT 0;
ALTER TABLE table_name ADD PRIMARY KEY (email);
INSERT IGNORE INTO table_name SELECT * FROM table_name_bak;