How Can I Insert Data into Multiple Tables in MySQL Using a Single Query?

Efficiently Inserting Data Across Multiple MySQL Tables

Database management often requires inserting data into multiple tables simultaneously. While MySQL doesn't directly support multi-table inserts in a single query, transactions provide a robust solution. Transactions bundle multiple database operations, ensuring either complete success or complete failure, maintaining data integrity.

This approach uses transactions to insert data into multiple tables reliably:

1. Begin the transaction: Use START TRANSACTION; to initiate a transaction block.
2. Execute INSERT statements: Within the transaction, execute INSERT statements for each table.
3. Commit or rollback: Use COMMIT; to save all changes if all inserts succeed. If any error occurs during the process, use ROLLBACK; to undo all changes within the transaction, preserving data consistency.

Here's an example illustrating a transaction inserting data into "names" and "phones" tables:

<code class="language-sql">START TRANSACTION;
INSERT INTO names VALUES ('1','2','3');
INSERT INTO phones VALUES ('bob','smith');
COMMIT;</code>

This method guarantees data consistency and enforces database constraints, preventing partial data updates and maintaining database integrity.

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