จัดเรียงและเพิ่มค่าลำดับใน MySQL

การจัดเรียงข้อมูลและกำหนดลำดับใหม่ (Re-sequence) ใน MySQL

ใช้ User-defined Variable ร่วมกับ UPDATE ... ORDER BY เพื่อคำนวณค่าใหม่แบบลำดับขณะอัปเดต

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

สร้างตารางและเพิ่มข้อมูล:

mysql> create table DemoTable (FirstName varchar(20), Position int);
mysql> insert into DemoTable values('Chris',100);
mysql> insert into DemoTable values('Robert',120);
mysql> insert into DemoTable values('David',130);

ข้อมูลต้นฉบับ:

+-----------+----------+
| FirstName | Position |
+-----------+----------+
| Chris     | 100      |
| Robert    | 120      |
| David     | 130      |
+-----------+----------+

คิวรีจัดเรียงตาม Position แล้วเพิ่มทีละ 10 เริ่มจาก 100:

mysql> SET @myPosition := 100;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE DemoTable SET Position = @myPosition := @myPosition + 10 ORDER BY Position;
Query OK, 1 row affected (0.17 sec)  -- แถวที่เปลี่ยนจริง 1 แถว (Chris 100->110)

ผลลัพธ์:

+-----------+----------+
| FirstName | Position |
+-----------+----------+
| Chris     | 110      |
| Robert    | 120      |
| David     | 130      |
+-----------+----------+

การทำงาน:

1. SET @myPosition := 100 - ตั้งค่าเริ่มต้น
2. ORDER BY Position - เรียง Chris(100), Robert(120), David(130)
3. แถว 1 (Chris): @myPosition := 100 + 10 = 110 → Position = 110
4. แถว 2 (Robert): @myPosition := 110 + 10 = 120 → Position = 120 (ค่าเดิม)
5. แถว 3 (David): @myPosition := 120 + 10 = 130 → Position = 130 (ค่าเดิม)

ข้อควรระวัง: UPDATE ... ORDER BY ไม่รองรับในทุก Storage Engine/Version | MySQL 8.0+ แนะนำใช้ Window Function: ROW_NUMBER() OVER (ORDER BY Position) แทน