MySQL INSERT ... ON DUPLICATE KEY UPDATE with WHERE？

INSERT ... ON DUPLICATE KEY UPDATE with WHERE?

提问by Andreas Wederbrand

I'm doing a INSERT ... ON DUPLICATE KEY UPDATEbut I need the update part to be conditional, only doing the update if some extra condition has changed.

我正在做一个INSERT ... ON DUPLICATE KEY UPDATE但我需要更新部分是有条件的，只有在一些额外的条件发生变化时才进行更新。

However, WHEREis not allowed on this UPDATE. Is there any workaround for this?

但是，WHERE在这方面是不允许的UPDATE。有什么解决方法吗？

I can't do combinations of INSERT/UPDATE/SELECT since this needs to work over a replication.

我不能做 INSERT/UPDATE/SELECT 的组合，因为这需要在复制上工作。

回答by LouisXIV

I suggest you to use IF() to do that.

我建议你使用 IF() 来做到这一点。

Refer: conditional-duplicate-key-updates-with-mysql

参考：conditional-duplicate-key-updates-with-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);

回答by Andreas Wederbrand

This is our final solution, works like a charm!

这是我们的最终解决方案，就像一个魅力！

The insert ignorewill make sure that the row exists on both the master and slave, in case they've ever diverted.

该插入忽略将确保在主机和从机都存在该行，万一他们曾经改行。

The update ... wheremakes sure that only the most recent update, globally, is the end result after all replication is done.

该更新...其中，确保只有最新的更新，全球范围内，为最终结果的所有复制完成后。

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();

回答by lexu

you could use two insert statements .. since you CAN add a where clause to the select part for the source data.

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

select two sets of data, one that you will insert with 'on duplicate' and the other will be inserted without 'on duplicate'.

选择两组数据，您将使用“重复”插入一组数据，另一组将插入而没有“重复”。

回答by Tito100

table php_lock:
name:idString, locked:bool, time:timestamp, locked_by:string
values to insert or update
1, CURRENT_TIMESTAMP, 'script'
where name='wwww' AND locked=2

table php_lock:
name:idString, locked:bool, time:timestamp, locked_by:string
values to insert or update
1, CURRENT_TIMESTAMP, 'script'
where 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`);

回答by Ranjit Singh

On duplicate keydo not allow us to use where clause, so there are two alternative to achieve the same.

On duplicate key不允许我们使用 where 子句，因此有两种替代方法可以实现相同的效果。

1. If you know most of the time you will get the duplicate key then

   a. Update the table first using update query and where clause b. If update fails then insert the record into table using insert query

2. If you know most of the time you are going to insert into table then

   a. Insert the record into table using insert ignore query - what it does is actually ignore the insert if duplicate key found b. If insert ignore fails then update the record using update query

1. 如果你知道大部分时间你会得到重复的密钥

   a. Update the table first using update query and where clause b. If update fails then insert the record into table using insert query

2. 如果你知道大部分时间你要插入到表中，那么

   a. Insert the record into table using insert ignore query - what it does is actually ignore the insert if duplicate key found b. If insert ignore fails then update the record using update query

For reference of insert ignoreclick here

参考请点击这里insert ignore

回答by dreftymac

Overview

概述

• AWUpsertCondy wants to change BEFOREinto AFTER

• AWUpsertCondy 想把BEFORE改成AFTER

Problem

问题

• AWUpsertCondy does not want the insert query to fail if MySQL detects duplicate primary key
• MySQL does not support conditional WHERE clause with ON DUPLICATE KEY UPDATE

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

Solution

解决方案

• MySQL supports conditional clause with the IF()function
• Here we have a simple conditional to update only those items with userid less-than 9

• MySQL 支持带有IF()函数的条件子句
• 这里我们有一个简单的条件来更新那些用户 ID 小于 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 )
;

Pitfalls

陷阱

• We introduce a MySQL variable @doupdatein order to flag whether or not the UPDATE row meets the condition. Then we use that same variable for all the database columns we use in the UPDATE statement
• In the first conditional we both declare the variable and determine whether the conditional applies. This approach is arguably more cumbersome than a WHERE clause

• 我们引入了一个 MySQL 变量@doupdate来标记 UPDATE 行是否满足条件。然后我们对我们在 UPDATE 语句中使用的所有数据库列使用相同的变量
• 在第一个条件中，我们都声明了变量并确定条件是否适用。这种方法可以说比 WHERE 子句更麻烦

See also

也可以看看

• MySQL copy column withtout conditional

• MySQL 无条件复制列