MySQL 在非 PRIMARY 键的重复更新时插入

MySQL insert on duplicate update for non-PRIMARY key

提问by moonvader

I am little confused with insert on duplicate update query. I have MySQL table with structure like this:

我对插入重复更新查询有点困惑。我有这样结构的 MySQL 表：

• record_id (PRIMARY, UNIQUE)
• person_id (UNIQUE)
• some_text
• some_other_text

• record_id (PRIMARY, UNIQUE)
• person_id（唯一）
• some_text
• some_other_text

I want to update some_text and some_other_text values for person if it's id exists in my table.person or insert new record in this table otherwise. How it can be done if person_id is not PRIMARY?

我想更新 some_text 和 some_other_text 人的值，如果它的 id 存在于我的 table.person 中，否则在此表中插入新记录。如果 person_id 不是 PRIMARY 怎么办？

回答by Lorenzo Vincenzi

You need a query that check if exists any row with you record_id (or person_id). If exists update it, else insert new row

您需要一个查询来检查是否存在包含您的 record_id（或 person_id）的任何行。如果存在更新它，否则插入新行

IF EXISTS (SELECT * FROM table.person WHERE record_id='SomeValue')
    UPDATE table.person 
    SET some_text='new_some_text', some_other_text='some_other_text' 
    WHERE record_id='old_record_id'
ELSE
    INSERT INTO table.person (record_id, person_id, some_text, some_other_text) 
    VALUES ('new_record_id', 'new_person_id', 'new_some_text', 'new_some_other_text')

Another better approach is

另一种更好的方法是

UPDATE table.person SET (...) WHERE person_id='SomeValue'
IF ROW_COUNT()=0
    INSERT INTO table.person (...) VALUES (...)

回答by Timo

Your question is very valid. This is a very common requirement. And most people get it wrong, due to what MySQL offers.

你的问题很有道理。这是一个非常普遍的要求。由于 MySQL 提供的功能，大多数人都弄错了。

• The requirement: Insert unless the PRIMARYkey exists, otherwise update.
• The common approach: ON DUPLICATE KEY UPDATE
• The result of that approach, disturbingly: Insert unless the PRIMARYor any UNIQUEkeyexists, otherwise update!

• 要求：除非PRIMARY键存在，否则插入，否则更新。
• 常见的做法： ON DUPLICATE KEY UPDATE
• 这种方法的结果令人不安的是：除非存在PRIMARY或UNIQUE存在任何键，否则插入，否则更新！

What can go horribly wrong with ON DUPLICATE KEY UPDATE? You insert a supposedly new record, with a new PRIMARYkey value (say a UUID), but you happen to have a duplicate value for its UNIQUEkey.

有什么可怕的错误ON DUPLICATE KEY UPDATE？您插入了一个假定的新记录，带有一个新的PRIMARY键值（比如 UUID），但您碰巧有一个重复的UNIQUE键值。

What you want is a proper exception, indicating that you are trying to insert a duplicate into a UNIQUEcolumn.

您想要的是一个适当的异常，表明您正在尝试将重复项插入到UNIQUE列中。

But what you get is an unwanted UPDATE! MySQL will take the conflicting record and start overwriting its values. If this happens unintentionally, you have mutilated an old record, and any incoming references to the old record are now referencing the new record. And since you probably won't tell the query to update the PRIMARYcolumn, your new UUID is nowhere to be found. If you ever encounter this data, it will probably make no sense and you will have no idea where it came from.

但是你得到的是一个不需要的UPDATE！MySQL 将获取冲突记录并开始覆盖其值。如果无意中发生这种情况，您已经破坏了旧记录，并且任何对旧记录的传入引用现在都在引用新记录。而且由于您可能不会告诉查询更新PRIMARY列，因此您的新 UUID 无处可寻。如果您遇到这些数据，它可能毫无意义，您将不知道它来自哪里。

We need a solution to actuallyinsert unless the PRIMARYkey exists, otherwise update.

我们需要一个解决方案来实际插入，除非PRIMARY键存在，否则 update。

