如何删除两个完全相同的行之一? [英] How can I delete one of two perfectly identical rows?

问题描述

我正在清理没有主键的数据库表(我知道，我知道，他们在想什么?).我无法添加主键，因为在列中有重复的键将成为主键.重复值来自在所有方面都相同的两行之一.我无法通过GUI删除行(在这种情况下为MySQL Workbench，但我正在寻找一种与数据库无关的方法)，因为它拒绝在没有主键(或至少是UQ NN列)的表上执行任务，并且我无法添加主键，因为在列中有重复的键将成为主键.重复值来自一个...

I am cleaning out a database table without a primary key (I know, I know, what were they thinking?). I cannot add a primary key, because there is a duplicate in the column that would become the key. The duplicate value comes from one of two rows that are in all respects identical. I can't delete the row via a GUI (in this case MySQL Workbench, but I'm looking for a database agnostic approach) because it refuses to perform tasks on tables without primary keys (or at least a UQ NN column), and I cannot add a primary key, because there is a duplicate in the column that would become the key. The duplicate value comes from one...

如何删除其中一对双胞胎?

How can I delete one of the twins?

推荐答案

解决问题的一种方法是使用相同的架构创建一个新表，然后执行以下操作:

One option to solve your problem is to create a new table with the same schema, and then do:

INSERT INTO new_table (SELECT DISTINCT * FROM old_table)

然后重新命名表.

您当然需要大约与表所需的磁盘空间相同的磁盘空间！

You will of course need approximately the same amount of space as your table requires spare on your disk to do this!

效率不高，但是非常简单.

It's not efficient, but it's incredibly simple.

这篇关于如何删除两个完全相同的行之一?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！