MySQL 条件插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/913841/
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 Conditional Insert
提问by The Unknown
I am having a difficult time forming a conditional INSERT
我很难形成有条件的 INSERT
I have x_table with columns (instance, user, item) where instance ID is unique. I want to insert a new row only if the user already does not have a given item.
我有 x_table 列（实例、用户、项目），其中实例 ID 是唯一的。仅当用户还没有给定项目时，我才想插入新行。
For example trying to insert instance=919191 user=123 item=456
例如尝试插入 instance=919191 user=123 item=456
Insert into x_table (instance, user, item) values (919191, 123, 456)
ONLY IF there are no rows where user=123 and item=456
Any help or guidance in the right direction would be much appreciated.
任何在正确方向上的帮助或指导将不胜感激。
回答by Jonathan Leffler
If your DBMS does not impose limitations on which table you select from when you execute an insert, try:
如果您的 DBMS 没有对您在执行插入时从哪个表中选择施加限制，请尝试：
INSERT INTO x_table(instance, user, item)
SELECT 919191, 123, 456
FROM dual
WHERE NOT EXISTS (SELECT * FROM x_table
WHERE user = 123
AND item = 456)
In this, dualis a table with one row only (found originally in Oracle, now in mysql too). The logic is that the SELECT statement generates a single row of data with the required values, but only when the values are not already found.
其中，dual是一个只有一行的表（最初在 Oracle 中发现，现在也在 mysql 中）。逻辑是 SELECT 语句生成具有所需值的单行数据，但仅当尚未找到这些值时。
Alternatively, look at the MERGE statement.
或者，查看 MERGE 语句。
回答by MrD
You can also use INSERT IGNOREwhich silently ignores the insert instead of updating or inserting a row when you have a unique index on (user, item).
INSERT IGNORE当您在 (user, item) 上有唯一索引时，您还可以使用which 以静默方式忽略插入而不是更新或插入行。
The query will look like this:
查询将如下所示：
INSERT IGNORE INTO x_table(instance, user, item) VALUES (919191, 123, 456)
You can add the unique index with CREATE UNIQUE INDEX user_item ON x_table (user, item).
您可以添加唯一索引CREATE UNIQUE INDEX user_item ON x_table (user, item)。
回答by temple
Have you ever tried something like that?
你有没有尝试过这样的事情？
INSERT INTO x_table
SELECT 919191 as instance, 123 as user, 456 as item
FROM x_table
WHERE (user=123 and item=456)
HAVING COUNT(*) = 0;
回答by Alex Martelli
With a UNIQUE(user, item), do:
使用UNIQUE(user, item)，请执行以下操作：
Insert into x_table (instance, user, item) values (919191, 123, 456)
ON DUPLICATE KEY UPDATE user=123
the user=123bit is a "no-op" to match the syntax of the ON DUPLICATEclause without actually doing anything when there are duplicates.
该user=123位是一个“无操作”，用于匹配ON DUPLICATE子句的语法，但在存在重复项时无需实际执行任何操作。
回答by Gew
In case you don't want to set a unique constraint, this works like a charm :
如果您不想设置唯一约束，这就像一个魅力：
INSERT INTO `table` (`column1`, `column2`) SELECT 'value1', 'value2' FROM `table` WHERE `column1` = 'value1' AND `column2` = 'value2' HAVING COUNT(`column1`) = 0
Hope it helps !
希望能帮助到你 ！
回答by martin
What you want is INSERT INTO table (...) SELECT ... WHERE .... from MySQL 5.6 manual.
你想要的是INSERT INTO table (...) SELECT ... WHERE .... 来自MySQL 5.6 手册。
In you case it's:
在你的情况下是：
INSERT INTO x_table (instance, user, item) SELECT 919191, 123, 456
WHERE (SELECT COUNT(*) FROM x_table WHERE user=123 AND item=456) = 0
Or maybe since you're not using any complicated logic to determiante whether to run the INSERTor not you could just set a UNIQUEkey on the combination of these two columns and then use INSERT IGNORE.
或者，也许因为您没有使用任何复杂的逻辑来确定是否运行，INSERT您可以只UNIQUE在这两列的组合上设置一个键，然后使用INSERT IGNORE.
回答by Keith Becker
You can use the following solution to solve your problem:
您可以使用以下解决方案来解决您的问题：
INSERT INTO x_table(instance, user, item)
SELECT 919191, 123, 456
FROM dual
WHERE 123 NOT IN (SELECT user FROM x_table)
回答by NickZoic
If you add a constraint that (x_table.user, x_table.item) is unique, then inserting another row with the same user and item will fail.
如果添加 (x_table.user, x_table.item) 唯一的约束，则插入具有相同用户和项目的另一行将失败。
eg:
例如：
mysql> create table x_table ( instance integer primary key auto_increment, user integer, item integer, unique (user, item));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into x_table (user, item) values (1,2),(3,4);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into x_table (user, item) values (1,6);
Query OK, 1 row affected (0.00 sec)
mysql> insert into x_table (user, item) values (1,2);
ERROR 1062 (23000): Duplicate entry '1-2' for key 2
回答by Beatles1692
Although it's good to check for duplication before inserting your data I suggest that you put a unique constraint/index on your columns so that no duplicate data can be inserted by mistake.
虽然在插入数据之前检查重复是很好的，但我建议您在列上放置一个唯一的约束/索引，这样就不会错误地插入重复的数据。
回答by Danny
Slight modification to Alex's response, you could also just reference the existing column value:
对 Alex 的回复稍作修改，您也可以只引用现有的列值：
Insert into x_table (instance, user, item) values (919191, 123, 456)
ON DUPLICATE KEY UPDATE user=user
