How to Insert Data into Multiple Tables Simultaneously in MySQL?

Inserting into Multiple Tables in MySQL

In this scenario, the goal is to insert data into two tables simultaneously: visits and registration. While a single INSERT statement can typically insert data into one table, it's not feasible in this case.

To address this challenge, there are two viable options:

1. Batch Insertion:

Break down the insertion into two distinct INSERT statements and execute them as a batch. This approach involves using the EXECUTE command as follows:

START TRANSACTION;
INSERT INTO visits (visit_id, card_id) VALUES (NULL, 12131141);
INSERT INTO registration (registration_id, type, timestamp, visit_id) 
VALUES (NULL, 'in', UNIX_TIMESTAMP(), LAST_INSERT_ID());
COMMIT;

2. Stored Procedure:

Create a stored procedure that encapsulates the two INSERT statements. This allows you to execute both insertions with a single procedure call. Consider the following example:

CREATE PROCEDURE insert_into_multiple_tables(
    IN visit_card_id INT,
    IN registration_type ENUM('in', 'out')
)
BEGIN
    # Insert into `visits` table
    INSERT INTO visits (visit_id, card_id) VALUES (NULL, visit_card_id);
    
    # Insert into `registration` table
    INSERT INTO registration (registration_id, type, timestamp, visit_id) 
    VALUES (NULL, registration_type, UNIX_TIMESTAMP(), LAST_INSERT_ID());
END;

To invoke the stored procedure, use the following syntax:

CALL insert_into_multiple_tables(12131141, 'in');

Both the batch insertion and stored procedure methods provide reliable ways to insert data into multiple tables. The choice between the two depends on the specific application requirements and performance considerations.

The above is the detailed content of How to Insert Data into Multiple Tables Simultaneously in MySQL?. For more information, please follow other related articles on the PHP Chinese website!