mysql添加唯一键_MySQL“在重复键上插入...”具有多个唯一键

I've been reading up on how to use MySQL insert on duplicate key to see if it will allow me to avoid Selecting a row, checking if it exists, and then either inserting or updating. As I've read the documentation however, there is one area that confuses me. This is what the documentation says:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed

The thing is, I don't want to know if this will work for my problem, because the 'condition' I have for not inserting a new one is the existence of a row that has two columns equal to a certain value, not necessarily that the primary key is the same. Right now the syntax I'm imagining is this, but I don't know if it will always insert instead of replace:

INSERT INTO attendance (event_id, user_id, status) VALUES(some_event_number, some_user_id, some_status) ON DUPLICATE KEY UPDATE status=1

The thing is, event_id and user_id aren't primary keys, but if a row in the table 'attendance' already has those columns with those values, I just want to update it. Otherwise I would like to insert it. Is this even possible with ON DUPLICATE? If not, what other method might I use?

解决方案

If I were you, I would make a primary key out of event_id and user_id. That will make this extremely easy with ON DUPLICATE.

create table attendance (

event_id int,

user_id int,

status varchar(100),

primary key(event_id, user_id)

);

Then with ease:

insert into attendance (event_id, user_id, status) values(some_event_number, some_user_id, some_status)

on duplicate key

update status = values(status);