MySQL“在重复键上插入..."具有多个唯一键 [英] MySQL "Insert ... On Duplicate Key" with more than one unique key

问题描述

我一直在阅读如何在重复键上使用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:

如果您指定ON DUPLICATE KEY UPDATE，并且插入一行会导致UNIQUE索引或PRIMARY KEY中的值重复，则会执行旧行的UPDATE

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

问题是，event_id和user_id不是主键，但是如果表出席"中的一行已经具有这些值的列，我只想对其进行更新.否则，我想插入它.使用ON DUPLICATE甚至可能吗?如果没有，我还可以使用其他什么方法?

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?

推荐答案

如果我是你，我将使用event_id和user_id作为主键.这将使ON DUPLICATE变得非常简单.

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.

SQLFiddle

create table attendance (
    event_id int,
    user_id int,
    status varchar(100),
    primary key(event_id, user_id)
);

然后轻松点:

insert into attendance (event_id, user_id, status) values(some_event_number, some_user_id, some_status)
on duplicate key
update status = values(status);

这篇关于MySQL“在重复键上插入..."具有多个唯一键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！