How Can I Efficiently Insert or Update Rows Based on a Composite Key in MySQL?

INSERT INTO or UPDATE with Double Condition

Consider a table that tracks data for multiple entities, identified by name and dates. Each day, new rows are generated with future dates for a finite list of name values. The goal is to insert a new row if it doesn't exist, or update the existing row if it does.

A common solution might be a stored procedure that checks for the condition, but this is infeasible due to the data being pushed from another language.

Solution: INSERT ON DUPLICATE KEY UPDATE

The MySQL feature INSERT ON DUPLICATE KEY UPDATE provides an elegant solution to this problem. It allows you to insert a new row if it doesn't exist, or update an existing row if it does, based on a unique key or index.

In this case, a composite unique key on (name, dates) would suffice. When an insert is attempted, MySQL will detect the conflict due to this unique key and perform the update instead.

Example:

CREATE TABLE myThing (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name INT NOT NULL,
    values1 INT NOT NULL,
    values2 INT NOT NULL,
    dates DATE NOT NULL,
    UNIQUE KEY (name, dates)
);

INSERT INTO myThing (name, values1, values2, dates) VALUES (777, 1, 1, '2015-07-11')
ON DUPLICATE KEY UPDATE values2 = values2 + 1;

By performing multiple inserts with the same (name, dates) values, you can update the values2 column accordingly:

INSERT INTO myThing (name, values1, values2, dates) VALUES (777, 1, 1, '2015-07-11')
ON DUPLICATE KEY UPDATE values2 = values2 + 1;

INSERT INTO myThing (name, values1, values2, dates) VALUES (777, 1, 1, '2015-07-11')
ON DUPLICATE KEY UPDATE values2 = values2 + 1;

Result:

SELECT *
FROM myThing;

+----+------+---------+---------+------------+
| id | name | values1 | values2 | dates      |
+----+------+---------+---------+------------+
| 1  | 777  | 1       | 3       | 2015-07-11 |
+----+------+---------+---------+------------+

As you can see, the INSERT ON DUPLICATE KEY UPDATE clause effectively updates the values2 column for the existing row.

The above is the detailed content of How Can I Efficiently Insert or Update Rows Based on a Composite Key in MySQL?. For more information, please follow other related articles on the PHP Chinese website!