Triển khai INSERT ... ON DUPLICATE KEY UPDATE trong MySQL

Upsert trong MySQL: INSERT ... ON DUPLICATE KEY UPDATE

Cơ chế INSERT ... ON DUPLICATE KEY UPDATE (thường gọi là "upsert"): Thử INSERT, nếu vi phạm PRIMARY KEY hoặc UNIQUE KEY → tự động chuyển sang UPDATE hàng hiện có.

Ví dụ minh họa

Tạo bảng DemoTable733 với UNIQUE KEY trên StudentName:

mysql> CREATE TABLE DemoTable733 (
    StudentId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    StudentName VARCHAR(100),
    StudentMarks INT,
    UNIQUE KEY Un_Name (StudentName)
);
Query OK, 0 rows affected (0.60 sec)

Thực hiện upsert:

mysql> INSERT INTO DemoTable733(StudentName,StudentMarks) VALUES('John',45) ON DUPLICATE KEY UPDATE StudentMarks=86;
Query OK, 1 row affected (0.19 sec)
mysql> INSERT INTO DemoTable733(StudentName,StudentMarks) VALUES('Adam',65) ON DUPLICATE KEY UPDATE StudentMarks=86;
Query OK, 1 row affected (0.15 sec)
mysql> INSERT INTO DemoTable733(StudentName,StudentMarks) VALUES('Carol',75) ON DUPLICATE KEY UPDATE StudentMarks=86;
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO DemoTable733(StudentName,StudentMarks) VALUES('John',45) ON DUPLICATE KEY UPDATE StudentMarks=86;
Query OK, 2 rows affected (0.12 sec)

Kết quả (John được UPDATE Marks từ 45 → 86, không tạo hàng mới):

mysql> SELECT * FROM DemoTable733;

+-----------+-------------+--------------+
| StudentId | StudentName | StudentMarks |
+-----------+-------------+--------------+
| 1         | John        | 86           |
| 2         | Adam        | 65           |
| 3         | Carol       | 75           |
+-----------+-------------+--------------+
3 rows in set (0.00 sec)

Giải thích rows affected

• 1 row affected: INSERT mới thành công
• 2 rows affected: UPDATE hàng hiện có (MySQL đếm: 1 delete + 1 insert logic)
• 0 rows affected: UPDATE nhưng giá trị không thay đổi

Sử dụng VALUES() để tham chiếu giá trị INSERT

INSERT INTO DemoTable733(StudentName,StudentMarks) VALUES('John',90) 
ON DUPLICATE KEY UPDATE StudentMarks = VALUES(StudentMarks) + 10;

Nếu John tồn tại: Marks = 90 + 10 = 100. Nếu mới: Marks = 90.

Nhiều cột UNIQUE / Composite Key

CREATE TABLE scores (
    student_id INT,
    subject_id INT,
    score INT,
    UNIQUE KEY uq_student_subject (student_id, subject_id)
);

INSERT INTO scores VALUES(1, 101, 85) 
ON DUPLICATE KEY UPDATE score = VALUES(score);

Lưu ý quan trọng

• Bắt buộc có PRIMARY KEY hoặc UNIQUE INDEX mới kích hoạt ON DUPLICATE KEY
• Không hoạt động với FOREIGN KEY violation
• Trigger: BEFORE INSERT và BEFORE UPDATE đều chạy (INSERT trước, nếu trùng thì UPDATE)
• Auto-increment: Giá trị ID vẫn tăng kể cả khi UPDATE (có thể tạo lỗ hổng ID)
• Replication: Statement-based replication có thể gây inconsistency → dùng ROW-based

MySQL 8.0.20+: Alias cho VALUES()

INSERT INTO t(a,b) VALUES(1,2) AS new 
ON DUPLICATE KEY UPDATE b = new.b + 1;