0

Possible Duplicate:
PHP/MySQL: Retrieving the last Inserted Ids for multiple rows

Mysql table columns ( ID, name) id- auto increment.

I will retrieve the last inserted id by...

  DECLARE value VARCHAR(30); 
   insert into table (name) values ('foo');
   select max(id) into value from table;

This is how i m retrieving the max of id.

What happens if multiple insert statements triggers at same time. Whats the best practice to do this.

1) Locking the Mysql table

2) add one more column and set it to '0' or '1' technique.

3) Any other ways to do this.

Am using MySql and c# .Net Thanks

Community
  • 1
  • 1
Learner
  • 346
  • 1
  • 6
  • 16
  • what is the purpose why you still execute this? `select max(id) into value from table;`? – John Woo Oct 04 '12 at 10:47
  • This [answer](http://stackoverflow.com/a/1008382/856202) should help: – Bruno Flávio Oct 04 '12 at 11:14
  • @JohnWoo i want the last inserted ID, which is my PK. its in SP. – Learner Oct 04 '12 at 11:33
  • read this for details: http://viralpatel.net/blogs/get-autoincrement-value-after-insert-query-in-mysql/ – AnandPhadke Oct 04 '12 at 11:12
  • Thanks for the post, it will also use LAST_INSERT_ID(). But what happens if more than one insert will hit the db at the same time from different users. If multiple insert will happens at same time it will take the 'last_insert_id' of 'last inserted table.... – Learner Oct 04 '12 at 11:43
  • 1
    @kishorkumar86 check the answer I advised you and the relevant [manual entry](http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id). As I understand it is a session scoped operation, so you will always get the latest insertion id on the current session. – Bruno Flávio Oct 04 '12 at 12:08

1 Answers1

1

You can use LAST_INSERT_ID(); :

INSERT INTO table (name) VALUES ('foo');
SELECT LAST_INSERT_ID();
Nasreddine
  • 36,610
  • 17
  • 75
  • 94