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.

Improve this question
1
  • 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. Commented Jan 16, 2023 at 13:27

2 Answers 2

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

I assumed InnoDB, because most applications should use InnoDB, it is the best general-purpose engine.
For engines with table-level locking (memory, myisam etc), auto-inc is trivial, as there is no concurrency to worry about.
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
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.
So you have to lock the table first? Or use a transaction?
|
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.

Improve this answer

1 Comment

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)?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.