INSERT ... ON DUPLICATE KEY UPDATE is a statement in MySQL that performs insertion or update operations when repeated key conflicts are handled. Its core mechanism is: if the insertion data does not violate the primary key or unique constraint, it will be inserted normally; if it conflicts, the update part will be executed. This statement is suitable for user registration, order writing and other scenarios, and can simplify logic and ensure data integrity. Key points of use include: 1. The table must define a primary key or a unique index; 2. The update of multiple fields must be separated by commas; 3. The insertion value can be referenced through the VALUES() function; 4. It supports inserting multiple rows at a time, and each row independently determines whether it is updated. Notes include: Ensure the index is accurate to avoid misinterpretation, pay attention to lock performance under high concurrency, use transaction control reasonably, and be sure to test and verify before going online.

MySQL's INSERT ... ON DUPLICATE KEY UPDATE is a very practical function, especially when processing duplicate data, which can avoid errors and automatically update existing records. Its core function is: if the inserted data does not conflict with unique constraints or primary key constraints, it will be inserted normally; if it conflicts, the subsequent update operation will be automatically performed .

This is used a lot in actual development, such as user registration, order writing, data statistics and other scenarios, which can not only ensure data integrity, but also simplify logical judgments.

What is "Duplicate Key"?

The prerequisite for understanding this statement is to understand what a "repeat key" is.

The primary key (PRIMARY KEY) and unique index (UNIQUE INDEX) in MySQL can be used as criteria for determining whether it is duplicated. When you insert a record, if a field value already exists in these constraint fields, a "duplicate key error" is triggered.

And ON DUPLICATE KEY UPDATE will not report an error in this case, but will execute the update part you specified.

For example:

 CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE,
    name VARCHAR(50),
    age INT
);

In this table, email is unique. If you execute:

 INSERT INTO users (email, name, age)
VALUES ('test@example.com', 'Tom', 25)
ON DUPLICATE KEY UPDATE name = 'Jerry', age = 30;

• If 'test@example.com' does not exist yet, a new record will be inserted;
• If it already exists, the corresponding record name will be changed to 'Jerry' and age will be changed to 30 .

Several key points in actual use

1. Ensure there is a corresponding unique constraint or primary key

This is the most basic point. If UNIQUE or PRIMARY KEY is not defined, then ON DUPLICATE KEY UPDATE will not take effect and will not have any effect.

Common errors:

• Forgot to add a unique index to the field
• Insert multiple fields into a unique combination, but only a single field index is added

Therefore, when designing the table structure, you must figure out which fields need unique constraints and add them in advance.

2. When updating multiple fields, the syntax must be correct

When updating multiple fields, you need to separate them with commas:

 INSERT INTO users (email, name, age)
VALUES ('test@example.com', 'Tom', 25)
ON DUPLICATE KEY UPDATE
    name = 'Jerry',
    age = 30;

Do not miss commas or write the wrong field name.

3. It can be used in conjunction with the VALUES() function

If you want to reference the inserted value when updating, you can use VALUES(column_name) :

 INSERT INTO users (email, name, age)
VALUES ('test@example.com', 'Tom', 28)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    age = VALUES(age) 1;

What this means here:

• If there is a conflict, set name to the value passed when inserting;
• Set age to the insert value plus 1.

This is useful for some statistics class operations, such as updating the counter every time you insert it.

4. Multiple inserts can also be processed together

You can insert multiple lines at once, each line will trigger a check:

 INSERT INTO users (email, name, age)
VALUES
    ('a@example.com', 'A', 10),
    ('b@example.com', 'B', 20)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    age = VALUES(age);

If there is a conflict in one line, only that line will be updated and the other lines will be inserted as usual.

Recommendations and precautions for use

• Avoid mis-update : Make sure that the unique index is accurate, otherwise it may be updated to records that should not be updated.
• Performance considerations : Frequent use of this statement has little impact on performance, but if the concurrency is very high, you should pay attention to the locking problem.
• Transaction control : If used in a transaction, ensure logical consistency.
• Test verification : Be sure to test before going online, especially when joint unique indexes are in the case of errors.

In general, INSERT ... ON DUPLICATE KEY UPDATE is a very easy to use and safe operation, suitable for most scenarios that require "insert or update". As long as the table structure is designed reasonably, it is very easy to use.

Basically all of this is it. You need to pay more attention to the details that are not complicated but are prone to ignore.

The above is the detailed content of mysql insert on duplicate key update. For more information, please follow other related articles on the PHP Chinese website!