MySQL插入重复键;删除？ [英] MySQL insert on duplicate key; delete?

问题描述

有没有办法在MySQL中删除重复键上的记录？

说我们在数据库中有一个特定主键的记录，我们尝试添加另一个一个具有相同的键 - ON DUPLICATE KEY UPDATE 只会更新记录，但是如果已经存在，是否有删除记录的选项？这是一个点击按钮的简单的输入/输出功能。

解决方案

这是一个解决方案，但它的工作原理： / p>

创建一个新列，并将其调用为 do_delete ，或者使其成为一个tiny-int。然后执行重复密钥更新do_delete = 1;

根据您的MySQL版本/连接，您可以执行多个查询在同一个语句中。但是，如果没有，只需立即运行一个单独的查询。无论哪种方式，下一个查询只是：从[table]中删除do_delete = 1; 。这样，如果它的新条目，它不会删除任何东西。如果它不是一个新条目，它将会将其标记为删除，那么您可以删除它。

Is there a way of removing record on duplicate key in MySQL?

Say we have a record in the database with the specific primary key and we try to add another one with the same key - ON DUPLICATE KEY UPDATE would simply update the record, but is there an option to remove record if already exists? It is for simple in/out functionality on click of a button.

解决方案

It's a work-around, but it works:

Create a new column and call it do_delete, or whatever, making it a tiny-int. Then do On Duplicate Key Update do_delete = 1;

Depending on your MySQL version/connection, you can execute multiple queries in the same statement. However, if not, just run a separate query immediately afterwords. Either way, the next query would just be: Delete From [table] Where do_delete = 1;. This way, if its a new entry, it will not delete anything. If it was not a new entry, it will tell mark it for deletion then you can delete it.

这篇关于MySQL插入重复键;删除？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！