Which ways is better that insert data to two different table simultaneously in Mysql?-CodePudding

I'm trying to insert data to two different table simultaneously. If failed , then roll back the data. And I try to google, and found some solutions:

1.Use data transactions. 2.Create a extra table then join two table. 3.Create a view .

Consider about the web server performance, which one is better?

CodePudding user response：

Use must use your first solution: a transaction. That is the only option. You can do two consecutive INSERT statements in a transaction.

There is no syntax in SQL to support INSERT to two tables in the same statement.

You cannot INSERT to a view that is based on a join. Such views are not writeable.

CodePudding user response：

You shouldn't do them at the same time, just do one at a time. Even if you made 2 connections and tried to time 2 inserts, MySQL would still make sure to only make 1 change at a time.

You should just obtain 1 connection, start a transaction, do 1 insert at a time and then commit.

If either of the inserts fail, both will be reverted.

CodePudding user response：

You can use a transaction:

A Transactions are group of SQL statement that are treated as single unit. on error it will roll back all SQL statement, to the point where it was started.

Example:

START TRANSACTION;
--SQL Statement 1
INSERT INTO table1 (column1, column2, column3) VALUES (value1, value2, value3);

--you can write here another INSERT statement
--SQL Statement 2
UPDATE table2 SET column=column_value WHERE column=value;

COMMIT;