如果存在则更新，否则插入到MySQL表中

Question

我想要向数据库表中添加一行数据，但如果已经存在具有相同唯一键的行，则我希望更新该行。

例如：

INSERT INTO table_name (ID, NAME, AGE) VALUES(1, "A", 19);

假设唯一键值是ID，在我的数据库中有一行ID=1。那么，在这种情况下，我希望使用这些值来更新该行数据。通常这将引发一个错误。
如果我使用INSERT IGNORE，它会忽略错误，但仍然不会更新数据。

- Keshan

---

SQL需要一个官方语法来解决这种情况，该语法不会强制在语法中重复值并且可以保留主键。 - Pete Alvin

要获取受影响的ID，请参考MySQL ON DUPLICATE KEY - last insert id?。 - LF00

注意：从版本5.7开始，此方法不直接支持WHERE子句作为INSERT / UPDATE操作的一部分。此外，更新实际上计为两个单独的操作（删除和插入）...如果这对审计目的有影响的话。（Learnbit） - dreftymac

Answer 1

使用 INSERT ... ON DUPLICATE KEY UPDATE

查询：

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE    
name="A", age=19

- Donnie

---

根据我的发现，相比REPLACE INTO方法，这种方法在自动增量键和其他唯一键冲突方面问题较少，并且更加高效。+1 - Andrew Ensley

我知道你们所有人都在暗示这一点，但我想为其他人明确一下。如果您插入的ID不是主键或唯一的，那么这将无法运行。因为我的ID不是唯一的，所以这最初对我没有起作用。 - neuquen

有点晚了，但是无论如何：在手册中指出，在ON DUPLICATE KEY UPDATE中的更新会使受影响的行数增加2。如果实际上没有更新任何内容（与常规的UPDATE相同），则报告0。 - Vatev

请注意，您可以使用VALUES（name）引用您尝试插入的值，例如INSERT INTO table（id，name，age）VALUES（1，“A”，19）ON DUPLICATE KEY UPDATE name=VALUES（name），age=VALUES（age）。 - Curious Sam

不幸的是，ON DUPLICATE KEY UPDATE 在更新时会导致自增量递增，这使得如果您每天需要执行数百或数千个此类查询，则几乎无法使用，因为即使没有添加新行，自增量也会每次稳定地增加 x 行。 - twhitney

Answer 2

参考 REPLACE：

REPLACE 像 INSERT 一样工作，但是如果表中的旧行与新行在 PRIMARY KEY 或 UNIQUE 索引上有相同的值，则会先删除旧行，再插入新行。

例如：

REPLACE INTO `tablename` (`id`, `name`, `age`) VALUES (1, "A", 19)

- Martin Schapendonk

---

因为这个更短，更易懂，而且没有人解释为什么“插入时重复”更好。 - Mr_Chimp

它会改变记录的ID，从而可能破坏外部引用。 - boh

REPLACE INTO 的另一个问题是必须为所有字段指定值，否则字段将会丢失或替换为默认值。如果行存在，REPLACE INTO 本质上会删除该行，然后插入新行。例如，如果执行 'REPLACE INTO table (id, age) values (1, 19)'，那么 name 字段将变为 null。 - Dale

这实际上是要删除整行并执行新的插入操作。 - mjb

这里所有的评论都是真实的，但有时这可能正是所需要的。 - IceFire

@IceFire “但有时候”不是将某些东西作为一般方法的好理由。 - DennisK

Answer 3

在使用批量插入时，请使用以下语法：

INSERT INTO TABLE (id, name, age) VALUES (1, "A", 19), (2, "B", 17), (3, "C", 22)
ON DUPLICATE KEY UPDATE
    name = VALUES (name),
    ...

- Fabiano Souza

---

如果VALUES(name)无法工作，你可以尝试 name = 'name_value',...; - Son Pham

VALUES现已被弃用-您应该使用insert into TABLE (Id, name, age) values (1, "A", 19), (2, "B", 20) as ins on duplicate key update name=ins.name, age=ins.age; 建议使用新的语法，将数据插入表中，并在遇到重复键时更新相应列的值。请注意，不要使用VALUES关键字。 - Liam

所有名称将被更新为相同的值。 - Umair Ayub

Answer 4

无论哪种解决方案都可以解决您的问题：

INSERT IGNORE INTO table (id, name, age) VALUES (1, "A", 19);

或者

INSERT INTO TABLE (id, name, age) VALUES(1, "A", 19) 
    ON DUPLICATE KEY UPDATE NAME = "A", AGE = 19;  

或者

REPLACE INTO table (id, name, age) VALUES(1, "A", 19);

- Dilraj Singh

---

我之所以给这个评分是因为它没有解释这些解决方案之间的区别和每个解决方案可能存在的缺点。 - bfontaine

我不相信第一个版本会产生与其他版本相同的结果。据我所知，“IGNORE”只会使数据库将冲突视为警告而不是错误。它不会更新冲突行。此外，正如其他人指出的那样，“REPLACE INTO”实际上是一个DELETE/INSERT，而“ON DUPLICATE KEY”将更新现有行。 - Greg Brown

