How Can I Insert Data into Multiple Tables Simultaneously in MySQL?

Inserting Data into Multiple Tables Concurrently

Inserting data into two tables simultaneously can be achieved in MySQL, although it requires a different approach compared to the provided SQL statement.

The provided query attempts to insert data into both the visits and registration tables in a single operation. However, this is not directly supported by MySQL as INSERT statements can only target one table at a time. To achieve the desired result, you have the following options:

Batch Processing

Execute two separate INSERT queries as a batch:

BEGIN;

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;

Stored Procedure

Create a stored procedure that encapsulates both insert operations:

CREATE PROCEDURE insert_into_tables(
    IN card_id INT,
    IN type ENUM('in', 'out'),
    IN timestamp INT
)
BEGIN
    DECLARE visit_id INT;

    INSERT INTO visits (card_id) VALUES (card_id);
    SET visit_id = LAST_INSERT_ID();

    INSERT INTO registration (registration_id, type, timestamp, visit_id)
    VALUES (NULL, type, timestamp, visit_id);
END;

You can then invoke the stored procedure with the desired parameters:

CALL insert_into_tables(12131141, 'in', UNIX_TIMESTAMP());

Transaction Management

To ensure that both insert operations are performed atomically, wrap them within a transaction:

BEGIN 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;

By utilizing these approaches, you can insert data into multiple tables concurrently in MySQL.

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