How to Efficiently Insert Data into Multiple MySQL Tables in a Single Transaction?

MySQL Insert into Multiple Tables

Attempting to insert data into multiple tables with a single MySQL query may yield unexpected results. While it may seem like multiple queries would resolve the issue, correlating the auto-increment ID from the user table to the manual user ID for the profile table presents a challenge.

Using Transactions and LAST_INSERT_ID()

To insert into multiple tables in a single transaction, use the following approach:

BEGIN;
INSERT INTO users (username, password) VALUES('test', 'test');
INSERT INTO profiles (userid, bio, homepage) VALUES(LAST_INSERT_ID(),'Hello world!', 'http://www.stackoverflow.com');
COMMIT;

• Begin a transaction with "BEGIN;".
• Insert into the first table, capturing the auto-increment ID using "LAST_INSERT_ID()".
• Insert into the second table, referencing the captured ID as "userid".
• Commit the transaction with "COMMIT;".

Storing the Auto-Increment ID in a Variable

Alternatively, storing the auto-increment ID in a variable allows for referencing it in subsequent queries. This can be achieved using:

• MySQL variable:

  INSERT INTO ...
  SELECT LAST_INSERT_ID() INTO @mysql_variable_here;
  INSERT INTO table2 (@mysql_variable_here, ...);

  Copy after login

• PHP variable:

  INSERT ...
  $result = mysql_query("SELECT LAST_INSERT_ID()");
  $id = mysql_result($result, 0);
  INSERT INTO table2 ($id, ...);

  Copy after login

Transaction Considerations

Regardless of the approach chosen, consider the potential impact of interrupted execution. In the absence of transactions, inconsistency may arise in your database. To prevent this, wrap the insert statements in a transaction, as demonstrated above.

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