Back to General and Gameplay Programming

mySQL -- Retrieve index on INSERT?

General and Gameplay Programming Programming

Started by Mattman March 31, 2005 05:01 PM

4 comments, last by Leffe 19 years ago

Mattman

336

Author

March 31, 2005 05:01 PM

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?

Instruo

373

March 31, 2005 05:10 PM

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

"Game Programming" in an of itself does not exist. We learn to program and then use that knowledge to make games.

UfoZ

150

March 31, 2005 05:14 PM

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.

turnpast

1,011

March 31, 2005 09:15 PM

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.

markr

1,692

April 01, 2005 08:37 AM

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

Leffe

301

April 01, 2005 08:55 AM

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 :(

mySQL -- Retrieve index on INSERT?

This topic is closed to new replies.

Popular Topics

Recommended Tutorials

mySQL -- Retrieve index on INSERT?

This topic is closed to new replies.

Popular Topics

Recommended Tutorials

Reticulating splines