ลบแถวและเรียงลำดับ ID ใหม่ใน MySQL (Reorder IDs after DELETE)

สถานการณ์

หลังลบแถว ID ไม่ต่อเนื่อง (gap) ต้องการ re-number ให้ต่อเนื่องอีกครั้ง

คำเตือนสำคัญ

ตัวอย่าง: Reorder แบบง่าย (ไม่มี FK)

1. สร้างตาราง

mysql> create table ReorderSortDemo 
    -> ( 
    -> UserId int 
    -> ); 
Query OK, 0 rows affected (0.57 sec)

2. เพิ่มข้อมูล ID ไม่เรียง

mysql> insert into ReorderSortDemo values(14); 
Query OK, 1 row affected (0.13 sec)
mysql> insert into ReorderSortDemo values(4); 
Query OK, 1 row affected (0.10 sec)
mysql> insert into ReorderSortDemo values(6); 
Query OK, 1 row affected (0.11 sec)
mysql> insert into ReorderSortDemo values(3); 
Query OK, 1 row affected (0.09 sec)
mysql> insert into ReorderSortDemo values(8); 
Query OK, 1 row affected (0.11 sec)
mysql> insert into ReorderSortDemo values(18); 
Query OK, 1 row affected (0.08 sec)
mysql> insert into ReorderSortDemo values(1); 
Query OK, 1 row affected (0.12 sec)
mysql> insert into ReorderSortDemo values(11); 
Query OK, 1 row affected (0.08 sec)
mysql> insert into ReorderSortDemo values(16); 
Query OK, 1 row affected (0.09 sec)

3. ดูข้อมูลก่อน

mysql> select * from ReorderSortDemo;

+--------+
| UserId |
+--------+
| 14     |
| 4      |
| 6      |
| 3      |
| 8      |
| 18     |
| 1      |
| 11     |
| 16     |
+--------+

4. ลบ UserId = 8

mysql> delete from ReorderSortDemo where UserId = 8; 
Query OK, 1 row affected (0.20 sec)

5. ดูหลังลบ (เหลือ 8 แถว)

mysql> select * from ReorderSortDemo;

+--------+
| UserId |
+--------+
| 14     |
| 4      |
| 6      |
| 3      |
| 18     |
| 1      |
| 11     |
| 16     |
+--------+

6. Reorder: ลบ 1 จาก ID ที่ > 8

mysql> update ReorderSortDemo 
    -> set UserId = UserId - 1 
    -> where UserId > 8; 
Query OK, 4 rows affected (0.22 sec) Rows matched: 4 Changed: 4 Warnings: 0

7. ผลลัพธ์สุดท้าย

mysql> select * from ReorderSortDemo;

+--------+
| UserId |
+--------+
| 13     |
| 4      |
| 6      |
| 3      |
| 17     |
| 1      |
| 10     |
| 15     |
+--------+

วิธีที่ดีกว่า: ใช้ ROW_NUMBER() (MySQL 8.0+)

-- สร้างตารางใหม่พร้อม ID ใหม่
CREATE TABLE new_table AS
SELECT ROW_NUMBER() OVER (ORDER BY UserId) AS new_id, col2, col3...
FROM old_table;

-- แล้ว rename table

Best Practice: ไม่ควร Reorder PK

สรุป: สามารถ reorder ได้ด้วย UPDATE ... WHERE id > deleted_id แต่ไม่แนะนำสำหรับ Production PK