插入 MySQL 表或更新（如果存在）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4205181/
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
Insert into a MySQL table or update if exists
提问by Keshan
I want to add a row to a database table, but if a row exists with the same unique key I want to update the row.
我想向数据库表中添加一行，但如果存在具有相同唯一键的行，我想更新该行。
For example:
例如：
insert into table (id, name, age) values(1, "A", 19)
Let's say the unique key is id, and in my Database, there is a row with id = 1. In that case, I want to update that row with these values. Normally this gives an error.
If I use insert IGNOREit will ignore the error, but it still won't update.
假设唯一键是id，并且在我的数据库中，有一排带有id = 1. 在这种情况下，我想用这些值更新该行。通常这会产生错误。
如果我使用insert IGNORE它会忽略错误，但它仍然不会更新。
回答by Donnie
Use INSERT ... ON DUPLICATE KEY UPDATE
用 INSERT ... ON DUPLICATE KEY UPDATE
QUERY:
询问：
INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE
name="A", age=19
回答by Martin Schapendonk
Check out REPLACE
检查更换
http://dev.mysql.com/doc/refman/5.0/en/replace.html
http://dev.mysql.com/doc/refman/5.0/en/replace.html
REPLACE into table (id, name, age) values(1, "A", 19)
回答by Fabiano Souza
When using batch insert use the following syntax:
使用批量插入时，请使用以下语法：
INSERT INTO TABLE (id, name, age) VALUES (1, "A", 19), (2, "B", 17), (3, "C", 22)
ON DUPLICATE KEY UPDATE
name = VALUES (name),
...
回答by Luis Reyes
Try this out:
试试这个：
INSERT INTO table (id, name, age) VALUES (1, 'A', 19) ON DUPLICATE KEY UPDATE id = id + 1;
Hope this helps.
希望这可以帮助。
回答by Rasel
Try this:
尝试这个：
INSERT INTO table (id,name,age) VALUES('1','Mohammad','21') ON DUPLICATE KEY UPDATE name='Mohammad',age='21'
INSERT INTO table (id,name,age) VALUES('1','Mohammad','21') ON DUPLICATE KEY UPDATE name='Mohammad',age='21'
Note:
Here if id is the primary key then after first insertion with id='1'every time attempt to insert id='1'will update name and age and previous name age will change.
注意：
这里如果 id 是主键，那么在第一次插入后，id='1'每次尝试插入id='1'都会更新姓名和年龄，以前的姓名年龄会改变。
回答by Dilraj Singh
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);
All these solution will work regarding your question.
所有这些解决方案都适用于您的问题。
If you want to know in details regarding these statement visit this link
如果您想详细了解这些声明，请访问此链接
回答by DawnSong
回答by SteveCinq
Just because I was here looking for this solution but for updating from another identically-structured table (in my case website test DB to live DB):
仅仅因为我在这里寻找这个解决方案，但是为了从另一个结构相同的表更新（在我的例子中是网站测试数据库到实时数据库）：
INSERT live-db.table1
SELECT *
FROM test-db.table1 t
ON DUPLICATE KEY UPDATE
ColToUpdate1 = t.ColToUpdate1,
ColToUpdate2 = t.ColToUpdate2,
...
As mentioned elsewhere, only the columns you want to update need to be included after ON DUPLICATE KEY UPDATE.
正如其他地方提到的，只有您想要更新的列需要包含在ON DUPLICATE KEY UPDATE.
No need to list the columns in the INSERTor SELECT, though I agree it's probably better practice.
无需在INSERTor 中列出列SELECT，但我同意这可能是更好的做法。
回答by Rich
In case that you wanted to make a non-primaryfields as criteria/condition for ON DUPLICATE, you can make a UNIQUE INDEXkey on that table to trigger the DUPLICATE.
如果您想将non-primary字段作为 的条件/条件ON DUPLICATE，您可以UNIQUE INDEX在该表上创建一个键来触发DUPLICATE.
ALTER TABLE `table` ADD UNIQUE `unique_index`(`name`);
And in case you want to combine two fields to make it unique on the table, you can achieve this by adding more on the last parameter.
如果您想组合两个字段以使其在表格中独一无二，您可以通过在最后一个参数上添加更多字段来实现这一点。
ALTER TABLE `table` ADD UNIQUE `unique_index`(`name`, `age`);
Note, just make sure to delete first all the data that has the same nameand agevalue across the other rows.
注意，只要确保删除首先具有相同的数据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;
After that, it should trigger the ON DUPLICATEevent.
之后，它应该触发ON DUPLICATE事件。
INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE
name = VALUES(name), age = VALUES(age)
回答by Renish Gotecha
In my case i created below queries but in the first query if id1 is already exists and age is already there, after that if you create first query without agethan the value of agewill be none
在我的情况下，我创建了下面的查询，但是在第一个查询中，如果id1 已经存在并且年龄已经存在，那么如果您创建第一个查询而age不是值age将是 none
REPLACE into table SET `id` = 1, `name` = 'A', `age` = 19
for avoiding above issue create query like below
为避免上述问题，请创建如下查询
INSERT INTO table SET `id` = '1', `name` = 'A', `age` = 19 ON DUPLICATE KEY UPDATE `id` = "1", `name` = "A",`age` = 19
may it will help you ...
可能会帮助你...
