Alternative to "PDO::lastInsertId" / "mysql_insert_id"

Question

I always hear that using "lastInsertId" (or mysql_insert_id() if you're not using PDO) is evil. In case of triggers it obviously is, because it could return something that's totally not the last ID that your INSERT created.

$DB->exec("INSERT INTO example (column1) VALUES ('test')");
// Usually returns your newly created ID.
// However when a TRIGGER inserts into another table with auto-increment:
// -> Returns newly created ID of trigger's INSERT
$id = $DB->lastInsertId();

What's the alternative?

Answer 1

It's not sophisticated and it's not efficient, but if the data you've inserted include unique fields, then a SELECT can obviously yield what you're after.

For example:

INSERT INTO example (column1) VALUES ('test');
SELECT id FROM example WHERE column1 = 'test';

Answer 2

I guess it's not really state of the art either but I use write locks to make sure that I really get the last inserted ID.

Answer 3

IMHO it's only considered "evil" because hardly any other SQL database (if any) has it.

Personally I find it incredibly useful, and wish that I didn't have to resort to other more complicated methods on other systems.

Answer 4

If you go the route of ADOdb (http://adodb.sourceforge.net/), then you can create the insert ID before hand and explicitly specific the ID when inserting. This can be implemented portably (ADOdb supports a ton of different databases...) and guarantees you're using the correct insert ID.

The PostgreSQL SERIAL data type is similar except that it's per-table/per-sequence, you specify the table/sequence you want the last insert ID for when you request it.

Answer 5

One alternative is to use sequences instead, so you generate the ID yourself before you do the insert.

Unfortunately they are not supported in MySQL but libraries like Adodb can emulate them using another table. I think however, that the emulation itself will use lastInsertId() or equivalent... but at least you are less likely to have a trigger on a table which is purely used for a sequence