MySQL: INSERT ด้วย MAX() + 1 ใช้ COALESCE ป้องกัน NULL

เมื่อต้องการสร้างค่า ID ใหม่โดยอัตโนมัติจากค่าสูงสุดที่มีอยู่ (MAX + 1) โดยไม่ใช้ AUTO_INCREMENT สามารถใช้ COALESCE() ร่วมกับ Subquery ได้

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

INSERT INTO table_name (id_column, name_column)
SELECT 1 + COALESCE((SELECT MAX(id_column) FROM table_name WHERE name_column = 'value'), 0), 'value';

คำอธิบาย:

• SELECT MAX(id_column) ... WHERE name = 'value' - หา ID สูงสุดของ name นั้น
• COALESCE(..., 0) - หากไม่มีแถว (NULL) ใช้ 0 แทน
• 1 + ... - บวก 1 เพื่อได้ ID ใหม่

ตัวอย่างการใช้งาน

1. สร้างตารางและเพิ่มข้อมูล (มี John หลายแถว ID 1,2,3)

CREATE TABLE InsertMaxPlus1Demo (
    Id INT,
    Name VARCHAR(20)
);

INSERT INTO InsertMaxPlus1Demo (Id, Name) VALUES
(1, 'John'), (1, 'Mike'), (2, 'John'),
(1, 'Larry'), (3, 'John'), (2, 'David');

ข้อมูลในตาราง:

+------+-------+
| Id   | Name  |
+------+-------+
|    1 | John  |
|    1 | Mike  |
|    2 | John  |
|    1 | Larry |
|    3 | John  |
|    2 | David |
+------+-------+

2. INSERT John ใหม่ด้วย MAX(Id) + 1 ของ John

INSERT INTO InsertMaxPlus1Demo (Id, Name)
SELECT 1 + COALESCE((SELECT MAX(Id) FROM InsertMaxPlus1Demo WHERE Name = 'John'), 0), 'John';

ผลลัพธ์: Query OK, 1 row affected

John มี MAX(Id) = 3 → ใหม่จะได้ Id = 4

3. ตรวจสอบข้อมูลหลังเพิ่ม

SELECT * FROM InsertMaxPlus1Demo;

+------+-------+
| Id   | Name  |
+------+-------+
|    1 | John  |
|    1 | Mike  |
|    2 | John  |
|    1 | Larry |
|    3 | John  |
|    2 | David |
|    4 | John  |  -- เพิ่งเพิ่ม
+------+-------+

กรณีใช้กับ AUTO_INCREMENT (ไม่แนะนำทำเอง)

หากตารางมี AUTO_INCREMENT ให้ปล่อยให้ MySQL จัดการเอง:

CREATE TABLE auto_demo (
    Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(20)
);

INSERT INTO auto_demo (Name) VALUES ('John');  -- Id = 1
INSERT INTO auto_demo (Name) VALUES ('John');  -- Id = 2 (อัตโนมัติ)

ข้อควรระวัง: Race Condition

วิธี MAX() + 1 มีปัญหา Race Condition ในระบบ Multi-user:

1. User A อ่าน MAX = 10
2. User B อ่าน MAX = 10
3. ทั้งคู่ INSERT Id = 11 → Duplicate Key Error

วิธีแก้: ใช้ Transaction + Lock

START TRANSACTION;
SELECT MAX(Id) INTO @max_id FROM table_name WHERE Name = 'John' FOR UPDATE;
INSERT INTO table_name (Id, Name) VALUES (@max_id + 1, 'John');
COMMIT;

หรือใช้ INSERT ... ON DUPLICATE KEY UPDATE หากมี Unique Key

สรุป

• ใช้ COALESCE(MAX(...), 0) + 1 สำหรับ Single-user หรือ Batch job
• ใช้ AUTO_INCREMENT สำหรับ Production/Multi-user
• ใช้ SEQUENCE (MySQL 8.0+) สำหรับความต้องการขั้นสูง