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

?>