5

Running the following query using PDO (Actually, I use prepared statements but same problem)

INSERT INTO MyTable(MyField) VALUES('Row1'), ('Row2')

How can I get the Ids for the records relating to Row1 and Row2?

$db->lastInsertId() literally returns the last single Id.

Is it sufficient to take this last Id, subtract the # of records and assume that range covers all my records? can there be gaps/jumps. Is this query guaranteed to be atomic?

tereško
  • 58,060
  • 25
  • 98
  • 150
Basic
  • 26,321
  • 24
  • 115
  • 201
  • 1
    Which storage engine are you using? InnoDB? MyISAM? – James C May 09 '11 at 19:08
  • 1
    It's currently MyISAM but I'm hoping to use InnoDb in future so exporting the schema includes the FK constraints. That said, I can be flexible. – Basic May 09 '11 at 20:49

1 Answers1

3

If you're using MyISAM tables then because of the table level locking mechanism it's only possible for you to get given a range of ids.

After reading http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html#innodb-auto-increment-traditional assuming you're using "traditional" innodb locking (which likely you are) then again for a single statement the set of IDs will be sequential.

James C
  • 14,047
  • 1
  • 34
  • 43
  • 1
    thanks for the response, can you please clarify - Table-level locking (whilst normally bad) might be a boon in this scenario but the PHP lastInsertId() function returns only a single Id even if multiple records are inserted. – Basic May 09 '11 at 20:50
  • 1
    if you insert ten rows then the range of ids you just inserted will be `LAST_INSERT_ID() - 10` - `LAST_INSERT_ID()` – James C May 09 '11 at 20:59