How to Ensure Unique Rows When Inserting Data into MySQL?

MySQL Conditional Insertion: Ensuring Row Uniqueness

MySQL provides versatile mechanisms for data insertion, allowing you to implement conditional insertions that prevent duplicate rows.

One scenario involves a table named x_table with columns instance, user, and item, where instance is unique. To insert a new row into x_table only if the combination of user and item does not already exist, follow these steps:

Using the INSERT...SELECT Syntax

If your MySQL version supports selecting from other tables during insertion, consider the following query:

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, dual is a single-row table that ensures the availability of a valid row, while the NOT EXISTS condition checks for the absence of duplicate rows. If no matches are found, the new row is inserted.

Exploring the MERGE Statement

Alternatively, the MERGE statement provides a powerful tool for conditional insertions. Its syntax is as follows:

MERGE INTO x_table AS target
USING (
    SELECT 919191 AS instance, 123 AS user, 456 AS item
) AS source
ON (target.user = source.user AND target.item = source.item)
WHEN NOT MATCHED THEN INSERT VALUES (source.instance, source.user, source.item);

In this case, the USING clause specifies the values to be inserted, while the ON clause establishes the matching criteria. If no matching rows exist in the target table (x_table), INSERT is performed.

The above is the detailed content of How to Ensure Unique Rows When Inserting Data into MySQL?. For more information, please follow other related articles on the PHP Chinese website!