php mysql primary-key auto-increment multiple-insert

How retrieve in PHP the generated PRIMARY KEY from an AUTO_INCREMENT column for each row with an INSERT of multiple rows in MySQL?

I have an INSERT with syntax for insert multiple rows:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

With MySQL function LAST_INSERT_ID() can retrieve the primary key of an generated AUTO_INCREMENT column, but how can I retrieve the primary key for row (1,2,3), (4,5,6) AND (7,8,9) inserted in one unique SQL INSERT instruction?

The programming language is PHP 5.4.13 and MySQL Server 5.6.10

Solution

There is no way to retrieve more than one generated auto-inc variable. Calling LAST_INSERT_ID() returns you the first value generated by the last INSERT.

To get all the values, insert one row at a time, and call LAST_INSERT_ID() after each one. This results in more overhead, but it's the only workaround.

This is especially an issue if you're doing INSERT...SELECT or LOAD DATA INFILE, generating many new id values in a batch.