How to Perform a Conditional INSERT in MySQL to Ensure Unique Rows?

MySQL Conditional Insert: Inserting Only if Unique

MySQL provides limited support for conditional inserts. In this specific case, you aim to insert a row into the x_table only if the combination of user and item doesn't exist.

MySQL's INSERT statement doesn't allow direct conditional insertion. However, you can employ a workaround using a subquery. One approach is to use the NOT EXISTS clause:

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 query, the subquery with NOT EXISTS checks if the specified user and item already exist in the x_table. If they don't, the new row will be inserted. Otherwise, nothing happens.

Another option is to utilize the MERGE statement, which allows specifying conditional insertion and update operations in a single statement. However, MERGE is supported in MySQL only as an extension, not as part of the core functionality.

The above is the detailed content of How to Perform a Conditional INSERT in MySQL to Ensure Unique Rows?. For more information, please follow other related articles on the PHP Chinese website!