MySQL 如果已经存在，如何插入记录或更新？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1952922/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to INSERT a record or UPDATE if it already exists?
提问by Niko Gamulin
I have a table with columns record_id(auto inc), sender, sent_timeand status.
我有一个包含列record_id(auto inc) sender、sent_time和 的表status。
In case there isn't any record of a particular sender, for example "sender1", I have to INSERT a new record otherwise I have to UPDATE the existing record which belongs to "user1".
如果没有特定发件人的任何记录，例如“sender1”，我必须插入新记录，否则我必须更新属于“user1”的现有记录。
So if there isn't any record already stored, I would execute
所以如果没有任何记录已经存储，我会执行
# record_id is AUTO_INCREMENT field
INSERT INTO messages (sender, sent_time, status)
VALUES (@sender, time, @status)
Otherwise I would execute UPDATE statement.
否则我会执行 UPDATE 语句。
Anyway.. does anyone know how to combine these two statements in order to insert a new record if there isn't any record where the field sender value is "user1" otherwise update the existing record?
无论如何..有谁知道如何组合这两个语句以插入新记录，如果没有任何记录的字段发送者值为“user1”，否则更新现有记录？
回答by Andomar
MySQL supports the insert-on-duplicatesyntax, f.e.:
MySQL 支持insert-on-duplicate语法，fe：
INSERT INTO table (key,col1) VALUES (1,2)
ON DUPLICATE KEY UPDATE col1 = 2;
回答by BalusC
If you have solid constraints on the table, then you can also use the REPLACE INTOfor that. Here's a cite from MySQL:
如果您对表有严格的约束，那么您也可以使用REPLACE INTO。这是来自 MySQL 的引用：
REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
REPLACE 的工作方式与 INSERT 完全相同，但如果表中的旧行与 PRIMARY KEY 或 UNIQUE 索引的新行具有相同的值，则在插入新行之前删除旧行。
The syntax is basically the same as INSERT INTO, just replace INSERTby REPLACE.
语法与 基本相同INSERT INTO，只是替换INSERT为REPLACE。
INSERT INTO messages (sender, sent_time, status) VALUES (@sender, time, @status)
would then be
那么将是
REPLACE INTO messages (sender, sent_time, status) VALUES (@sender, time, @status)
Note that this is a MySQL-specific command which doesn't occur in other DB's, so keep portability in mind.
请注意，这是一个特定于 MySQL 的命令，不会出现在其他数据库中，因此请记住可移植性。
回答by Ike Walker
As others have mentioned, you should use "insert...on duplicate key update", sometimes referred to as an "upsert". However, in your specific case you don't want to use a static value in the update, but rather the values you pass in to the values clause of the insert statement.
正如其他人所提到的，您应该使用“插入...重复密钥更新”，有时也称为“更新插入”。但是，在您的特定情况下，您不想在更新中使用静态值，而是希望传递给 insert 语句的 values 子句的值。
Specifically, I think you want to update two columns if the row already exists:
具体来说，如果该行已经存在，我认为您想更新两列：
1) sent_time
2) status
In order to do this, you would use an "upsert" statement like this (using your example):
为此，您将使用这样的“upsert”语句（使用您的示例）：
INSERT INTO messages (sender, sent_time, status)
VALUES (@sender, time, @status)
ON DUPLICATE KEY UPDATE
sent_time = values(sent_time),
status = values(status);
回答by Sampson
Check out "Insert on Duplicate Key Update".
查看“在重复密钥更新时插入”。
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
UPDATE table SET c=c+1 WHERE a=1;
回答by Julian Davchev
One options is using on duplicate update syntax
一种选择是使用重复的更新语法
http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html
http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html
Other options is doing select to figure out if record exists and then doind inser/update accordingly. Mind that if you're withing transaction select will not explicitly terminate the transaction so it's safe using it.
其他选项是选择以找出记录是否存在，然后相应地执行插入/更新。请注意，如果您使用事务 select 不会明确终止事务，因此使用它是安全的。
回答by Rajeev Kumar Mehta
use merge statement :
merge into T1
using T2
on (T1.ID = T2.ID)
when matched
then update set
T1.Name = T2.Name
when not matched
then insert values (T2.ID,T2.Name);
