MySQL insert on duplicate key; delete?

问题:

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.

回答1:

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 then mark it for deletion then you can delete it.

回答2:

Use REPLACE INTO:

replace into some_table
select somecolumn from othertable

will either insert new data or if thr same data exist will delete the data and insert the new one

回答3:

The nearest possible solution for the same is REPLACE statement. Here is the documentation for REPLACE.

A similar question was asked on MySQL Forums and the recommended(and only) answer was to use REPLACE.

回答4:

Yes of course there is a solutions in MySQL for your problem.

If you want to delete or skip the new inserting record if there already a duplicate record exists in the key column of the table, you can use IGNORE like this:

insert ignore into mytbl(id,name) values(6,'ron'),(7,'son');

Here id column is primary key in the table mytbl. This will insert multiple values in the table by deleting or skipping the new duplicate records.

Hope this will fulfill your requirement.