Is there a better way to return the last inserted id for multiple insertion?

Question

I use PHP to add 95 new rows to my MySQL database and return a new row id. The runtime takes about 24 seconds. If I pass in 30 seconds, PHP will stop executing (the default limit is 30 seconds).

I need to return a row id for each row inserted so that I can use it to set related data.

My current solution is this:

/* snippets from my class objects to illustrate my code */ // This foreach takes 24 seconds on just 95 rows foreach($list as $row) { $id = $this->importRows($sid,$table) array_push($id_list,$id); } /* PDO insertion */ protected function importRows($row) { $sql = "INSERT INTO my_table (name, colour) VALUES $row['name'], $row['colour']"; $result = $this->db->exec($sql); return $this->db->lastInsertId(); } 

To reduce the insertion time, I hope that I can insert several rows into one query According to MySQL (scroll down to the red lens and the word IMPORTANT), it says:

If you insert multiple rows using the same INSERT statement, LAST_INSERT_ID () returns the value generated for the first inserted row only.

The solution that they offer is to create another table and insert a new identifier there, then I can get the new identifier using the select statement at the end.

Has anyone worked on a similar solution? Any suggestions on how I can make this more time efficient?

Answer 1

This is the trick I used in such cases:

 $query = "INSERT INTO my_table (name, colour) VALUES"; $i = 0; foreach( $rows as $row ) { if( $i ) $query .= ','; $query .= " ( $row[ 'name' ], IF( @id$i := LAST_INSERT_ID(), $row[ 'colour' ], $row[ 'colour' ] ) )"; ++$i; } $result = $this->db->exec( $query ); 

then to get the identifiers for this request you need:

 SELECT @id1, @id2, @id3, ... 

Answer 2

• lock table
• add your data with one "insert"
• unlock table
• get the last insert id
• another identifier can be calculated: id[n]=last_insert_id+n , where n is the number of your inserted row