I would like to retrieve the id of a row I just inserted.
In Oracle this problem is handled with the use of sequences,
SQL> SELECT uid_seq.NEXTVAL INTO user_id FROM DUAL; SQL> INSERT INTO users (id, username) VALUES (user_id, 'joebob'); 1 row created. SQL> -- do something else with user_id
Basically, a sequence can provide you, a-priori, a unique number safe to use in an insert statement. Concurrent calls to a sequence.NEXTVAL will be unique.
In mysql there are no sequences. Instead, an AUTO-INCREMENT field is often used and the LAST_INSERT_ID() will return the value in question. For example,
mysql> INSERT INTO users (username) VALUES ('joebob'); Query OK, 1 row affected (0.01 sec) mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 7 | +------------------+ 1 row in set (0.00 sec) mysql>
These two solutions often spill over and arguably break encapsulation of the data-access layer, leaving your code dependent on a specific database driver (and database engine). For example, in PHP (using a PDO interface) a sequence name is often required in order to use the correct sequence:
<?php // if there was an auto-increment $conn->exec('INSERT INTO table (data) VALUES(255)'); $id = $conn->lastInsertId(); // but if there was a sequence $conn->exec('INSERT INTO table (id, data) VALUES(sequence_name.NEXTVAL, 255)'); $id = $conn->lastInsertId('sequence_name'); ?>