Need is such, there are a few data, a, b, c, a need to put the data inserted into the table 1, and then take to automatically generate the id of the table 1, together with the b is inserted into a table 2; Then id is inserted into a table with c 3,
I points three statements written, there is a problem, sometimes the first mysql performed, but not the second implementation success, thus leading to the lack of data,

Want to, so if we can put the three statements to synthesize 1, and then submit together, success is successful, the failure is together

If you can, how do you write this statement?

CodePudding user response:

1, use the stored procedure that is punctuated with transactions, these three operations in a transaction, success is success, failure is failure,
2, use the last_insert_id get inserted into the generated on the id in the table, as the data into a table,

CodePudding user response:

reference 1st floor sinat_28984567 response:

1, use the stored procedure, which combined with the transaction, the three operations in a transaction, success is success, failure is failure,
2, use the last_insert_id get inserted into the generated on the id in the table, as the data into a table,

Can you help write an example? I am a novice, stored procedures, transactions do not understand,
Thank you very much!

CodePudding user response:

No validation

 
DROP PROCEDURE IF the EXISTS test_sp1 
CREATE PROCEDURE test_sp1 (IN str_a varchar (100), IN str_b varchar (100), IN str_c varchar (100)) 
The BEGIN 
DECLARE t_error INTEGER DEFAULT 0; 
DECLARE the CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; 

START TRANSACTION; 
INSERT INTO test1 VALUES (str_a); 
The SET Result_ID=LAST_INSERT_ID (); 
INSERT INTO test2 VALUES (str_b Result_ID); 
INSERT INTO test3 VALUES (str_c Result_ID); 

IF t_error=1 THEN 
ROLLBACK; 
The ELSE 
COMMIT; 
END the IF; 

The END; 

CALL test_sp1 (' aa ', 'bb', 'cc'); 

CodePudding user response:

Can understand the first database transactions, to understand myself more deeply

CodePudding user response:

reference 4 floor benluobobo response:

to know about the database transactions, understand myself more deeply

Thank you ~

CodePudding user response:

The first thing you see in your database table if
ENGINE=InnoDB
InnoDB only supports transactions, and then you ~ baidu PHP affairs