การใช้ INSERT ... ON DUPLICATE KEY UPDATE ใน MySQL

เมื่อแทรกข้อมูลใหม่แต่พบคีย์หลัก (PRIMARY KEY) หรือดัชนีเฉพาะ (UNIQUE INDEX) ซ้ำ จะเกิดข้อผิดพลาด วิธีแก้คือใช้ ON DUPLICATE KEY UPDATE เพื่ออัปเดตแถวเดิมแทน

mysql> CREATE TABLE DemoTable (
    -> Value INT
    -> );
Query OK, 0 rows affected (0.61 sec)

สร้างดัชนีเฉพาะ (UNIQUE INDEX) บนคอลัมน์ Value:

mysql> CREATE UNIQUE INDEX value_index ON DemoTable(Value);
Query OK, 0 rows affected (0.77 sec)

แทรกข้อมูลพร้อมกำหนดการอัปเดตหากคีย์ซ้ำ:

mysql> INSERT INTO DemoTable VALUES(40) ON DUPLICATE KEY UPDATE Value = Value + 1000;
Query OK, 1 row affected (0.12 sec)
mysql> INSERT INTO DemoTable VALUES(50) ON DUPLICATE KEY UPDATE Value = Value + 1000;
Query OK, 1 row affected (0.13 sec)
mysql> INSERT INTO DemoTable VALUES(40) ON DUPLICATE KEY UPDATE Value = Value + 1000;
Query OK, 2 rows affected (0.15 sec)

ครั้งที่ 3 ค่า 40 ซ้ำ จึงอัปเดตค่าเดิม (40) เพิ่ม 1000 กลายเป็น 1040

mysql> SELECT * FROM DemoTable;

+-------+
| Value |
+-------+
|    50 |
|  1040 |
+-------+
2 rows in set (0.00 sec)

ประโยชน์: เหมาะสำหรับการนับจำนวน (counter) หรืออัปเดตสถิติโดยไม่ต้องตรวจสอบก่อนว่าแถวมีอยู่แล้วหรือไม่