MySQL 如何从具有相同列值的mysql表中删除某一行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18378190/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to delete a certain row from mysql table with same column values?
提问by Dani
I have a problem with my queries in MySQL. My table has 4 columns and it looks something like this:
我在 MySQL 中的查询有问题。我的表有 4 列，它看起来像这样：
id_users id_product quantity date
1 2 1 2013
1 2 1 2013
2 2 1 2013
1 3 1 2013
id_usersand id_productare foreign keys from different tables.
id_users并且id_product是来自不同表的外键。
What I want is to delete just one row:
我想要的是只删除一行：
1 2 1 2013
Which appears twice, so I just want to delete it.
出现了两次，所以我只想删除它。
I've tried this query:
我试过这个查询：
delete from orders where id_users = 1 and id_product = 2
But it will delete both of them (since they are duplicated). Any hints on solving this problem?
但它会删除它们（因为它们是重复的）。有关解决此问题的任何提示？
回答by juergen d
回答by dnet
All tables should have a primary key (consisting of a single or multiple columns), duplicate rows doesn't make sense in a relational database. You can limit the number of delete rows using LIMITthough:
所有表都应该有一个主键（由单列或多列组成），在关系数据库中重复行没有意义。您可以使用以下方法限制删除行数LIMIT：
DELETE FROM orders WHERE id_users = 1 AND id_product = 2 LIMIT 1
But that just solves your current issue, you should definitely work on the bigger issue by defining primary keys.
但这只是解决了您当前的问题，您绝对应该通过定义主键来解决更大的问题。
回答by Rob
You need to specify the number of rows which should be deleted. In your case (and I assume that you only want to keep one) this can be done like this:
您需要指定应删除的行数。在您的情况下（我假设您只想保留一个），可以这样做：
DELETE FROM your_table WHERE id_users=1 AND id_product=2
LIMIT (SELECT COUNT(*)-1 FROM your_table WHERE id_users=1 AND id_product=2)
回答by praveenraj4ever
Best way to design table is add one temporary row as auto increment and keep as primary key. So we can avoid such above issues.
设计表的最佳方法是添加一个临时行作为自动增量并保留为主键。所以我们可以避免上述问题。
回答by Somnath Muluk
There are already answers for Deleting row by LIMIT. Ideally you should have primary key in your table. But if there is not.
已经有关于删除行的答案LIMIT。理想情况下，您的表中应该有主键。但如果没有。
I will give other ways:
我会给出其他方式：
- By creating Unique index
- 通过创建唯一索引
I see id_users and id_product should be unique in your example.
我看到 id_users 和 id_product 在您的示例中应该是唯一的。
ALTER IGNORE TABLE orders ADD UNIQUE INDEX unique_columns_index (id_users, id_product)
These will delete duplicate rows with same data.
这些将删除具有相同数据的重复行。
But if you still get an error, even if you use IGNORE clause, try this:
但是如果你仍然得到一个错误，即使你使用了 IGNORE 子句，试试这个：
ALTER TABLE orders ENGINE MyISAM;
ALTER IGNORE TABLE orders ADD UNIQUE INDEX unique_columns_index (id_users, id_product)
ALTER TABLE orders ENGINE InnoDB;
- By creating table again
- 通过再次创建表
If there are multiple rows who have duplicate values, then you can also recreate table
如果有多行具有重复值，那么您也可以重新创建表
RENAME TABLE `orders` TO `orders2`;
CREATE TABLE `orders`
SELECT * FROM `orders2` GROUP BY id_users, id_product;
回答by Andrei Tornea
You must add an id that auto-increment for each row, after that you can delet the row by its id. so your table will have an unique id for each row and the id_user, id_product ecc...
您必须为每一行添加一个自动递增的 id，之后您可以通过其 id 删除该行。所以你的表的每一行都有一个唯一的id，id_user, id_product ecc ...