We will use a query that consists of two statements:

我们将使用由两个语句组成的查询：

1. Update where the PRIMARYkey value matches (affects 0 or 1 rows).
2. Insert if the PRIMARYkey value does not exist (inserts 1 or 0 rows).

1. 更新PRIMARY键值匹配的位置（影响 0 或 1 行）。
2. 如果PRIMARY键值不存在，则插入（插入 1 或 0 行）。

This is the query:

这是查询：

UPDATE my_table SET
unique_name = 'one', update_datetime = NOW()
WHERE id = 1;

INSERT INTO my_table
SELECT 1, 'one', NOW()
FROM my_table
WHERE id = 1
HAVING COUNT(*) = 0;

Only one of these queries will have an effect. The UPDATEis easy. As for the INSERT: WHERE id = 1results in a row if the id exists, or no row if it does not. HAVING COUNT(*) = 0inverts that, resulting in a row if the id is new, or no row if it already exists.

只有这些查询之一会产生影响。这UPDATE很容易。至于INSERT:WHERE id = 1如果 id 存在，则结果为一行，如果不存在，则为无行。HAVING COUNT(*) = 0反转它，如果 id 是新的，则产生一行，或者如果它已经存在，则没有行。

I have explored other variants of the same idea, such as with a LEFT JOINand WHERE, but they all looked more convoluted. Improvements are welcome.

我已经探索了相同想法的其他变体，例如使用 aLEFT JOIN和WHERE，但它们看起来都更加复杂。欢迎改进。

回答by wchiquito

13.2.5.3 INSERT ... ON DUPLICATE KEY UPDATE Syntax

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, MySQL performs an UPDATE of the old row.

13.2.5.3 INSERT ... ON DUPLICATE KEY UPDATE 语法

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

Example:

例子：

DELIMITER //

DROP PROCEDURE IF EXISTS `sp_upsert`//
DROP TABLE IF EXISTS `table_test`//

