• Advertisement
Sign in to follow this  

mySQL -- Retrieve index on INSERT?

This topic is 4710 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Is there a way in mySQL (or other databases for that matter) to retrieve an auto-incrementing value when performing an INSERT statement? e.g. I have a table such as the following:
Person
--------------
BIGINT      PersonID   (PK, Auto-increment)
VARCHAR(20) FirstName
VARCHAR(30) LastName
CHAR(1)     Gender
VARCHAR(50) Email
Typically I'd simply do an insert like this:
INSERT INTO Person (FirstName, LastName, Gender, Email) VALUES ('Benny', 'Hill', 'M', 'benny@hill.com');
However, there's no way of retrieving the new PK at the same time. Is there perhaps a way of locking the table to grab the most recent insert at the same time? Or even better, a way to do this without explicitly locking the table?

Share this post


Link to post
Share on other sites
Advertisement
I assume this is for a multi-user database, so its theoretically possible that another 'Person' could slip in between the Inserted person in question and a query to select the last auto-incremented pk? So far as I've seen, there's not perfect solution to this. When contemplating it before, I considered one possibility, not perfect, but would probably put you closer to 100%, anyway. My idea was to do the following:


  • Get a timestamp accurate down to the ms and store it in a variable

  • On insert, store the timestamp with the Person

  • Query for your pk, using the timestamp as your "where"

  • If there's multiple records returned (which would, of course, be unlikely), you could query again using more of the data for your person instance (name, gender, etc), until you have only one remaining pk.

  • Use the pk you deduced



Not horribly pretty, but I think it'd be fairly effective ;)

Share this post


Link to post
Share on other sites
In PHP the function mysql_insert_id does just this. I assume that this is part of the mysql C api and is accessible from any other language. :)

Edit: more info on the page I linked, including the mysql function LAST_INSERT_ID()

As for other databases, at work we use PostgreSQL and sequences for auto-incrementing. So in cases where I need the new id value, I first select nextval('seq_whatever_id') and then use that in subsequent queries.

Share this post


Link to post
Share on other sites
This issue has caused me no end of pain. No two databases seem to do it the same way. Mysql's LAST_INSERT_ID() is perhaps one of the easiest because, addressing the problems that Instruo brought up, it maintains the variable for every connection so you don't have to worry about someone else comming in and inserting in the same table before you can read it -- though you may still have to wory in a threaded app that shares connections.



Share this post


Link to post
Share on other sites
In MySQL you can either use the API function mysql_insert_id() or similar to retrieve it, or execute another query "SELECT last_insert_id()"

The latter technique has a problem if you're in a connection-pooling environment where you might get a different connection for the second query.

The first one should work.

Whatever API you are using will probably have a native way of retrieving the new ID. That is the safest and most reliable way of doing it.

Mark

Share this post


Link to post
Share on other sites
If you are using PHP there is always Pear::DB with cross-database implementations of everything one can possibly need, Sequences for example.

I should have used this for my last project... but I found out about it too late to implement it :(

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement