Get ID of multiple rows insert

Databases
1 

$query = "INSERT INTO choice(choice_name, choice_type) VALUES ('choice 1', 'r'), ('choice 2', 'c'), ('choice 2', 'r')";

If I’m inserting multiple records at once, I want to insert in one query for better performance and is there a way to get the generated auto_increment ID of EACH inserted row ? Is this possible ? if not then I will do multiple query.

Thanks

2

You can get the LAST id added by various methods, and if you know how many rows you inserted, you now know all the earlier ids as well, don’t you.

(you can get the last id via php, or asking for max(id))

3

Aha…thanks that gives a clue.

4

You can’t get the last id through max(id), because someone else might have inserted a new row in the mean time.

And you might be right about getting the n ids before the last one inserted by your insert statement, it depends on if mysql does all the inserts of one insert statement without executing any other insert statement. I’m sure one of the mysql experts here can tell us that.

5

only if the table has some other unique key besides the surrogate auto_increment key – which it should have!!

in this case, let’s assume that choice_name and choice_type together form a unique key

then you would return the ids like this –

SELECT id
     , choice_name
     , choice_type
  FROM choice
 WHERE choice_name = 'choice 1' AND choice_type = 'r'
    OR choice_name = 'choice 2' AND choice_type = 'c'
    OR choice_name = 'choice 2' AND choice_type = 'r'

:slight_smile:

6

thanks for the reply, that auto_increment ID is primary key so it’s unique. I already got it using the clue given by Dr John.