How to Insert Data into Multiple Tables in MySQL?

Inserting Data into Multiple Tables with MySQL

Inserting data simultaneously into multiple tables in MySQL is not feasible using a single query. However, there are alternative approaches to achieve this:

Batch Processing

You can execute multiple INSERT statements 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

Alternatively, you can create a stored procedure that encapsulates both INSERT statements:

CREATE PROCEDURE insert_into_multiple_tables(
  IN card_id INT
)
BEGIN
  INSERT INTO visits (visit_id, card_id) VALUES (NULL, card_id);
  INSERT INTO registration (registration_id, type, timestamp, visit_id)
  VALUES (NULL, 'in', UNIX_TIMESTAMP(), LAST_INSERT_ID());
END

You can then execute the stored procedure with the desired card ID:

CALL insert_into_multiple_tables(12131141);

Transactional Control

To ensure the integrity of the data, you can wrap the batch or stored procedure in a transaction:

BEGIN TRANSACTION;
-- Execute INSERT statements here
COMMIT;

If any of the INSERT statements fails, the transaction will be rolled back, ensuring that the data in all affected tables remains consistent.

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