How can I get a newly inserted auto increment value?

Question

Say a table T with a PRIMARY KEY id, I set it to AUTO INCREMENT.

I need to get the newly INSERTED id value for other operation, however, since it's the only PRIMARY KEY and AUTO INCREMENT, I don't how to do it.

Another SELECT with all other values in WHERE is valid in theory, but it's too slow.

Any better ideas?

The API client should be able to return the last inserted ID. — Kermit, Apr 24 '14 at 21:18

score 2 · Accepted Answer · answered Apr 24 '14 at 21:24

2

Should you be checking LAST_INSERT_ID() like

select LAST_INSERT_ID();

answered Apr 24 '14 at 21:24

Rahul

76,197
13
71
125

Thank you, I am currently using this. – fish748 Apr 25 '14 at 07:58

score 0 · Answer 2 · answered Apr 24 '14 at 21:19

0

Does this not work?

select max(id) from T;

answered Apr 24 '14 at 21:19

dg99

5,456
3
37
49

Then please edit your original question to show a sample of the input you're processing and the output you would like to see. – dg99 Apr 24 '14 at 21:24

score 0 · Answer 3 · answered Apr 24 '14 at 21:27

0

This will produce the last id without functions

SELECT id FROM t ORDER BY id DESC LIMIT 1

By the way .. You should get the id after your insert statement from any MySQL api

answered Apr 24 '14 at 21:27

Othman

2,942
3
22
31

score 0 · Answer 4 · edited May 23 '17 at 10:32

0

It obviously depends on what database engine you use. If , like I suppose, We are speaking about MSSQL, a good practise is to return last id generated in the current transaction (or session). You could see this post for more info.

Anyway, you could calculate the max id, but whitout warranties if someone instert new row in the while.

edited May 23 '17 at 10:32

Community

1
1

answered Apr 24 '14 at 21:27

zeppaman

844
8
23

How can I get a newly inserted auto increment value?

4 Answers4