Alternative "PDO :: lastInsertId" / "mysql_insert_id"

Question

I always hear that using "lastInsertId" (or mysql_insert_id () if you are not using PDO) is evil. In the case of triggers, this is obvious because it can return something that is not completely the last identifier created by your INSERT.

$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 INSERT $id = $DB->lastInsertId(); 

Which alternative?

Answer 1

If you are following the ADOdb route ( http://adodb.sourceforge.net/ ), you can create an insert identifier before hand and explicitly specify the identifier when inserting. This can be implemented portable (ADOdb supports tons of different databases ...) and ensures that you use the correct insert identifier.

The PostgreSQL SERIAL data type is similar to the type, except that for each table / for each sequence you specify the table / sequence in which you want to use the last insert identifier when you query it.

Answer 2

IMHO this was considered "evil" because hardly any other SQL database (if any) has it.

Personally, I find this incredibly useful and wish that I did not have to resort to other more complex methods in other systems.

Answer 3

One option is to use sequences instead, so you yourself create an identifier before you insert.

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

Answer 4

I assume this is not entirely up to date, but I use write locks to make sure that I really get the last inserted id.

Answer 5

It is neither complicated nor efficient, but if the entered data contains unique fields, then SELECT can obviously give what you need.

For example:

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

Answer 6

You can try the following:

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