รีเซ็ต Primary Key เริ่มจาก 1 หลังลบข้อมูลทั้งหมดใน MySQL

คำสั่งหลัก

ใช้ ALTER TABLE ... AUTO_INCREMENT = 1 ตามด้วย TRUNCATE TABLE

รูปแบบคำสั่ง

ALTER TABLE yourTableName AUTO_INCREMENT = 1;
TRUNCATE TABLE yourTableName;

ตัวอย่างครบถ้วน

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

mysql> create table resettingPrimaryKeyDemo 
    -> ( 
    -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY 
    -> ); 
Query OK, 0 rows affected (0.66 sec)

2. เพิ่มข้อมูล 4 แถว

mysql> insert into resettingPrimaryKeyDemo values(); 
Query OK, 1 row affected (0.15 sec)
mysql> insert into resettingPrimaryKeyDemo values(); 
Query OK, 1 row affected (0.10 sec)
mysql> insert into resettingPrimaryKeyDemo values(); 
Query OK, 1 row affected (0.08 sec)
mysql> insert into resettingPrimaryKeyDemo values(); 
Query OK, 1 row affected (0.12 sec)

3. ดูข้อมูล (ID 1-4)

mysql> select * from resettingPrimaryKeyDemo;

+--------+
| UserId |
+--------+
| 1      |
| 2      |
| 3      |
| 4      |
+--------+

4. รีเซ็ต AUTO_INCREMENT แล้ว TRUNCATE

mysql> alter table resettingPrimaryKeyDemo AUTO_INCREMENT = 1; 
Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> truncate table resettingPrimaryKeyDemo; 
Query OK, 0 rows affected (0.89 sec)

5. ตรวจสอบ - ตารางว่าง

mysql> select * from resettingPrimaryKeyDemo; 
Empty set (0.00 sec)

6. Insert ใหม่ - ID เริ่มจาก 1 อีกครั้ง

mysql> insert into resettingPrimaryKeyDemo values(); 
Query OK, 1 row affected (0.12 sec)
mysql> insert into resettingPrimaryKeyDemo values(); 
Query OK, 1 row affected (0.10 sec)
mysql> insert into resettingPrimaryKeyDemo values(); 
Query OK, 1 row affected (0.10 sec)

mysql> select * from resettingPrimaryKeyDemo;

+--------+
| UserId |
+--------+
| 1      |
| 2      |
| 3      |
+--------+

ข้อแตกต่าง: DELETE vs TRUNCATE

กรณีมี Foreign Key อ้างอิง

TRUNCATE จะล้มเหลว ถ้ามี FK อ้างอิง ต้อง:

1. DROP FK constraints
2. TRUNCATE
3. CREATE FK constraints กลับ

หรือใช้ DELETE FROM table; ALTER TABLE table AUTO_INCREMENT = 1; (ช้ากว่า แต่ปลอดภัยกว่า)

แนะนำ: ใน Production ควรไม่อาศัย AUTO_INCREMENT sequence เป็น business logic ให้มันเป็น surrogate key เท่านั้น