Answer 5

试试这个：

INSERT INTO table (id,name,age) VALUES('1','Mohammad','21') ON DUPLICATE KEY UPDATE name='Mohammad',age='21'

注意：
如果id是主键，在第一次插入时用id='1'，每次尝试插入id='1'时将会更新姓名和年龄，并更改之前的姓名和年龄。

- Rasel

---

我想在不使用id的情况下工作。你尝试过没有主键吗？ - ersks

@ersks请看问题。用户询问了何时存在唯一键。 - Rasel

我明白了，但我正在尝试解决我的问题，其中只有这些值是已知的。因此，在这种情况下，我写了上面的评论，希望能得到正确的解决方案。 - ersks

Answer 6

试一试这个：

INSERT INTO table (id, name, age) VALUES (1, 'A', 19) ON DUPLICATE KEY UPDATE id = id + 1;

希望这可以帮到你。

- Luis Reyes

---

实际上，我不需要将新值添加到另一行并使用新的ID，而是希望用这些值替换ID为1的现有值。（据我理解，这会增加ID并添加数据） - Keshan

我认为他不想在重复时将id增加一。 - Donnie

"transgress" 不是你要找的词 :) 不幸的是，现在我已经看到了 "transgress" 这个词，我不再能够想象出实际的单词了。 - mwfearnley

更新一个ID可能会打乱其序列，或者如果存在id+1的情况下会出现错误。 - El Gucs

Answer 7

如果您想将非主要字段作为触发ON DUPLICATE的条件，您可以在该表上创建一个UNIQUE INDEX键来触发DUPLICATE。

ALTER TABLE `table` ADD UNIQUE `unique_index`(`name`);

如果您想将两个字段组合成唯一的表格，可以在最后一个参数中添加更多内容来实现此目的。

ALTER TABLE `table` ADD UNIQUE `unique_index`(`name`, `age`);

请注意，在删除其他行中相同的 name 和 age 值之前，确保先删除所有具有相同值的数据。

DELETE table FROM table AS a, table AS b WHERE a.id < b.id 
AND a.name <=> b.name AND a.age <=> b.age;

此后，它应该触发ON DUPLICATE事件。

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE    
name = VALUES(name), age = VALUES(age)

- Rich

---

我已经尝试过了，但是出现了一个错误，提示“在关键字规范中使用了未指定长度的'BLOB/TEXT列'column_name”。 - Natalia

@Natalia 请创建一个 dbfiddle，这样我就可以检查了。 - Rich

我会尝试，但基本上，在MySQL中你不能使列的文本类型唯一。我将其设置为varchar。 - Natalia

"DELETE table FROM table AS a, table AS b WHERE a.id < b.id AND a.name <=> b.name AND a.age <=> b.age;" 给我报错 "Unknown table 'table' in MULTI DELETE"。 - Nosajimiki

最简单的答案，我很感激。 - Humphrey

Answer 8

我正在寻找一个解决方案，用于从另一个结构相同的表格（在我的情况下是网站测试数据库到生产数据库）进行更新：

INSERT  live-db.table1
SELECT  *
FROM    test-db.table1 t
ON DUPLICATE KEY UPDATE
        ColToUpdate1 = t.ColToUpdate1,
        ColToUpdate2 = t.ColToUpdate2,
        ...

如前所述，在ON DUPLICATE KEY UPDATE之后只需要包含您想要更新的列。

不需要在INSERT或SELECT中列出这些列，尽管我认为这可能是更好的做法。

- SteveCinq

Answer 9

使用SQLite时:

REPLACE into table (id, name, age) values(1, "A", 19)

前提是id是主键，否则它只会插入另一行。请参见 INSERT (SQLite)。

- DawnSong

---

replace into 的作用是“插入或更新已存在的数据”。@Owl - DawnSong

+1（1/3）我发现这对我的情况有用-我需要用唯一键替换现有行，如果没有，则添加该行。这里是最简单的解决方案。 - therobyouknow

我的查询：CREATE TRIGGER worklog_update AFTER INSERT ON worklog FOR EACH ROW REPLACE INTO support_time_monthly_hours ( ProjectID, monthTotalTime, Year, Month ) SELECT jiraissue.PROJECT, SUM(worklog.timeworked), YEAR(CURRENT_DATE()), MONTH(CURRENT_DATE()) FROM worklog, jiraissue WHERE worklog.issueid = jiraissue.ID AND jiraissue.PROJECT = (SELECT PROJECT FROM jiraissue WHERE NEW.issueid = jiraissue.ID ) AND worklog.startdate BETWEEN DATE_FORMAT(NOW() ,'%Y-%m-01 00:00:00') AND NOW(); - therobyouknow

相关问题/答案：http://stackoverflow.com/questions/41767517/insert-row-into-mysql-table-result-from-query-into-one-field-and-sql-function-re - therobyouknow

在mysql中，这个问题是replace会删除其他未提供的值。然而，@fabiano-souza的解决方案更为合适。 - Quamber Ali

Answer 10

如果你想保留旧的字段（例如：姓名）。查询将会是：

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE    
name=name, age=19;

- Xman Classical