mysql: is there a way to get any information back from an insert?

BrianK · 05-07-2008, 01:09 PM

I'm inserting something into a mysql database & need to know the auto-increment id of the newly inserted item after the insert. Is there a way of quickly getting that id back other than searching the database for one of the fields I just inserted? I suppose I could speed up the search query by ordering on the id & doing a limit 1, but I'm not incredibly thrilled with that idea.

So, is there some fancy way of getting information back from an INSERT?

paulsm4 · 05-07-2008, 01:16 PM

You can try "last_insert_id()":
http://dev.mysql.com/doc/refman/5.0/...tionality.html

95se · 05-07-2008, 01:32 PM

Quote:

Originally Posted by BrianK

I'm inserting something into a mysql database & need to know the auto-increment id of the newly inserted item after the insert. Is there a way of quickly getting that id back other than searching the database for one of the fields I just inserted? I suppose I could speed up the search query by ordering on the id & doing a limit 1, but I'm not incredibly thrilled with that idea.

So, is there some fancy way of getting information back from an INSERT?

Plus, you could run into concurrency issues. Use Paulsm's advice.

BrianK · 05-07-2008, 05:15 PM

Thanks guys. Looks like a workable solution. It still seems like there's the potential for a race fouling up the results, but it's really, really unlikely in this case.

chrism01 · 05-07-2008, 05:49 PM

If you wrap SQL Transaction statements around that (and you should), then it'll only see what the current txn has done (until you do a commit/rollback).
http://dev.mysql.com/doc/refman/5.1/en/commit.html
Note that the ISAM engine does not support SQL txns, use InnoDB or NDB.

95se · 05-09-2008, 06:51 AM

Also, last_insert_id works on a per-client basis, so as long as you aren't doing anything else with the client (ie. you control your app), then it'll work as expected. So, if someone else does an insert after you do yours, but before you do a last_insert_id, then it'll still give you the insert id of YOUR last insert, not the one that followed from someone else

BrianK · 05-09-2008, 07:04 PM

Quote:

Originally Posted by 95se

Also, last_insert_id works on a per-client basis, so as long as you aren't doing anything else with the client (ie. you control your app), then it'll work as expected. So, if someone else does an insert after you do yours, but before you do a last_insert_id, then it'll still give you the insert id of YOUR last insert, not the one that followed from someone else

Oh nice. Then that's perfect.

Thanks again.