โลกของ MySQL และ MariaDB

การใช้ UPSERT ใน MySQL: วิธีทำงาน ตัวอย่าง และข้อดีที่ควรรู้

พื้นฐานของ SQL
目次

1. UPSERT คืออะไร?

ภาพรวม

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

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

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

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

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

  • หลีกเลี่ยงข้อผิดพลาดจากคีย์ซ้ำ
  • ทำให้คำสั่ง SQL สั้นและเข้าใจง่ายขึ้น
  • รักษาความถูกต้องของข้อมูล

2. วิธีใช้ UPSERT พื้นฐานใน MySQL

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

โครงสร้างพื้นฐาน

INSERT INTO ชื่อตาราง (คอลัมน์1, คอลัมน์2)
VALUES (ค่า1, ค่า2)
ON DUPLICATE KEY UPDATE
คอลัมน์1 = ค่า1, คอลัมน์2 = ค่า2;

คำอธิบาย:

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

ตัวอย่าง:

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

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

3. รายละเอียดและตัวอย่างของคำสั่ง 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

ตัวอย่างใน PostgreSQL

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

ใน 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);

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

UPSERT ด้วย Stored Procedure

สามารถใช้ Stored Procedure เพื่อทำให้กระบวนการ UPSERT มีประสิทธิภาพมากขึ้น ซึ่งช่วยเพิ่มความสามารถในการนำกลับมาใช้ซ้ำ และทำให้โค้ดอ่านง่ายและบำรุงรักษาได้ดีขึ้น

6. ข้อควรระวังและปัญหาที่อาจเกิดขึ้นของ UPSERT

Transaction และ Deadlock

เมื่อใช้ UPSERT กับข้อมูลจำนวนมาก อาจทำให้เกิด Deadlock ได้ โดยเฉพาะเมื่อระดับการแยกธุรกรรม (Transaction Isolation Level) ของ MySQL ถูกตั้งเป็น REPEATABLE READ ซึ่งมีความเสี่ยงต่อการเกิด Gap Lock

วิธีหลีกเลี่ยง Gap Lock

  • เปลี่ยนระดับการแยกธุรกรรมเป็น READ COMMITTED เพื่อช่วยลดโอกาสเกิด Deadlock
  • ถ้าจำเป็น ควรแยก UPSERT ออกเป็นหลายคำสั่งเพื่อจัดการเป็นลำดับ

7. สรุป

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