วิธีเรียงลำดับ Key ใหม่หลังลบแถวออกจากตาราง MySQL

ปัญหา: หลัง DELETE แถวกลาง ID จะมีช่องว่าง

เมื่อลบแถวที่มี ID กลาง (เช่น ลบ ID=30 จาก 10,20,30,40) จะเหลือ ID: 10, 20, 40 ซึ่งมีช่องว่าง หากต้องการเรียงใหม่เป็น 10, 20, 30 ต้องใช้ UPDATE พร้อมคำนวณค่าใหม่

ตัวอย่าง完整

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

mysql> CREATE TABLE DemoTable1476 (Id INT);
Query OK, 0 rows affected (0.81 sec)

mysql> INSERT INTO DemoTable1476 VALUES(10), (20), (30), (40);
Query OK, 4 rows affected

2. ข้อมูลก่อนลบ

+------+
| Id   |
+------+
|   10 |
|   20 |
|   30 |
|   40 |
+------+

3. ลบแถวที่ Id = 30

mysql> DELETE FROM DemoTable1476 WHERE Id = 30;
Query OK, 1 row affected (0.16 sec)

4. ข้อมูลหลังลบ (มีช่องว่าง)

+------+
| Id   |
+------+
|   10 |
|   20 |
|   40 |
+------+

5. อัปเดต ID ใหม่: ลดค่าลง 10 สำหรับ ID ที่มากกว่า 30

mysql> UPDATE DemoTable1476 SET Id = Id - 10 WHERE Id > 30;
Query OK, 1 row affected (0.14 sec)
Rows matched: 1  Changed: 1  Warnings: 0

6. ข้อมูลหลังเรียงใหม่

+------+
| Id   |
+------+
|   10 |
|   20 |
|   30 |
+------+

⚠️ ข้อควรระวังสำคัญ

ไม่แนะนำให้ทำกับ Primary Key หรือ Foreign Key จริง

Primary Key ควรคงที่ตลอดชีวิตของแถว (Immutable)
หากมีตารางอื่นอ้างอิง (Foreign Key) จะทำให้ข้อมูลเสียหาย
ควรใช้ AUTO_INCREMENT และยอมรับช่องว่าง (Gaps) เป็นเรื่องปกติ
วิธีนี้เหมาะเฉพาะคอลัมน์ลำดับที่ไม่ใช่ PK (เช่น display_order, sort_order)

แนวทางที่ดีกว่า

ใช้คอลัมน์แยกสำหรับลำดับแสดงผล (เช่น sort_order INT)
เมื่อลบแถว ให้อัปเดต sort_order แทน Primary Key
หรือใช้คำสั่ง ROW_NUMBER() ใน SELECT เมื่อต้องการแสดงลำดับ