แก้ไข Auto Increment หลังลบแถว ให้เรียงต่อ 1,2,3 ใน MySQL

การรีเซ็ตลำดับ Auto Increment หลังลบแถว

เมื่อลบแถวทำให้ ID ข้ามกัน (เช่น 1,3,5) สามารถอัปเดตค่า ID ใหม่เรียงต่อกันได้ด้วยตัวแปรผู้ใช้ (User-defined variable)

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

mysql> CREATE TABLE DemoTable1955 (
    -> UserId INT NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY(UserId)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO DemoTable1955 VALUES();
mysql> INSERT INTO DemoTable1955 VALUES();
mysql> INSERT INTO DemoTable1955 VALUES();
mysql> INSERT INTO DemoTable1955 VALUES();
mysql> INSERT INTO DemoTable1955 VALUES();

ข้อมูลเริ่มต้น: 1,2,3,4,5

ลบแถว ID 2 และ 4

mysql> DELETE FROM DemoTable1955 WHERE UserId IN (2,4);
Query OK, 2 rows affected (0.00 sec)

เหลือ ID: 1,3,5

คำสั่ง UPDATE เพื่อเรียงใหม่

mysql> UPDATE DemoTable1955
    -> SET UserId = (@increment_value := @increment_value + 1)
    -> ORDER BY UserId;
Query OK, 2 rows affected (0.00 sec)

ตรวจสอบผลลัพธ์

mysql> SELECT * FROM DemoTable1955;
+--------+
| UserId |
+--------+
| 1      |
| 2      |
| 3      |
+--------+

คำอธิบาย: ตัวแปร @increment_value เริ่มต้นเป็น NULL (เท่ากับ 0) เพิ่มทีละ 1 ตามลำดับ ORDER BY UserId ทำให้ ID ใหม่เรียง 1,2,3 คำเตือน: วิธีนี้เปลี่ยนค่า Primary Key อาจส่งผลต่อ Foreign Key ในตารางอื่น