MySQL条件插入 [英] MySQL Conditional Insert

问题描述

我在形成条件插入时遇到困难

I am having a difficult time forming a conditional INSERT

我的x_table带有实例ID唯一的列(实例，用户，项目).我只想在用户已经没有给定项目的情况下插入新行.

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.

例如，尝试插入instance = 919191 user = 123 item = 456

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.

推荐答案

如果DBMS在执行插入操作时不限制从哪个表中选择表，请尝试:

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)

在此，dual是仅具有一行的表(最初在Oracle中找到，现在也在mysql中).逻辑是SELECT语句生成具有所需值的单行数据，但仅当尚未找到这些值时.

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.

或者，查看MERGE语句.

Alternatively, look at the MERGE statement.

这篇关于MySQL条件插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！