Development returning the last insert id of a sepcified table?

I know that i can use the mysql function LAST_INSERT_ID() to return the last auto-increment value that was created, but is there a way to specify the table?

say if i had a database with 2 tables `foo` and `bar`.

an insert was just made into `foo` by user 'a'
before that user 'b' made an entry into `bar` now if both users access the database througha website and thuis chances are by the same user name, if i called LAST_INSERT_ID() wouldnt that return user 'a's insert into table `foo`?

but what if i wanted to know the last insert id in table `bar`?


Any one know?

 

If it's an autoincrement value:

Code:

SELECT MAX(idField) FROM `table`

 

That's what I was going to say -

If the id is some strange alphanumeric hash thing that can't evaluate that way - you could always add a date-time column to your db and use that in your select.

 

its just a big int so that works great, thanks guys

 

I was kind of expecting a weird situation from you, didn't think it would be this easy

DTE: I didn't think you could make an AID with non-ints?

 

Selecting the max value is a really nasty way of doing it. Since MySQL already knows what the next auto increment should be, just use the SHOW TABLE STATUS queries and minus one number from the next auto_increment's value

 

Yeah - I just realized that that's probably not possible. I use an application that has a 30 character alphanumeric thing on every record of every table - but thinking about it I'm sure it's handled at the app level - not the DB level

 

What happens if you have deleted the last entry? surely the auto inc value is still the same but the last entry is actually auto_inc - 2 etc? I.e not a guaranteed way of getting the latest entry?

 

Yep, not guaranteed, but neither is using MAX, if you think about it (for exactly the same reasons he outlined in the first post) He really needs to be introducing table locks... or not designing a system that depends on knowing the last insert id

 

Only problem with that is it returns lots of other data which takes time to gather. show tables status like 'blah' on one of our larger innodb tables took 9 secs compared to 0.02 secs for a select max(primary_key).

 

Does MySQL not have a function similar or equivalent to SQL Servers IDENT_CURRENT??

http://msdn2.microsoft.com/en-us/library/ms175098(SQL.90).aspx

I know it's still a maturing platform, but I figured there should be such a provision within it - it's a common request, after all.

 

I take it this is not a high transaction volume system? If so, there are better ways to tackle this problem.

 

i just wish there was somthing as simple as LAST_INSERT_ID('table_name') <yes i looked in the doc's and no its not that easy.

ok i missed something when stating the question. and i feel stupid for forgetting it. I just need to know the last insert id of the data just entered because it affects the forign key of a row that will be inserted into the database making the upcoming insert a dependent of the row that was just inserted

i guess the only way im going to get the exact ID number i want every time without fail is after the data is entered run a query for the row ID number but query it using all the data that was used to insert it, thus the only thing that should come back is what is expected.

There has to be a better way

 

A lot of systems manage the keys in the code instead of within the database. This typically involves having a table that tracks the tablenames, the next key value, and the number of keys to hold. Then in the code, you track the tables and keys...and once you run out of keys you go back to the table and extract the next number of keys for that table.

tablename nextkeyvalue keyrange
---------- ------------- --------
...startup of system...
person 51000 500
...need more keys....
person 51500 500
...need more keys....
person 52000 500

 

If you're using PHP:

Code:

 mysql_insert_id() 

From here

But since you're using a bigint, it won't work, so try this in SQL

Code:

SELECT LAST_INSERT_ID(); 

From within the database. It'll only run off the last insert though.

http://dev.mysql.com/doc/refman/4.1/en/getting-unique-id.html

 

Is this thread safe? I had problems with this, but admittedly it has been a few years since I tried that approach.

 

What I think it is (from what I've just done) is that the first time I ran MAX on a table with 1.6m records in it, it took 0.97s to run.

But after that it runs in 0.00s, so I think it's an optimization thang.

nachofault: not sure, can't run it here as my tables no longer use autoincrement

 

mysql cache, i would imagine. if the table hasn't been altered since you last ran it anyway...

 

Altered it a few times by adding records.