问题描述
由于我的 javascript 单击处理中的错误，多个 Location 对象被发布到发送到服务器的 JSON 数组中.我想我知道如何修复该错误，但我还想实现服务器端数据库重复擦除功能.但是，我不确定如何编写此查询.
Due to a bug in my javascript click handling, multiple Location objects are posted to a JSON array that is sent to the server. I think I know how to fix that bug, but I'd also like to implement a server side database duplicate erase function. However, I'm not sure how to write this query.
唯一受影响的表格布局为
The only affected table is laid out as
+----+------------+--------+
| ID | locationID | linkID |
+----+------------+--------+
| 64 | 13 | 14 |
| 65 | 14 | 13 |
| 66 | 14 | 15 |
| 67 | 15 | 14 |
| 68 | 15 | 16 |
| 69 | 16 | 17 |
| 70 | 16 | 14 |
| 71 | 17 | 16 |
| 72 | 17 | 16 |
| 73 | 17 | 16 |
| 74 | 17 | 16 |
| 75 | 17 | 16 |
| 76 | 17 | 16 |
| 77 | 17 | 16 |
+----+------------+--------+
如你所见，我有多对 (17, 16)，而 14 有两对 (14, 13)> 和 (14, 15).如何删除除一条记录之外的所有重复条目?
As you can see, I have multiple pairs of (17, 16), while 14 has two pairs of (14, 13) and (14, 15). How can I delete all but one record of any duplicate entries?
推荐答案
不要实现事后更正逻辑，在需要唯一的字段上放置唯一索引，这样数据库会在它太之前停止重复插入迟到了.
Don't implement post factum correction logic, put a unique index on the fields that need to be unique, that way the database will stop dupe inserts before it's too late.
如果您使用的是 MySQL 5.1 或更高版本，则可以在 1 个命令中删除重复项并创建唯一索引:
If you're using MySQL 5.1 or higher you can remove dupes and create a unique index in 1 command:
ALTER IGNORE TABLE 'YOURTABLE'
ADD UNIQUE INDEX somefancynamefortheindex (locationID, linkID)
这篇关于从 MySQL 中删除重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！