เพิ่มค่าแบบ Incremental ใน MySQL UPDATE ใช้ User Variable

หลักการ UPDATE แบบ Incremental

ใช้ User Variable (@var) เก็บค่าฐาน แล้วอัปเดตคอลัมน์พร้อมเพิ่มค่าสะสม:

ขั้นตอน

1. SET @incrementValue := initial_value;
2. UPDATE table SET col = (@incrementValue := @incrementValue + @incrementValue);

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

สร้างตารางและข้อมูล

CREATE TABLE UpdateValueIncrementally (ProductId INT);
INSERT INTO UpdateValueIncrementally VALUES (10), (100), (1000), (110), (102);

ข้อมูลเดิม

+-----------+
| ProductId |
+-----------+
| 10        |
| 100       |
| 1000      |
| 110       |
| 102       |
+-----------+

ตั้งค่า Variable และ UPDATE

SET @incrementValue := 33333;

UPDATE UpdateValueIncrementally 
SET ProductId = (SELECT @incrementValue := @incrementValue + @incrementValue);

ผลลัพธ์ (คูณ 2 ทุกแถว: 33333→66666→133332→...)

+-----------+
| ProductId |
+-----------+
| 66666     |
| 133332    |
| 266664    |
| 533328    |
| 1066656   |
+-----------+

รูปแบบ Incremental อื่นๆ

เพิ่มค่าคงที่ (Running Number)

SET @rn := 0;
UPDATE your_table SET sort_order = (@rn := @rn + 1) ORDER BY created_at;

เพิ่มค่าจากคอลัมน์อื่น

SET @sum := 0;
UPDATE sales SET running_total = (@sum := @sum + amount) ORDER BY sale_date;

⚠️ ข้อจำกัดและคำเตือนสำคัญ

• ลำดับการอัปเดตไม่ได้รับประกัน หากไม่มี ORDER BY ใน UPDATE (MySQL ไม่รองรับ ORDER BY ใน UPDATE โดยตรง)
• Replication Unsafe Statement-based Replication อาจให้ผลต่างบน Slave
• ไม่รองรับใน Prepared Statement บาง Version
• MySQL 8.0+: ใช้ Window Function แทน (แนะนำ)

วิธีแทนที่สมัยใหม่ (MySQL 8.0+)

ใช้ CTE + Window Function สำหรับ SELECT

WITH numbered AS (
    SELECT Id, ROW_NUMBER() OVER (ORDER BY Id) AS rn
    FROM your_table
)
UPDATE your_table t
JOIN numbered n ON t.Id = n.Id
SET t.sort_order = n.rn;

หรือใช้ AUTO_INCREMENT ใน Temp Table

CREATE TEMPORARY TABLE temp_ids (id INT AUTO_INCREMENT PRIMARY KEY, orig_id INT);
INSERT INTO temp_ids (orig_id) SELECT Id FROM your_table ORDER BY Id;
UPDATE your_table t JOIN temp_ids ti ON t.Id = ti.orig_id SET t.sort_order = ti.id;