Retrieving all LAST_INSERT_ID of MySQL batch INSERT ... ON DUPLICATE UPDATE ...?

Question

Current rows in t1:

id,f1,f2,f3
1,'01','02','03'
2,'11','12','13'
4,'41','42','43'

Wherein f1 is a unique key field.

Now we perform this query:

INSERT INTO `t1` (`f1`, `f2`, `f3`) VALUES 
('11', '12', '13'),
('21', '22', '23'),
('31', '32', '33'),
('41', '42', '43'),
('51', '52', '53')
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)

How do I get the last insert IDs of all the rows inserted/updated?

For the example above, I believe the IDs would be:

2 5 6 4 7

Right?

But how do I get them from MySQL after a batch INSERT query with ON DUPLICATE KEY UPDATE clause like this?

From this answer, LAST_INSERT_ID "gives you the id of the FIRST row inserted in the last batch" and one can simply get all the rest IDs in a sequential manner if the rows are all freshly INSERT-ed.

Apparently, when we have ON DUPLICATE KEY UPDATE, it's a totally different story. So is there any way to get all the last insert IDs of a batch INSERT with ON DUPLICATE KEY UPDATE? Tried google but found nothing helpful.

Why do I need this

After inserting in t1, I need to associate the rows just inserted with one of the rows in t2. It's a many-to-many relationship so I would need to insert in t1_x_t2 with the ID of the row from t2 and all the IDs just inserted in t1.

Is there any other way to achieve this?

Answer 1

You can use triggers in MySql for this purpose on events AFTER INSERT and AFTER UPDATE. In trigger body you will get the old and new values that you can use to insert, update or delete in another table. MySQL doc link: https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html