getting new record id on autoincrementing field

[klitscher]

Hey there,

I am inserting a new row into a table where the record_id is an autoincrementing field.  I have other data that has to be inserted into other tables with the record_id for that row, but don't know how to grab the new id to use to insert the rows in the other tables...would I just use

"SELECT record_id FROM table ORDER BY record_id DESC LIMIT 0,1;"

Thanks in advance...
Ken

[COBOLdinosaur]

SELECT MAX(record_id) FROM table;

[VGR]

yo. classical problem.

the ***theoretical*** solution is to use mysql_last_insert_id() function from PHP, ***but*** you could just as well rely - as I do - on the select you provided : select id from yourtable order by id desc limit 1;". Moreover, it's faster than the max() computation. You can just say "LIMIT 1" by the way.

This said, to be honest you run the risk of a second replace or insert having taken place between YOUR insert and YOUR select, as your two operations are not atomical. You can either use a TRANSACTION, or just LOCK the table if you really want to ensure 0 risk (a too high price to pay IMHO for classical webservers doing 99% selects and 1% updates/inserts/replaces/deletes and having medium traffic & activity)


best regards

[klitscher]

Thanks for the help gents.

I am not too worried about an insert taking place between the insert and the select...this isn't a high usage script that will be running and we have a total userbase of under 200.  I appreciate the help...kinda feel like you all are writing half my script...I'll spread the word.

Ken

[rdivilbiss]

Just to add to the already good advice, MySQL 5+ now has @@IDENTITY like other SQL databases.

You should be able to execute a multiple query like this:

"INSERT INTO foo (auto,text) VALUES(NULL,'text'); SELECT @@IDENTITY;"

Which is worthless from PHP, by the way.



 

[VGR]

to complete what my colleague wrote, you have since MySql 3.23.x the MySql function LAST_INSERT_ID() that could be used in a multi-statement query like above.

the LAST_INSERT_ID() type-of function is not a SQL-99 requirement, even optional, and is not supported by any other major RDBMS vendor, at least under a function form (there may be "macros" like the @@identity above, a bit like PL/SQL extensions to Oracle, available only in the console/TOAD and not at the API level).
I only checked postgresql, oracle, mysql, sql-server for SQL-99 features IDs B031 to P003 , so I may have missed something.

Aaaah sorry last minute modification : SQL-Server has a ident_current(`table_name') function as a vendor extension to SQL-99