ใช้ Stored Procedure INSERT เข้า 2 ตารางพร้อมกันใน MySQL

MySQL ไม่มีคำสั่ง INSERT หลายตารางใน Statement เดียว ต้องใช้ Stored Procedure ห่อหุ้ม Transaction เพื่อความเป็น Atomic

สร้างตาราง 2 ตาราง

mysql> create table DemoTable (
    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    StudentFirstName varchar(20)
);

mysql> create table DemoTable2 (
    ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ClientName varchar(20),
    ClientAge int
);

สร้าง Stored Procedure พร้อม Transaction

mysql> DELIMITER //
mysql> CREATE PROCEDURE insert_into_twoTables(name varchar(100), age int)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    INSERT INTO DemoTable(StudentFirstName) VALUES(name);
    INSERT INTO DemoTable2(ClientName, ClientAge) VALUES(name, age);
    COMMIT;
END //
mysql> DELIMITER ;

เรียกใช้ Procedure

mysql> call insert_into_twoTables('Tom', 38);

ตรวจสอบข้อมูลทั้ง 2 ตาราง

mysql> select * from DemoTable;
+-----------+------------------+
| StudentId | StudentFirstName |
+-----------+------------------+
| 1         | Tom              |
+-----------+------------------+

mysql> select * from DemoTable2;
+----------+------------+-----------+
| ClientId | ClientName | ClientAge |
+----------+------------+-----------+
| 1        | Tom        | 38        |
+----------+------------+-----------+

โครงสร้าง Procedure

• DELIMITER // - เปลี่ยน Delimiter ชั่วคราว
• CREATE PROCEDURE name(params) BEGIN ... END
• DECLARE EXIT HANDLER - จับ Exception Rollback
• START TRANSACTION ... COMMIT - Atomicity
• DELIMITER ; - คืน Delimiter

ดู Procedure ที่สร้าง

SHOW CREATE PROCEDURE insert_into_twoTables;

ลบ Procedure

DROP PROCEDURE IF EXISTS insert_into_twoTables;

ทางเลือกอื่น

Best Practice

• Application Layer จัดการ Transaction เอง (Modern Approach)
• Stored Procedure เหมาะสำหรับ: Batch Job, ETL, Legacy System
• หลีกเลี่ยง Business Logic ซับซ้อนใน DB