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?

Gratt

Answer 1

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.

Mickiemickle

Looks quite interesting. Which effect does this technique have on performance? – Gratt

Yeah, but what do you do if you are stuck with PDO? – Howdoyoudo

You can write your own code to do the same thing ADOdb is doing, except using PDO. You essentially end up with a separate table to store the sequence, which you WRITE LOCK before incrementing the sequence value when you fetch a new ID. – Mickiemickle

It shouldn't affect performance too much, it's only a very simple table that gets locked, fetch a single record, increment, unlock. I haven't noticed any performance difference on any application I've used it with. Remember: get it working first, tweak performance later. – Mickiemickle

Answer 2

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.

Sprag

At least MSSQL has the same feature. In my old job using the "insert id" has caused major headaches when someone added a trigger to a table that broke the whole website - without obvious reason for those who didn't know about that trigger... – Gratt

Answer 3

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

Killarney

Answer 4

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.

Begat

Obviously also working - but IMHO locking is even more evil in high performance web apps... – Gratt

Answer 5

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';

Motorboat

Obviously ;) - But also obviously quite ugly ;) – Gratt

Answer 6

You could try this:

$sql = "SELECT id FROM files ORDER BY id DESC LIMIT 1";
$PS = $DB -> prepare($sql);
$PS -> execute();
$result = $PS -> fetch();

Blate

That's not an alternative in high-traffic environments. You'll constantly get wrong IDs. – Gratt