mysql - 从 Mysql 表中删除重复项

Question

我有一个类似于这个的 mysql 表，我想在其中删除重复项。

CREATE TABLE IF NOT EXISTS `map` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  `address` text NOT NULL,
  `type` text NOT NULL,
  `lat` text NOT NULL,
  `lng` text NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `map` (`id`, `name`, `address`, `type`, `lat`, `lng`) VALUES
(607, 'Vostok Station', 'Antarctica', 'establishment', '-82.8627519', '-135'),
(608, 'Vostok Station', 'Antarctica', 'establishment', '-82.8627519', '-135');

我已经尝试过类似的方法:

SELECT COUNT(address) AS numUsers;
delete from map 
where id in 
(SELECT MAX(id) FROM TABLE WHERE address IN (SELECT address FROM TABLE 
GROUP BY address 
HAVING COUNT(*)>1));

如果我犯了任何错误，请不要对我太苛刻。我只是一个几乎没有经验的新手:)

Answer 1

据我所知，一个简单的 DELETE JOIN 就可以做到；

DELETE m1
FROM map m1
JOIN map m2
  ON m1.address = m2.address
 AND m1.id > m2.id;

An SQLfiddle to test with .

这将删除所有存在具有较低 ID 和相同地址的行。

...永远记住，在运行来自 Internet 上随机用户的具有潜在破坏性的 SQL 之前，请始终进行备份。