带有WHERE的INSERT ... ON DUPLICATE KEY UPDATE是什么意思？

Question

我正在进行一个INSERT ... ON DUPLICATE KEY UPDATE，但我需要更新部分有条件，只有在某些额外条件改变时才执行更新。

然而，对于这个UPDATE，不允许使用WHERE语句。是否有任何解决办法？

我无法进行INSERT/UPDATE/SELECT的组合操作，因为这需要在复制过程中工作。

- Andreas Wederbrand

Answer 1

我建议您使用IF()来完成此操作。

参考：MySQL中的条件重复键更新

INSERT INTO daily_events (created_on, last_event_id, last_event_created_at)
  VALUES ('2010-01-19', 23, '2010-01-19 10:23:11')
ON DUPLICATE KEY UPDATE
  last_event_id = IF(last_event_created_at < VALUES(last_event_created_at), VALUES(last_event_id), last_event_id);

- LouisXIV

---

我从未知道我可以在 UPDATE 部分中访问原始表值。这可能解决了我的问题（尽管我的旧解决方法非常好 :))。 - Andreas Wederbrand

有时候MySQL会抱怨“field list中的列不明确”，那么您需要使用以下代码：
ON DUPLICATE KEY UPDATE daily_events.last_event_id = IF(daily_events.last_event_created_at < VALUES(last_event_created_at), VALUES(last_event_id), daily_events.last_event_id); - boryn

很遗憾，发布在thewebfellas.com上的链接已经失效了。 - Taylor R

发布到 thewebfellas.com 的链接现在是可行的。 - Razvan_TK9692

Answer 2

这是我们的最终解决方案，非常有效！

插入忽略将确保该行存在于主库和从库中，以防它们曾经分离。

更新...where确保在所有复制完成后，全局只有最新的更新是最终结果。

mysql> desc test;
+-------+--------------+------+-----+-------------------+-------+
| Field | Type         | Null | Key | Default           | Extra |
+-------+--------------+------+-----+-------------------+-------+
| id    | int(11)      | NO   | PRI | NULL              |       | 
| value | varchar(255) | YES  |     | NULL              |       | 
| ts    | timestamp    | NO   |     | CURRENT_TIMESTAMP |       | 
+-------+--------------+------+-----+-------------------+-------+

mysql> insert ignore into test values (4, "foo", now());    
mysql> update test set value = "foo", ts = now() where id = 4 and ts <= now();

- Andreas Wederbrand

---

解释在@lexu的答案中：两个语句，第一个是“insert ignore”，第二个是类似于“update ignore”的语句。 - xmedeko

看起来第二个语句只是以非常复杂的方式执行 update test set value = "foo", ts = now() where id = 4 and ts < now()。 - antak

是的，你说得对。我在发布这个答案后已经更改了代码，变成了insert ignore...，然后是update .... where ts < now()。 - Andreas Wederbrand

@AndreasWederbrand，您能否相应地更新您的答案，以便其他人也可以从您的结论中受益？ - Eliran Malka

Answer 3

您可以使用两个插入语句...因为您可以在源数据的选择部分添加where子句。

选择两组数据，一组将使用“on duplicate”插入，另一组将不使用“on duplicate”插入。

- lexu

Answer 4

概述

• AWUpsertCondy想把BEFORE改成AFTER

问题

• 如果MySQL检测到重复的主键，AWUpsertCondy不想插入查询失败
• MySQL不支持带有ON DUPLICATE KEY UPDATE的条件WHERE子句

解决方案

• MySQL支持使用IF()函数进行条件子句
• 这里我们有一个简单的条件，只更新userid小于9的项目

INSERT INTO zzdemo_table02
    (lname,userid)
  SELECT
    lname,userid
    FROM(
      SELECT
        lname,userid
      FROM
        zzdemo_table01
    ) as tt01
ON DUPLICATE KEY UPDATE
    userid=IF(@doupdate:=IF( (tt01.userid < 9) , True, False), 
        tt01.userid, zzdemo_table02.userid)
    ,lname=IF(@doupdate, tt01.lname , zzdemo_table02.lname )
;

陷阱

• 我们引入了一个MySQL变量@doupdate，以标记更新行是否符合条件。然后我们使用相同的变量来处理UPDATE语句中使用的所有数据库列。
• 在第一个条件语句中，我们声明变量并确定条件是否适用。这种方法可能比使用WHERE子句更繁琐。

另请参阅

• MySQL无条件复制列

注意：保留html标签。

- dreftymac

Answer 5

表格php_lock:
name:idString, locked:bool, time:timestamp, locked_by:string
要插入或更新的值
1，CURRENT_TIMESTAMP，'script'
其中name='wwww' AND locked=2

INSERT INTO `php_lock` (`name`, locked, `time`, `locked_by`)  
(SELECT * FROM 
    (SELECT `name`,1,CURRENT_TIMESTAMP, 'script' FROM `php_lock` 
        WHERE `name`='wwww' AND `locked`=2  
    UNION (
    SELECT 'wwww',1 ,CURRENT_TIMESTAMP, 'script') 
) AS temp LIMIT 1) 
ON DUPLICATE KEY UPDATE locked=VALUES(locked), `time`=VALUES(`time`), `locked_by`=VALUES(`locked_by`);

- Tito100

Answer 6

在重复键的情况下，不允许我们使用where子句，因此有两种替代方法可以实现相同的效果。

1. 如果您知道大多数时间都会得到重复键，则

   a. 使用update查询和where子句首先更新表 b. 如果更新失败，则使用insert查询将记录插入表中

2. 如果您知道大多数时间都要插入到表中，则

   a. 使用insert ignore查询将记录插入表中 - 它实际上会忽略插入，如果发现重复键 b. 如果插入忽略失败，则使用update查询更新记录

关于insert ignore的参考 点击这里

- Ranjit Singh

---

insert ignore 怎么会失败？ - Andreas Wederbrand

如果您尝试插入相同的唯一键，“insert ignore”将会失败。 - Ranjit Singh

不，这就是 ignore 的作用。它会忽略重复的键异常。 - Andreas Wederbrand