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

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 2

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