How Can I Perform Conditional Inserts in MySQL?

Understanding Conditional Inserts in MySQL

Conditional insert statements allow you to insert a new row into a table only if certain conditions are met. In MySQL, there is no dedicated syntax for conditional inserts, but you can achieve the same result using techniques such as subqueries and the MERGE statement.

Sample Scenario: Avoiding Duplicate Insertions

Consider a scenario where you have a table called x_table with columns (instance, user, item). The instance column is unique, and you want to avoid inserting duplicate rows where a specific user already has a given item.

For example, let's say you want to insert the row (instance=919191, user=123, item=456) if and only if there are no other rows in x_table with user=123 and item=456.

Using Subquery and Row Selection

To achieve this, you can use a subquery to check for the existence of the duplicate row:

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 statement, dual is a special table with only one row (originally found in Oracle and now also available in MySQL). The subquery within the SELECT statement selects a single row of data, but only if the values do not already exist in x_table.

Alternative: Using the MERGE Statement

MySQL also introduced a dedicated MERGE statement in version 8.0, which provides a convenient way to handle conditional inserts, updates, and deletions in a single statement:

MERGE INTO x_table (instance, user, item)
USING (
  SELECT 919191, 123, 456
  WHERE NOT EXISTS (SELECT * FROM x_table
                      WHERE user = 123
                        AND item = 456)
) AS new_row
ON x_table.user = new_row.user AND x_table.item = new_row.item
WHEN NOT MATCHED THEN INSERT (instance, user, item) VALUES (new_row.instance, new_row.user, new_row.item);

The MERGE statement uses a subquery similar to the previous example to check for the existence of the duplicate row. If the row does not exist (WHEN NOT MATCHED), then it executes an insert operation.

The above is the detailed content of How Can I Perform Conditional Inserts in MySQL?. For more information, please follow other related articles on the PHP Chinese website!