23

When inserting data into a table which has an auto-incremented PK, I need to obtain that key for use in another statement. As many questions show on SO this can be done in PHP using mysql_insert_id().

However, I have been grouping my inserts together so I insert more than one row at a time. I did this as I guessed there would probably be some performance issue, please advise if I am wrong. Anyway, as far as I am aware however, mysql_insert_id() only returns the last id, when I need the ids for all the inserted rows.

I guess in this case I could:

  1. Do some simple maths to calculate all the ids using mysql_insert_id() and the number of rows I have entered. But is this guaranteed be consistently correct?

  2. Use multiple insert statements, one for each row

  3. Generate my IDs before and no use auto-increment.

I am sure this must be a classic problem, so I am wondering on what the most common and advisable approaches are.

George Cummins
  • 28,485
  • 8
  • 71
  • 90
zenna
  • 9,006
  • 12
  • 73
  • 101
  • in case of do or die one can add a new column to table and can insert a guid and according to guid all the records can be fetched easily. – garish Jan 16 '23 at 13:27

2 Answers2

34

Calling last_insert_id() gives you the id of the FIRST row inserted in the last batch. All others inserted, are guaranteed to be sequential.

Unless you're doing something very strange, this allows you to calulate the ID of each row easily enough.

Actually the behaviour varies in 5.1 depending on the setting of the innodb auto increment mode parameter; this should not matter. As long as you don't change it from the default, you will see the expected behaviour.

There are occasional cases where this doesn't do what you expect and is not useful - such as if you do an ON DUPLICATE KEY UPDATE or INSERT IGNORE. In these cases, you'll need to do something else to work out the IDs of each row.

But for a plain vanilla INSERT batch, with no values specified for the auto-inc column, it's easy.

A full description of how auto-increments are handled in innodb is here

MarkR
  • 62,604
  • 14
  • 116
  • 151
  • 1
    I assumed InnoDB, because most applications should use InnoDB, it is the best general-purpose engine. – MarkR Aug 16 '09 at 20:32
  • For engines with table-level locking (memory, myisam etc), auto-inc is trivial, as there is no concurrency to worry about. – MarkR Aug 16 '09 at 20:32
  • This is what I thought by 'do some maths'. Is chaos wrong then by saying it's not guaranteed to be sequential? or perhaps he misunderstood me – zenna Aug 16 '09 at 20:46
  • Yes, but your maths would be wrong as last_insert_id() gives the first ID, not the last one. Chaos is right saying it's not absolutely guaranteed to be sequential, read the link above. – MarkR Aug 16 '09 at 20:57
  • So you have to lock the table first? Or use a transaction? –  Jul 16 '13 at 21:48
  • You don't need to lock the table or use an explicit transaction. See the documentation. You only need to ensure that auto-inc mode is not set to the non-default value 2. innodb_auto_inc_lock_mode=2 probably breaks many bapplications. – MarkR Jul 18 '13 at 10:48
  • so firends can we get last insert id and add the count of insertation for get the last row inserted? – Peyman abdollahy Jun 24 '15 at 13:12
  • How about a insert ignore batch? If the first one is a duplicate, does it return 0 for last_id or the second inserted one. How can you map those not inserted to the ones that are? – Friso Kluitenberg Aug 18 '16 at 11:40
2

There are a couple of approaches that I'd use, if I were me:

First, there's the CreateDate field with a default of CURRENT_TIMESTAMP method. You essentially select CURRENT_TIMESTAMP to get the date, run the insert, and then select id from table where CreateDate > $mytimestamp.

Secondly, you could create a trigger on the table to log after insert and put the new IDs in a little audit table. Truncate the table before checking those IDs, though. I would use this method if your table is millions upon millions of rows.

Eric
  • 92,005
  • 12
  • 114
  • 115
  • The `CURRENT_TIMESTAMP` approach seems a little bit dangerous to me. What if you and someone else write at the same table at the same time (same timestamp)? – tonix Dec 10 '15 at 14:21