条件插入

我有一个困难的时间形成一个条件插入

我有一个带有列(实例、用户、项)的 x _ table，其中实例 ID 是唯一的。只有在用户已经没有给定项时，我才想插入新行。

例如，尝试插入 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

任何正确方向上的帮助或指导都将不胜感激。

106570 次浏览
小开 
Insert into x_table (instance, user, item) values (919191, 123, 456)
where ((select count(*) from x_table where user=123 and item=456) = 0);

The syntax may vary depending on your DB...

小开

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.

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
小开

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.

小开
最佳答案

If your DBMS does not impose limitations on which table you select from when you execute an insert, try:

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, dual is 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.

Alternatively, look at the MERGE statement.

小开

With a UNIQUE(user, item), do:

Insert into x_table (instance, user, item) values (919191, 123, 456)
ON DUPLICATE KEY UPDATE user=123

the user=123 bit is a "no-op" to match the syntax of the ON DUPLICATE clause without actually doing anything when there are duplicates.

小开

Slight modification to Alex's response, you could also just reference the existing column value:

Insert into x_table (instance, user, item) values (919191, 123, 456)
ON DUPLICATE KEY UPDATE user=user
小开

Have you ever tried something like that?

INSERT INTO x_table (instance, user, item)
SELECT 919191 as instance, 123 as user, 456 as item
FROM x_table
WHERE (user=123 and item=456)
HAVING COUNT(*) = 0;
小开

You can also use INSERT IGNORE which silently ignores the insert instead of updating or inserting a row when you have a unique index on (user, item).

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).

小开

So this one stands for PostgreSQL 

INSERT INTO x_table
SELECT NewRow.*
FROM (SELECT 919191 as instance, 123 as user, 456 as item) AS NewRow
LEFT JOIN x_table
ON x_table.user = NewRow.user AND x_table.item = NewRow.item
WHERE x_table.instance IS NULL
小开

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 !

小开

What you want is INSERT INTO table (...) SELECT ... WHERE .... from MySQL 5.6 manual.

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 INSERT or not you could just set a UNIQUE key on the combination of these two columns and then use INSERT IGNORE.

小开

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)
小开

I have found out today that a SELECT statement can have a WHERE condition even if it has no FROM clause and does not read any tables at all.

This makes it very easy to conditionally insert something using this construct:

SELECT ... INTO @condition;
-- or if you prefer: SET @condition = ...


INSERT INTO myTable (col1, col2) SELECT 'Value 1', 'Value 2' WHERE @condition;

Tested this on MySQL 5.7 and MariaDB 10.3.


提交答案