Practical MySQL & MariaDB

อธิบาย MySQL UPSERT: คู่มือ INSERT … ON DUPLICATE KEY UPDATE พร้อมตัวอย่าง

พื้นฐาน SQL
目次

1. UPSERT คืออะไร?

ภาพรวม

“UPSERT” หมายถึงคุณลักษณะของฐานข้อมูลที่รวมการทำงานของ “INSERT” และ “UPDATE” เข้าด้วยกัน กล่าวคือ หากข้อมูลยังไม่มีอยู่ จะทำการแทรก; หากข้อมูลเดียวกันมีอยู่แล้ว จะทำการอัปเดต การใช้คุณลักษณะนี้ช่วยให้ทำการดำเนินการได้อย่างมีประสิทธิภาพพร้อมคงความสอดคล้องของข้อมูล

ใน MySQL ฟังก์ชันนี้ถูกนำมาใช้ผ่านไวยากรณ์ INSERT ... ON DUPLICATE KEY UPDATE ซึ่งช่วยหลีกเลี่ยงข้อผิดพลาดคีย์ซ้ำและอัปเดตแถวที่มีอยู่แม้จะเกิดคีย์ซ้ำ

กรณีการใช้งาน

  • ระบบจัดการลูกค้า : เพิ่มข้อมูลลูกค้าใหม่หากไม่มีอยู่ และอัปเดตข้อมูลลูกค้าที่มีอยู่เมื่อมีการเปลี่ยนแปลง
  • ระบบจัดการสินค้าคงคลัง : เพิ่มสินค้ารายการใหม่พร้อมอัปเดตจำนวนสต็อกของสินค้าที่มีอยู่

ข้อดีของ UPSERT ใน MySQL

  • ป้องกันข้อผิดพลาดคีย์ซ้ำ
  • ทำให้คำสั่ง SQL ง่ายขึ้น
  • รักษาความสมบูรณ์ของข้อมูล

2.ใช้ UPSERT เบื้องต้นใน MySQL

ใน MySQL การทำ UPSERT จะทำผ่านไวยากรณ์ INSERT ... ON DUPLICATE KEY UPDATE หากเกิดคีย์ซ้ำ คุณสามารถอัปเดตบางส่วนหรือทั้งหมดของข้อมูลที่มีอยู่แทนการแทรกข้อมูลใหม่

ไวยากรณ์พื้นฐาน

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

คำอธิบาย:

  1. ใช้ INSERT INTO เพื่อแทรกข้อมูล
  2. หากข้อมูลที่กำลังจะแทรกมีอยู่แล้วในตาราง จะทำการประมวลผลคลอส ON DUPLICATE KEY UPDATE และอัปเดตข้อมูลที่มีอยู่

ตัวอย่าง:

INSERT INTO users (user_id, name)
VALUES (1, 'Taro Tanaka')
ON DUPLICATE KEY UPDATE
name = 'Taro Tanaka';

ในตัวอย่างข้างต้น หากมีผู้ใช้ที่มี user_id เท่ากับ 1 อยู่แล้ว ฟิลด์ name จะถูกอัปเดตเป็น ‘Taro Tanaka’ หากผู้ใช้ไม่มีอยู่ จะทำการแทรกแถวใหม่

3. ไวยากรณ์ SQL รายละเอียดและตัวอย่างของ UPSERT

การอัปเดตหลายคอลัมน์

เมื่อใช้ UPSERT บางครั้งคุณอาจต้องการอัปเดตเฉพาะคอลัมน์ที่ต้องการเท่านั้น ในกรณีนี้คุณสามารถระบุคอลัมน์ที่จำเป็นในคลอส ON DUPLICATE KEY UPDATE ได้

INSERT INTO products (product_id, name, price)
VALUES (100, 'Laptop', 50000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

ในตัวอย่างนี้ หากมีสินค้าโดยมี product_id เท่ากับ 100 อยู่แล้ว จะอัปเดตเฉพาะคอลัมน์ price เท่านั้น ส่วนคอลัมน์อื่น (เช่น name) จะคงค่าเดิมไว้

4. ความแตกต่างกับฐานข้อมูลอื่น

ฐานข้อมูลที่ไม่ใช่ MySQL ก็มีฟังก์ชันคล้ายกัน ตัวอย่างเช่น PostgreSQL และ SQLite ใช้ INSERT ... ON CONFLICT หรือคำสั่ง MERGE เพื่อให้ได้พฤติกรรมแบบ UPSERT

ตัวอย่าง PostgreSQL

INSERT INTO users (user_id, name)
VALUES (1, 'Taro Tanaka')
ON CONFLICT (user_id) DO UPDATE SET
name = 'Taro Tanaka';

ใน PostgreSQL และ SQLite คำสั่ง ON CONFLICT จะใช้ควบคุมพฤติกรรมเมื่อเกิดข้อผิดพลาดคีย์ซ้ำ ในขณะที่ MySQL ใช้คลอส ON DUPLICATE KEY UPDATE

ลักษณะเฉพาะของ MySQL

  • MySQL ใช้ INSERT ... ON DUPLICATE KEY UPDATE และเนื่องจากไวยากรณ์แตกต่างจากฐานข้อมูลอื่น จึงต้องระมัดระวังเป็นพิเศษเมื่อต้องย้ายข้อมูลระหว่างระบบ

5. เทคนิค UPSERT ขั้นสูง

Bulk UPSERT (การประมวลผลหลายแถวพร้อมกัน)

UPSERT สามารถทำได้ไม่เพียงแค่แถวเดียว แต่ยังสามารถทำกับหลายแถวพร้อมกัน ซึ่งช่วยเพิ่มประสิทธิภาพของการทำงานกับฐานข้อมูลอย่างมาก

INSERT INTO products (product_id, name, price)
VALUES
(100, 'Laptop', 50000),
(101, 'Smartphone', 30000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

ในตัวอย่างนี้ มีการแทรกหลายแถวของสินค้าพร้อมกัน หากมีคีย์ซ้ำ จะอัปเดตเฉพาะฟิลด์ price ของแถวที่ตรงกันเท่านั้น

การใช้ Stored Procedure สำหรับ UPSERT

To optimize UPSERT processing, you can also use stored procedures. This allows you to create reusable logic inside the database, improving both readability and maintainability of your code.

6. ข้อผิดพลาดทั่วไปและข้อพิจารณาที่สำคัญ

ธุรกรรมและ Deadlocks

When using UPSERT—especially with large volumes of data—deadlocks may occur. If MySQL’s transaction isolation level is set to REPEATABLE READ, gap locks are more likely to happen.

การหลีกเลี่ยง Gap Locks

  • You can reduce the likelihood of deadlocks by changing the transaction isolation level to READ COMMITTED .
  • If necessary, consider breaking a large UPSERT operation into smaller batches and executing multiple queries instead of one large statement.

7. สรุป

คุณสมบัติ UPSERT ของ MySQL มีประโยชน์อย่างยิ่งสำหรับการทำให้กระบวนการแทรกและอัปเดตข้อมูลเรียบง่ายขึ้น ในขณะที่หลีกเลี่ยงข้อผิดพลาดคีย์ซ้ำ อย่างไรก็ตาม การนำ UPSERT ไปใช้ต้องพิจารณาอย่างรอบคอบถึง deadlocks ที่อาจเกิดขึ้นและการตั้งค่าธุรกรรม เมื่อใช้อย่างถูกต้อง มันช่วยให้การดำเนินการฐานข้อมูลง่ายและมีประสิทธิภาพมากขึ้น