10

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?

pltc
  • 5,836
  • 1
  • 13
  • 31
datasn.io
  • 12,564
  • 28
  • 113
  • 154
  • 4
    So don't do batch inserts, it's that simple. Use prepared statements and run queries one by one, you can get last insert id easily then. Even if you manage to hack your way through this silly batch insert, a prepared statement will win in every possible field - performance, readability, robustness. – N.B. Jul 22 '15 at 10:15
  • @N.B. I guess what I'm asking is not possible with a batch INSERT then? This is more of an offline analysis job so the number of rows are tremendous and keep on coming. I tried prepared statements, but after some benchmarking, it's a lot slower to prepare a statement and run 100 times than just running a query 1 time. I would prefer the batch query if possible. – datasn.io Jul 22 '15 at 10:21
  • It is not slower to do that. Also, your batch insert can easily exceed `max_allowed_packet` variable and your entire request will go down the drain. Wrap your statements with a transaction block and you will get the same, if not better, result as with a batch insert. If you tested prepared statement **without** transaction block then yes, it will be slower because each query will be its own isolated transaction and will waste 1 I/O per query. – N.B. Jul 22 '15 at 10:26
  • @N.B. Ok, I'll try the prepared statements wrapped in a transaction block and let you guys know. – datasn.io Jul 22 '15 at 10:29
  • @kavoir - Read the highlighted text in the select answer on that other question, it says you only have access to the first ID. – Adrian Lynch Jul 22 '15 at 11:15
  • @N.B. After some [benchmarking](http://www.kavoir.com/backyard/showthread.php?210-INSERT-Performance-100-Stmt-Separately-vs-100-Stmt-in-Transaction-vs-Batch-INSERT) on my desktop computer (i5, 4G, SSD, WAMP w/ PHP5.4 & MySQL 5.5), it seems prepared statements wrapped in a transaction block wins when the number of rows to be inserted are less than 250. When the number of rows become larger than 250, single batch INSERT wins. I think I'll go with your approach. Thank you! – datasn.io Jul 22 '15 at 16:05
  • You found your sweet spot easily, I'm glad it worked for you :) – N.B. Jul 23 '15 at 07:30
  • make f1 as primary key and auto increment to it instead of the above insert stmt – Vijayabaskaran M Aug 28 '15 at 11:31

1 Answers1

0

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

Khalil Kamran
  • 65
  • 5