如何将Mysql一次INSERT到2个表中，其中tableA具有唯一的userID和tableB重复的userID

Question

I have the below queries which I'm iserting separately.

SQL1:
    INSERT INTO tableA (product) VALUES ('machine1'), ('machine2')

SQL2:
    INSERT INTO tableB (userid, name) VALUES (12345, 'Jan Smith'), (12345, 'Bert Smith'), (12346, 'Pieter King'), (12346, 'Martin King')

tableA has a unique primary key (autoincrement) on userID. tableB also has a column userID where userID is not unique.

Currently I use mysqli last inserted ID to obtain the first userID of the first inserted product from SQL1 which is machine1 in this case. In this case the number 12345 will be returned and will be used for SQL2.

I'm currently fetching the first userID and use it for the second SQL insertion while simply adding +1 for each next product assuming that the userID as given to the rows for SQL1 are all consecutive. As in the mean time there are already other insertions this approach is not valid and does not work. Further to that it is a dangeroes approach as the system breaks if any of the two SQL queries fail.

My question is: "How can I combine the above two INSERTION queries into 1?"

Answer 1

http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-functionality-last-insert-id.html

INSERT INTO tbl (auto,text) VALUES(NULL,'text'); INSERT INTO tbl2 (id,text) VALUES(LAST_INSERT_ID(),'text');

You can put both insert in the same transaction.