MySQL Conditional Insert

问题:

I am having a difficult time forming a conditional 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.

For example trying to insert 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.

回答1:

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

回答2:

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

回答3:

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;

回答4:

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.

回答5:

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 !

回答6:

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

回答7:

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.

回答8:

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.

回答9:

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

回答10:

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

回答11:

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

回答12:

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)