CREATE TABLE `table_test` (
  `record_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `person_id` INT UNSIGNED NOT NULL,
  `some_text` VARCHAR(50),
  `some_other_text` VARCHAR(50),
  UNIQUE KEY `record_id_index` (`record_id`),
  UNIQUE KEY `person_id_index` (`person_id`)
)//

INSERT INTO `table_test`
  (`person_id`, `some_text`, `some_other_text`)
VALUES
  (1, 'AAA', 'XXX'),
  (2, 'BBB', 'YYY'),
  (3, 'CCC', 'ZZZ')//

CREATE PROCEDURE `sp_upsert`(
  `p_person_id` INT UNSIGNED,
  `p_some_text` VARCHAR(50),
  `p_some_other_text` VARCHAR(50)
)
BEGIN
  INSERT INTO `table_test`
    (`person_id`, `some_text`, `some_other_text`)
  VALUES
    (`p_person_id`, `p_some_text`, `p_some_other_text`)
  ON DUPLICATE KEY UPDATE `some_text` = `p_some_text`,
                          `some_other_text` = `p_some_other_text`;
END//

DELIMITER ;

mysql> CALL `sp_upsert`(1, 'update_text_0', 'update_text_1');
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT
    ->   `record_id`,
    ->   `person_id`,
    ->   `some_text`,
    ->   `some_other_text`
    -> FROM
    ->   `table_test`;
+-----------+-----------+---------------+-----------------+
| record_id | person_id | some_text     | some_other_text |
+-----------+-----------+---------------+-----------------+
|         1 |         1 | update_text_0 | update_text_1   |
|         2 |         2 | BBB           | YYY             |
|         3 |         3 | CCC           | ZZZ             |
+-----------+-----------+---------------+-----------------+
3 rows in set (0.00 sec)

mysql> CALL `sp_upsert`(4, 'new_text_0', 'new_text_1');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT
    ->   `record_id`,
    ->   `person_id`,
    ->   `some_text`,
    ->   `some_other_text`
    -> FROM
    ->   `table_test`;
+-----------+-----------+---------------+-----------------+
| record_id | person_id | some_text     | some_other_text |
+-----------+-----------+---------------+-----------------+
|         1 |         1 | update_text_0 | update_text_1   |
|         2 |         2 | BBB           | YYY             |
|         3 |         3 | CCC           | ZZZ             |
|         5 |         4 | new_text_0    | new_text_1      |
+-----------+-----------+---------------+-----------------+
4 rows in set (0.00 sec)

SQL Fiddle demo

SQL Fiddle demo

回答by Chrisman

How about my approach?

我的方法呢？

Let's say you have one table with a autoincrement id and three text-columns. You want to insert/update the value of column3 with the values in column1 and column2 being a (non unique) key.

假设您有一个带有自动增量 ID 和三个文本列的表。您想插入/更新 column3 的值，其中 column1 和 column2 中的值是（非唯一）键。

I use this query (without explicitly locking the table):

我使用这个查询（没有明确锁定表）：

insert into myTable (id, col1, col2, col3)
select tmp.id, 'col1data', 'col2data', 'col3data' from
(select id from myTable where col1 = 'col1data' and col2 = 'col2data' union select null as id limit 1) tmp
on duplicate key update col3 = values(col3)

Anything wrong with that? For me it works the way I want.

有什么问题吗？对我来说，它以我想要的方式工作。

回答by antak

A flexible solution should retain the atomicity offered by INSERT ... ON DUPLICATE KEY UPDATEand work regardless of if it's autocommit=trueand not depend on a transaction with an isolation level of REPEATABLE READor greater.

一个灵活的解决方案应该保留由提供的原子性INSERT ... ON DUPLICATE KEY UPDATE和工作，不管它是否autocommit=true依赖于隔离级别REPEATABLE READ或更高的事务。

Any solution performing check-then-act across multiple statements would not satisfy this.

任何跨多个语句执行检查然后操作的解决方案都不能满足这一点。

Here are the options:

以下是选项：

If there tends to be more inserts than updates:

如果插入比更新多：

INSERT INTO table (record_id, ..., some_text, some_other_text) VALUES (...);

IF <duplicate entry for primary key error>
  UPDATE table SET some_text = ..., some_other_text = ... WHERE record_id = ...;

  IF affected-rows = 0
    -- retry from INSERT OR ignore this conflict and defer to the other session

If there tends to be more updates than inserts:

如果更新往往多于插入：

UPDATE table SET some_text = ..., some_other_text = ... WHERE record_id = ...;

IF affected-rows = 0
  INSERT INTO table (record_id, ..., some_text, some_other_text) VALUES (...);

  IF <duplicate entry for primary key error>
    -- retry from UPDATE OR ignore this conflict and defer to the other session

If you don't mind a bit of ugliness, you can actually use INSERT ... ON DUPLICATE KEY UPDATEand do this in a single statement:

如果您不介意有点丑陋，您实际上可以INSERT ... ON DUPLICATE KEY UPDATE在单个语句中使用和执行此操作：

INSERT INTO table (record_id, ..., some_text, some_other_text) VALUES (...)
    ON DUPLICATE KEY UPDATE
      some_text = if(record_id = VALUES(record_id), VALUES(some_text), some_text),
      some_other_text = if(record_id = VALUES(record_id), VALUES(some_other_text), some_other_text)

IF affected-rows = 0
  -- handle this as a unique check constraint violation

Note: affected-rowsin these examples mean affectedrows and not foundrows. The two can be confused because a single parameter switches which of these values the client is returned.

注意：affected-rows在这些示例中表示受影响的行和未找到的行。这两者可能会混淆，因为单个参数会切换客户端返回的这些值中的哪一个。

Also note, if some_textand some_other_textare not actually modified (and the record is not otherwise changed) when you perform the update, those checks on affected-rows = 0will misfire.

另请注意，如果执行更新时some_text和some_other_text没有实际修改（并且记录没有以其他方式更改），则这些检查affected-rows = 0将失败。