MySQL 与插入相同的重复密钥更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14383503/
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
On Duplicate Key Update same as insert
提问by Roy
I've searched around but didn't find if it's possible.
我四处寻找，但没有找到是否可能。
I've this MySQL query:
我有这个 MySQL 查询：
INSERT INTO table (id,a,b,c,d,e,f,g) VALUES (1,2,3,4,5,6,7,8)
Field id has a "unique index", so there can't be two of them. Now if the same id is already present in the database, I'd like to update it. But do I really have to specify all these field again, like:
字段 id 有一个“唯一索引”，所以不能有两个。现在，如果数据库中已经存在相同的 id，我想更新它。但是我真的必须再次指定所有这些字段，例如：
INSERT INTO table (id,a,b,c,d,e,f,g) VALUES (1,2,3,4,5,6,7,8)
ON DUPLICATE KEY UPDATE a=2,b=3,c=4,d=5,e=6,f=7,g=8
Or:
或者：
INSERT INTO table (id,a,b,c,d,e,f,g) VALUES (1,2,3,4,5,6,7,8)
ON DUPLICATE KEY UPDATE a=VALUES(a),b=VALUES(b),c=VALUES(c),d=VALUES(d),e=VALUES(e),f=VALUES(f),g=VALUES(g)
I've specified everything already in the insert...
我已经在插入中指定了所有内容......
A extra note, I'd like to use the work around to get the ID to!
额外说明，我想使用变通方法来获取 ID！
id=LAST_INSERT_ID(id)
I hope somebody can tell me what the most efficient way is.
我希望有人能告诉我最有效的方法是什么。
采纳答案by Roy
There is no other way, I have to specify everything twice. First for the insert, second in the update case.
没有其他办法，我必须两次指定所有内容。第一个用于插入，第二个用于更新案例。
回答by hjpotter92
The UPDATEstatement is given so that older fields can be updated to new value. If your older values are the same as your new ones, why would you need to update it in any case?
UPDATE给出该语句以便可以将旧字段更新为新值。如果旧值与新值相同，为什么无论如何都需要更新它？
For eg. if your columns ato gare already set as 2to 8; there would be no need to re-update it.
例如。如果你的列a到g已经被设置为2到8; 没有必要重新更新它。
Alternatively, you can use:
或者，您可以使用：
INSERT INTO table (id,a,b,c,d,e,f,g)
VALUES (1,2,3,4,5,6,7,8)
ON DUPLICATE KEY
UPDATE a=a, b=b, c=c, d=d, e=e, f=f, g=g;
To get the idfrom LAST_INSERT_ID; you need to specify the backend app you're using for the same.
要获得id从LAST_INSERT_ID; 您需要指定您正在使用的后端应用程序。
For LuaSQL, a conn:getlastautoid()fetches the value.
对于 LuaSQL， aconn:getlastautoid()获取值。
回答by Danack
There is a MySQL specific extension to SQL that may be what you want - REPLACE INTO
有一个特定于 MySQL 的 SQL 扩展可能是您想要的 - REPLACE INTO
However it does not work quite the same as 'ON DUPLICATE UPDATE'
但是它的工作方式与“ON DUPLICATE UPDATE”不太一样
It deletes the old row that clashes with the new row and then inserts the new row. So long as you don't have a primary key on the table that would be fine, but if you do, then if any other table references that primary key
You can't reference the values in the old rows so you can't do an equivalent of
INSERT INTO mytable (id, a, b, c) values ( 1, 2, 3, 4) ON DUPLICATE KEY UPDATE id=1, a=2, b=3, c=c + 1;
它删除与新行冲突的旧行，然后插入新行。只要您在表上没有主键就可以了，但是如果您这样做了，那么如果任何其他表引用该主键
你不能引用旧行中的值，所以你不能做等效的
INSERT INTO mytable (id, a, b, c) values ( 1, 2, 3, 4) ON DUPLICATE KEY UPDATE id=1, a=2, b=3, c=c + 1;
I'd like to use the work around to get the ID to!
我想使用解决方法来获取 ID！
That should work — last_insert_id() should have the correct value so long as your primary key is auto-incrementing.
这应该有效——只要你的主键是自动递增的，last_insert_id() 就应该有正确的值。
However as I said, if you actually use that primary key in other tables, REPLACE INTOprobably won't be acceptable to you, as it deletes the old row that clashed via the unique key.
但是，正如我所说，如果您实际在其他表中使用该主键，您REPLACE INTO可能无法接受，因为它会删除通过唯一键发生冲突的旧行。
Someone else suggested beforeyou can reduce some typing by doing:
INSERT INTO `tableName` (`a`,`b`,`c`) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE `a`=VALUES(`a`), `b`=VALUES(`b`), `c`=VALUES(`c`);
回答by Danack
Here is a solution to your problem:
这是您的问题的解决方案：
I've tried to solve problem like yours & I want to suggest to test from simple aspect.
我试图解决像你这样的问题，我想建议从简单的方面进行测试。
Follow these steps: Learn from simple solution.
请按照以下步骤操作： 从简单的解决方案中学习。
Step 1: Create a table schemausing this SQL Query:
步骤 1：使用此 SQL 查询创建表架构：
CREATE TABLE IF NOT EXISTS `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(30) NOT NULL,
`password` varchar(32) NOT NULL,
`status` tinyint(1) DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `no_duplicate` (`username`,`password`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
Step 2: Create an index of two columnsto prevent duplicate data using following SQL Query:
步骤 2：使用以下 SQL 查询创建两列的索引以防止重复数据：
ALTER TABLE `user` ADD INDEX no_duplicate (`username`, `password`);
or, Create an index of two column from GUIas follows:
或者，从 GUI 创建两列的索引，如下所示：
Step 3: Update if exist, insert if notusing following queries:
第 3 步：如果存在则更新，如果不使用以下查询则插入：
INSERT INTO `user`(`username`, `password`) VALUES ('ersks','Nepal') ON DUPLICATE KEY UPDATE `username`='master',`password`='Nepal';
INSERT INTO `user`(`username`, `password`) VALUES ('master','Nepal') ON DUPLICATE KEY UPDATE `username`='ersks',`password`='Nepal';
回答by Chris
Just in case you are able to utilize a scripting language to prepare your SQL queries, you could reuse field=value pairs by using SETinstead of (a,b,c) VALUES(a,b,c).
以防万一您能够利用脚本语言来准备 SQL 查询，您可以通过使用SET代替(a,b,c) VALUES(a,b,c).
An example with PHP:
PHP 示例：
$pairs = "a=$a,b=$b,c=$c";
$query = "INSERT INTO $table SET $pairs ON DUPLICATE KEY UPDATE $pairs";
Example table:
示例表：
CREATE TABLE IF NOT EXISTS `tester` (
`a` int(11) NOT NULL,
`b` varchar(50) NOT NULL,
`c` text NOT NULL,
UNIQUE KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
回答by Matej
You may want to consider using REPLACE INTOsyntax, but be warned, upon duplicate PRIMARY / UNIQUE key, it DELETESthe row and INSERTSa new one.
您可能要考虑使用REPLACE INTO的语法，但被警告，在重复的主键/唯一键，它DELETES行和INSERTS一个新的。
You won't need to re-specify all the fields. However, you should consider the possible performance reduction (depends on your table design).
您无需重新指定所有字段。但是，您应该考虑可能的性能降低（取决于您的表设计）。
Caveats:
注意事项：
- If you have AUTO_INCREMENT primary key, it will be given a new one
- Indexes will probably need to be updated
- 如果你有 AUTO_INCREMENT 主键，它会被赋予一个新的
- 索引可能需要更新
回答by Galang Piliang
I know it's late, but i hope someone will be helped of this answer
我知道已经晚了，但我希望有人能从这个答案中得到帮助
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
You can read the tutorial below here :
您可以在此处阅读以下教程：
https://mariadb.com/kb/en/library/insert-on-duplicate-key-update/
http://www.mysqltutorial.org/mysql-insert-or-update-on-duplicate-key-update/
https://mariadb.com/kb/en/library/insert-on-duplicate-key-update/
http://www.mysqltutorial.org/mysql-insert-or-update-on-duplicate-key-update/
回答by pitu
you can use insert ignore for such case, it will ignore if it gets duplicate records INSERT IGNORE ... ; -- without ON DUPLICATE KEY
您可以在这种情况下使用插入忽略，如果它获得重复记录，它将忽略 INSERT IGNORE ... ；-- 没有 ON DUPLICATE KEY
