ตั้งค่า Auto Increment ค่าต่ำกว่าเดิมใน MySQL (ต้องใช้ MyISAM)

ใน MySQL Engine InnoDB ไม่สามารถตั้งค่า AUTO_INCREMENT ต่ำกว่าค่าสูงสุดที่มีอยู่ ได้ ต้องเปลี่ยน Engine เป็น MyISAM จึงจะทำได้ (แต่ MyISAM ไม่รองรับ Transaction/Row Lock)

ข้อจำกัด InnoDB vs MyISAM

ตัวอย่าง: MyISAM Table

mysql> create table DemoTable (Id int NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE='MyISAM';

mysql> insert into DemoTable values(),(),(),(),(),(); -- 6 rows

+----+
| Id |
+----+
| 1  |
| 2  |
| 3  |
| 4  |
| 5  |
| 6  |
+----+

ลบ ID 4,5,6

mysql> delete from DemoTable where Id in (4,5,6);

+----+
| Id |
+----+
| 1  |
| 2  |
| 3  |
+----+

ตั้งค่า AUTO_INCREMENT = 4 (ต่ำกว่าเดิมที่เป็น 7)

mysql> alter table DemoTable auto_increment = 4;

INSERT ใหม่ 3 แถว

mysql> insert into DemoTable values(),(),();

ผลลัพธ์: ID ใหม่เริ่มจาก 4

+----+
| Id |
+----+
| 1  |
| 2  |
| 3  |
| 4  |
| 5  |
| 6  |
+----+

คำอธิบาย

• หลัง DELETE MAX(Id) = 3
• MyISAM: ALTER AUTO_INCREMENT=4 → ตั้งเป็น MAX+1 = 4
• InnoDB: ALTER AUTO_INCREMENT=4 → ไม่ทำอะไร (คงค่า AI เดิม = 7)

วิธีทำกับ InnoDB (Workaround)

1. TRUNCATE TABLE - รีเซ็ต AI เป็น 1 (ลบข้อมูลทั้งหมด)
2. Re-create Table - สร้างตารางใหม่ คัดลอกข้อมูล
3. ALTER TABLE ... AUTO_INCREMENT = value - ตั้งได้เฉพาะค่า > MAX(Id) ปัจจุบัน

คำเตือน MyISAM

• ไม่รองรับ Transaction (ACID)
• Table-level Lock (Concurrency ต่ำ)
• ไม่มี Foreign Key
• Crash Unsafe - อาจ Corrupt ได้
• ไม่แนะนำใช้ MyISAM สำหรับ Production

MySQL 8.0: ใช้ AUTO_INCREMENT ใน CREATE TABLE

CREATE TABLE new_table (id INT AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT = 100;

แปลง Integer เป็น VARCHAR ใน MySQL ไม่สูญเสียข้อมูลด้วย ALTER TABLE

แปลงสตริงเป็น Datetime รูปแบบ 24 ชั่วโมงใน MySQL ด้วย STR_TO_DATE