home

หน้าแรก
 Computer >> บทช่วยสอนคอมพิวเตอร์ >  >> การเขียนโปรแกรม >> MySQL

การเพิ่ม Constraint สำหรับ ON DUPLICATE KEY UPDATE ใน MySQL

คำสั่ง INSERT ... ON DUPLICATE KEY UPDATE ต้องการให้มี Unique Constraint (Primary Key หรือ Unique Index) บนคอลัมน์ที่ตรวจสอบ Duplicate เพื่อทราบว่าระเบียน "ซ้ำ" ตามคีย์ไหน จึงจะ Update แทน Insert

ตัวอย่างการใช้งาน

สร้างตาราง:

mysql> CREATE TABLE DemoTable1459 (Name VARCHAR(20), Score INT);

เพิ่ม Unique Constraint บน Name:

mysql> ALTER TABLE DemoTable1459 ADD CONSTRAINT Name_const UNIQUE (Name);

Insert พร้อม ON DUPLICATE KEY UPDATE:

mysql> INSERT INTO DemoTable1459 VALUES ('Chris', 57) ON DUPLICATE KEY UPDATE Score = Score + 10;
Query OK, 1 row affected (0.12 sec)  -- Insert ใหม่

mysql> INSERT INTO DemoTable1459 VALUES ('David', 89) ON DUPLICATE KEY UPDATE Score = Score + 10;
Query OK, 1 row affected (0.11 sec)  -- Insert ใหม่

mysql> INSERT INTO DemoTable1459 VALUES ('Chris', 78) ON DUPLICATE KEY UPDATE Score = Score + 10;
Query OK, 2 rows affected (0.33 sec)  -- Duplicate! Update Score: 57+10=67

ดูผลลัพธ์:

mysql> SELECT * FROM DemoTable1459;
+-------+-------+
| Name  | Score |
+-------+-------+
| Chris |    67 |
| David |    89 |
+-------+-------+

ไวยากรณ์ Constraint

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_name);
  • CONSTRAINT name: ชื่อ Constraint (Optional แต่แนะนำให้ตั้ง)
  • UNIQUE (col): สร้าง Unique Index

ประเภท Key ที่รองรับ ON DUPLICATE KEY UPDATE

  • PRIMARY KEY
  • UNIQUE INDEX / UNIQUE CONSTRAINT
  • Unique Key ที่สร้างด้วย CREATE UNIQUE INDEX

คำสั่งที่เกี่ยวข้อง

-- ดู Constraints
SHOW CREATE TABLE DemoTable1459;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'DemoTable1459';

-- ลบ Constraint
ALTER TABLE DemoTable1459 DROP INDEX Name_const;  -- MySQL ใช้ DROP INDEX สำหรับ UNIQUE

-- สร้าง Unique Index โดยตรง (ไม่ต้อง CONSTRAINT)
CREATE UNIQUE INDEX idx_name ON DemoTable1459 (Name);

กรณีการใช้งานจริง (Upsert Pattern)

-- นับ Page Views
INSERT INTO page_views (page_url, view_count, last_viewed)
VALUES ('/home', 1, NOW())
ON DUPLICATE KEY UPDATE view_count = view_count + 1, last_viewed = NOW();

-- Upsert User Profile
INSERT INTO users (email, name, updated_at)
VALUES ('john@example.com', 'John', NOW())
ON DUPLICATE KEY UPDATE name = 'John', updated_at = NOW();

ข้อควรระวัง

  • VALUES() ใน UPDATE clause อ้างถึงค่าที่ INSERT เข้าไป (เช่น VALUES(Score) = 78)
  • Rows Affected: 1 = Insert ใหม่, 2 = Update มีการเปลี่ยนแปลง, 0 = Duplicate แต่ค่าเหมือนเดิม
  • Race Condition: ใน High Concurrency อาจเกิด Deadlock → ใช้ Transaction + Locking หรือ INSERT IGNORE + UPDATE แยก