17

What is the best way to get the auto-id value in the same SQL with a SELECT?

A forum said adding this "; has Return Scope_Identity()"
in the end of the SQL works in ASP.

Is there a corresponding way in PHP?

Michael Myers
  • 188,989
  • 46
  • 291
  • 292
Kaptah
  • 9,785
  • 4
  • 22
  • 19

7 Answers7

16

It depends on your database server. Using MySQL, call mysql_insert_id() immediately after your insert query. Using PostgreSQL, first query "select nextval(seq)" on the sequence and include the key in your insert query.

Querying for "select max(id) + 1 from tbl" could fail if another request inserts a record simultaneously.

Matthew
  • 2,024
  • 15
  • 19
  • max(id)+1 can also fail due to the nature of auto-incrementing IDs, SQL skips values for performance sometimes (if it knows 12 was the last value, but there might be an in progress write, it'll just go 'screw it' and write 14 rather than waiting to find out) – The Lemon May 04 '22 at 05:03
8

In postgres the best way is to do something like:

insert into foos(name) values ('my_foo') returning id;
Omar Qureshi
  • 8,963
  • 3
  • 33
  • 35
3

It depends on the database engine you are using. Some DBMS, like Firebird for example, have RETURNING clause you can add to your query. For example, if you have a table named TABLE1 with autoincrement column named ID, you can use this:

insert into TABLE1(columns...) values (values...) returning ID;

And it would return the inserted ID just like a regular select statement.

Milan Babuškov
  • 59,775
  • 49
  • 126
  • 179
2

In Microsoft Transact SQL you can use @@IDENTITY.

e.g.

DECLARE @Table TABLE ( col0 INT IDENTITY, col1 VARCHAR(255), col2 VARCHAR(255))

INSERT INTO @Table (col1, col2) VALUES ('Hello','World!')

SELECT @@Identity

SELECT * FROM @Table
MyItchyChin
  • 13,733
  • 1
  • 24
  • 44
  • 1
    never use @@identity, it wil not always give you the correct identity value and can cause very bad data integrity problems. Use scope_identity() – HLGEM Jul 10 '09 at 13:50
  • Well, @@identity is ok as long as you know what it does and the scope of its effect. Just be careful if you have triggers on tables (an insert to a table with an identity column in a trigger will cause @@identity to be updated), or if the table is replicated. Ok, it's probably safer to use scope_identity() :) – Jon Jul 10 '09 at 13:57
0

Be very careful: Apparently select nextval(seq) does not work in high concurrency - some other connection can insert between the time when you inserted and the time when you called select nextval(seq). Always test such code in high concurrency test harnesses.

A-K
  • 16,804
  • 8
  • 54
  • 74
  • This doesn't matter unless you are using the ID for ordering, because you are using the result from nextval(seq), not another call to the sequence itself. – Brian Ramsay Apr 25 '10 at 19:11
0

In SQL Server a insert using the select statement can have an output clause which will return the identity value and whatever other columns you might need to identify which identity goes to which record. If you are using a values clause, then use select scope_identity () immediately after the insert.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
0

In php: mysql_insert_id() http://us3.php.net/mysql_insert_id

or

If you wanted to genterate the number from your mySql select query, you could use this EDIT:

SELECT LAST_INSERT_ID(`1`) + 1 FROM table
superUntitled
  • 22,351
  • 30
  • 83
  • 110
  • Selecting the max could be an issue if multiple processes are doing inserts could it not? – schooner Mar 07 '09 at 10:38
  • 1
    if you insert a record, then delete a record, then run this query you will get the identity of the deleted record. the next insert will not get that identity in any implementation I'm aware of. Avoid this at all costs... – MatBailie Mar 07 '09 at 15:11
  • you should put a read lock on the table if you want this to be safe! even then, I'm not sure that the DBMS won't re-use some deleted id if a gap in the table exists somewhere. – ʞɔıu Mar 07 '09 at 17:50