MySQLでONの重複キーアップデートステートメントを使用する方法は？

ON DUPLICATE KEY UPDATE 用于处理唯一键或主键冲突，1. 当插入数据出现重复时，不报错而是更新指定字段；2. 适用于计数器、数据同步等场景；3. 语法为 INSERT INTO ... VALUES ... ON DUPLICATE KEY UPDATE column = VALUES(column)；4. VALUES(column) 获取原计划插入的值；5. 仅在发生唯一键冲突时触发更新；6. 每条重复记录独立处理；7. 需注意 AUTO_INCREMENT 和 TIMESTAMP 列的意外更新；8. 可避免先查后插的复杂逻辑，提升效率；9. 该语法为 MySQL 特有，非标准 SQL；10. 也可使用 INSERT INTO ... SET 格式提高可读性；该机制通过单条语句实现安全的插入或更新操作，前提是表已定义正确的唯一约束。

The ON DUPLICATE KEY UPDATE clause in MySQL is used with the INSERT statement to handle situations where a duplicate key conflict occurs—typically due to a duplicate entry in a unique index or primary key. Instead of letting the query fail, you can specify actions to update the existing row.

When to Use ON DUPLICATE KEY UPDATE

This is useful when you want to insert a new record, but if a duplicate key already exists, update specific fields rather than throwing an error. It’s commonly used in scenarios like counters, upserts (update or insert), or syncing data.

Basic Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
    column1 = VALUES(column1),
    column2 = VALUES(column2),
    ...;

• VALUES(column) refers to the value that would have been inserted for that column.
• The update part only runs if a duplicate key is found.

Example: Insert or Update a User's Login Count

Suppose you have a users table:

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    login_count INT DEFAULT 0,
    last_login TIMESTAMP
);

You want to insert a new user, or if the user already exists (by username or id), just increment their login count and update the timestamp.

INSERT INTO users (id, username, login_count, last_login)
VALUES (1, 'john_doe', 1, NOW())
ON DUPLICATE KEY UPDATE
    login_count = login_count + 1,
    last_login = NOW();

• If user with id=1 doesn’t exist → insert the row.
• If user with id=1 already exists → increment login_count and update last_login.

Key Points to Remember

• Only triggers on unique key or primary key violations.
  The ON DUPLICATE KEY UPDATE only activates if the INSERT would cause a duplicate in a UNIQUE index or PRIMARY KEY.

  

• Use VALUES() to access the attempted insert values.
  VALUES(column) gives you the value you tried to insert, which is helpful for clean upsert logic.

• Only one row is updated per duplicate.
  If multiple rows cause duplicates in a multi-row INSERT, each is handled individually.

• Be cautious with auto-increment and timestamps.
  If you’re not careful, ON DUPLICATE KEY UPDATE might trigger unintended updates on AUTO_INCREMENT or TIMESTAMP columns.

Practical Tips

• You can set only some fields in the update, not all.
• Use it to avoid having to SELECT first and then conditionally INSERT or UPDATE—this is more efficient.
• It’s not standard SQL, so it’s MySQL-specific. Other databases use MERGE, UPSERT, or ON CONFLICT.

Alternative: Use INSERT ... SET Syntax

You can also write it using SET:

INSERT INTO users
SET id = 1,
    username = 'john_doe',
    login_count = 1,
    last_login = NOW()
ON DUPLICATE KEY UPDATE
    login_count = login_count + 1,
    last_login = NOW();

This format can be more readable for complex inserts.

Basically, ON DUPLICATE KEY UPDATE is a powerful way to handle conflicts gracefully in MySQL with a single query. Just make sure your table has the right unique constraints in place.

以上がMySQLでONの重複キーアップデートステートメントを使用する方法は？の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。