MySQL MySql表插入如果不存在否则更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6030071/
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
MySql Table Insert if not exist otherwise update
提问by OHLáLá
UPDATE AggregatedData SET datenum="734152.979166667",
Timestamp="2010-01-14 23:30:00.000" WHERE datenum="734152.979166667";
It works if the datenumexists, but I want to insert this data as a new row if the datenumdoes not exist.
如果datenum存在，它就可以工作，但如果不存在，我想将此数据作为新行插入datenum。
UPDATE
更新
the datenum is unique but that's not the primary key
datenum 是唯一的，但这不是主键
回答by Ike Walker
Jai is correct that you should use INSERT ... ON DUPLICATE KEY UPDATE.
Jai 是正确的，您应该使用INSERT ... ON DUPLICATE KEY UPDATE.
Note that you do not need to include datenum in the update clause since it's the unique key, so it should not change. You do need to include all of the other columns from your table. You can use the VALUES()function to make sure the proper values are used when updating the other columns.
请注意，您不需要在更新子句中包含 datenum，因为它是唯一键，因此不应更改。您确实需要包括表中的所有其他列。您可以使用该VALUES()函数来确保在更新其他列时使用正确的值。
Here is your update re-written using the proper INSERT ... ON DUPLICATE KEY UPDATEsyntax for MySQL:
这是使用正确INSERT ... ON DUPLICATE KEY UPDATE的 MySQL 语法重写的更新：
INSERT INTO AggregatedData (datenum,Timestamp)
VALUES ("734152.979166667","2010-01-14 23:30:00.000")
ON DUPLICATE KEY UPDATE
Timestamp=VALUES(Timestamp)
回答by Jai
Try using this:
尝试使用这个：
If you specify
ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in aUNIQUE index orPRIMARY KEY, MySQL performs an [UPDATE`](http://dev.mysql.com/doc/refman/5.7/en/update.html) of the old row...The
ON DUPLICATE KEY UPDATEclause can contain multiple column assignments, separated by commas.With
ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify theCLIENT_FOUND_ROWSflag tomysql_real_connect()when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values...
如果您指定
ON DUPLICATE KEY UPDATE，并且插入的行会导致旧行的UNIQUE index orPRIMARY KEY, MySQL performs an [UPDATE`]( http://dev.mysql.com/doc/refman/5.7/en/update.html) 中出现重复值。 ..该
ON DUPLICATE KEY UPDATE子句可以包含多个列分配，以逗号分隔。使用
ON DUPLICATE KEY UPDATE，如果将行作为新行插入，则每行的受影响行值为 1，如果更新现有行，则为 2，如果现有行设置为其当前值，则为 0。如果在连接到mysqld时指定CLIENT_FOUND_ROWS标志为，则如果现有行设置为其当前值，则受影响的行值为 1（而不是 0）...mysql_real_connect()
回答by salfter
I had a situation where I needed to update or insert on a table according to two fields (both foreign keys) on which I couldn't set a UNIQUE constraint (so INSERT ... ON DUPLICATE KEY UPDATE won't work). Here's what I ended up using:
我有一种情况，我需要根据两个字段（都是外键）更新或插入表，但我无法在其上设置 UNIQUE 约束（因此 INSERT ... ON DUPLICATE KEY UPDATE 不起作用）。这是我最终使用的：
replace into last_recogs (id, hasher_id, hash_id, last_recog)
select l.* from
(select id, hasher_id, hash_id, [new_value] from last_recogs
where hasher_id in (select id from hashers where name=[hasher_name])
and hash_id in (select id from hashes where name=[hash_name])
union
select 0, m.id, h.id, [new_value]
from hashers m cross join hashes h
where m.name=[hasher_name]
and h.name=[hash_name]) l
limit 1;
This example is cribbed from one of my databases, with the input parameters (two names and a number) replaced with [hasher_name], [hash_name], and [new_value]. The nested SELECT...LIMIT 1 pulls the first of either the existing record or a new record (last_recogs.id is an autoincrement primary key) and uses that as the value input into the REPLACE INTO.
此示例取自我的一个数据库，输入参数（两个名称和一个数字）替换为 [hasher_name]、[hash_name] 和 [new_value]。嵌套的 SELECT...LIMIT 1 提取现有记录或新记录中的第一个（last_recogs.id 是自动增量主键），并将其用作 REPLACE INTO 的值输入。
