6

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?

BlaM
  • 26,721
  • 31
  • 89
  • 104

6 Answers6

4

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.

Alnitak
  • 313,276
  • 69
  • 379
  • 466
  • 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... – BlaM Nov 14 '08 at 13:16
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

Tom Haigh
  • 54,886
  • 20
  • 107
  • 138
2

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.

Keith Palmer Jr.
  • 26,497
  • 16
  • 62
  • 103
  • Looks quite interesting. Which effect does this technique have on performance? – BlaM Nov 14 '08 at 15:47
  • 2
    Yeah, but what do you do if you are stuck with PDO? – Elijah Feb 24 '10 at 18:47
  • 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. – Keith Palmer Jr. Feb 25 '10 at 13:27
  • 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. – Keith Palmer Jr. Feb 25 '10 at 13:29
1

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.

markus
  • 38,729
  • 23
  • 95
  • 139
  • Obviously also working - but IMHO locking is even more evil in high performance web apps... – BlaM Nov 14 '08 at 13:03
0

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';
Peter Howe
  • 1,353
  • 2
  • 15
  • 28
-3

You could try this:

$sql = "SELECT id FROM files ORDER BY id DESC LIMIT 1";
$PS = $DB -> prepare($sql);
$PS -> execute();
$result = $PS -> fetch();
mikeyD
  • 1
  • 3
    That's not an alternative in high-traffic environments. You'll constantly get wrong IDs. – BlaM Aug 02 '12 at 10:07