MySQL如果行存在则更新，否则插入

Question

我正在制作一个网站，让人们可以报名参加活动并选择相关的活动。他们可以选择参加其中一个或两个都参加。

为此，我创建了一个名为“eventCounter”的表格。 表格图像 userID、eventID和activityID都是指向其他表的外键。

他们应该能够更新当前的“状态”，这样他们就可以在报名参加活动后加入活动。

所以我的问题是：如何编写“如果存在行则更新，否则插入”的语句？

IF EXISTS(select userID, eventID, activityID from eventCounter where userID=1 and eventID=1)
THEN 
UPDATE eventcounter SET activityID=1 WHERE userID=1; 
ELSE 
INSERT INTO eventcounter (userID, activityID) VALUES(1,1)

我认为 ON DUPLICATE 键不起作用，因为我有2个需要检查的列？

- Frederik Kiel

---

你不能创建复合主键吗？ - Justinas

这能帮到我什么？它们都是指向其他表的外键（已更新的主题）。即使 eventID 可能是 4，activityID 也可能是 2。并不能保证一个事件一定有一个关联的活动。 - Frederik Kiel

你做错了什么。你想通过指定的2个字段来检查存在性，但是插入了不同的字段。 - Alexander R.

这只是一个例子，我知道它是错误的。 - Frederik Kiel

Answer 1

这就是所谓的Upsert。MySQL语法比较奇怪，但你可以通过以下方式实现：

INSERT INTO eventcounter (userID, eventID, activityID) VALUES(1,1,1)
ON DUPLICATE KEY UPDATE
  activityID = VALUES(activityID)

但是它只适用于重复的 PK（不适用于您喜欢的任何字段），因此在此情况下，仅当 userID 和 eventID 组成 PK 且您只想更改 activityID 时才有效。

否则，您可以选择 IF-ELSE 路线，例如：

DECLARE mycount INT;
SET mycount = (SELECT COUNT(*) FROM eventcounter WHERE userID=1 AND eventID=1);
IF mycount > 0 THEN
  UPDATE eventcounter SET activityID=1 WHERE userID=1 AND eventID=1;
ELSE
  INSERT INTO eventcounter (userID, eventID, activityID) VALUES(1,1,1);
END IF;

免责声明：这是未经测试的代码

- Jcl

---

但是eventCounter表旨在用于所有事件和活动，因此ID不能是主键或“唯一”的。问题是我有两列可以更改/插入，即eventID和activityID。 - Frederik Kiel

似乎在对您的编辑进行一些调整后，我即将找到一个解决方案——唯一的问题是IF语法imgur.com/uMfV75G，有什么想法吗？ - Frederik Kiel

我认为在MySQL中，除了存储过程或函数外，IF语句不会以那种方式工作。 - Jcl

Answer 2

到了2021年，情况发生了变化！因此，我正在更新旧的@Jcl的答案：

你必须记住的一件事是，这个语句只有在插入时发生重复项时才适用于PRIMARY KEY或UNIQUE INDEX（因此不仅适用于PK，而且也适用于index）！

---

SQL命令：

INSERT INTO fooTable (RowNum, name, lastname, userID, score) VALUES(3,'Michael','Widenius','1012141618',19.75)
ON DUPLICATE KEY UPDATE name = 'Allan', lastname = 'Larsson', userNum = '2022242628', score = 10.50;

---

注意：

• 使用VALUES()引用新行和列已被弃用 (参考)
• 命令在最新的MYSQL 8.0和PHP Mysqli上测试过。
• 以上命令仅演示了如何更新多行；在实际情况中，大多数情况下您不需要更新所有name、lastname和userID，如果记录已经存在，则score就足够了！

---

我觉得这个命令非常简单易懂，你可以在几乎每个重要的字段上检查重复（使用UNIQUE INDEX）！只需记住第一行...

- Shahaboddin

Answer 3

或者，如果有人想要一个单一的查询语句而不需要创建数组来设置值，可以尝试这个查询方法。这只是对@Shahaboddin答案的改进。

INSERT INTO eventcounter 
    (
    userID,
    activityID
    )
SELECT userID, activityID FROM eventCounter WHERE userID=1 and eventID=1
ON DUPLICATE KEY UPDATE 
    userID = VALUES(userID),
    activityID = VALUES(activityID);

注意：

• 已在MySQL 5.6上测试通过

- Ras