Can you trust mysql_insert_id?

Question

I have a registration page which then inserts data into two tables in MySQL. The first one is technical information like username/password/email, and the other one is for personal information like name/address and such.

My technical information table has a primary key auto incremental ID, and then, once I create the row in this table, I immediately create another row in the general information table with the same id.

To obtain that ID, I use the mysql_insert_id() function right after the first insert query.

My question is rather general: can you really trust this function that it will necessarily return the appropriate index? What if I had a website with many users registering every second, would it still be reliable, and always return the ID? Or does it just return the last primary key of a table generated?

"What if I had a website with many users registering every second" --- yes it would be 100% reliable. One user per connection. — TheCarver, Dec 16 '13 at 18:27
Just curious, why would you keep technical and personal information in separate tables in the first place? — Mikk, Dec 16 '13 at 18:28
Also, `mysql_insert_id` is not supported anymore. You should be using `mysqli_insert_id()` (mysqli) or `PDO::lastInsertId()` (PDO). — TheCarver, Dec 16 '13 at 18:29
requisite reminder that you should be using: mysqli_insert_id() or PDO::lastInsertId() — Digital Chris, Dec 16 '13 at 18:30

score 2 · Answer 1 · answered Dec 16 '13 at 18:29

Yes, you can.

The function is per-session, but it is also only valid for the very last query.

That is, you'll never have one user "polluting" the last_insert_id of another, but you cannot recover an insert_id from two inserts ago. When you need to do several inserts in a row and don't need the ids until later, set them as variables:

INSERT INTO ...
SET @foo_id = LAST_INSERT_ID();
INSERT INTO ...
SET @bar_id = LAST_INSERT_ID();
INSERT INTO some_join_table VALUES (@foo_id, @bar_id);

Can you trust mysql_insert_id?

1 Answers1

Linked