Get the new record primary key ID from MySQL insert query?

Question

Let's say I am doing a MySQL INSERT into one of my tables and the table has the column item_id which is set to autoincrement and primary key.

How do I get the query to output the value of the newly generated primary key item_id in the same query?

Currently I am running a second query to retrieve the id but this hardly seems like good practice considering this might produce the wrong result...

If this is not possible then what is the best practice to ensure I retrieve the correct id?

Solution 1

You need to use the LAST_INSERT_ID() function: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

Eg:

INSERT INTO table_name (col1, col2,...) VALUES ('val1', 'val2'...);
SELECT LAST_INSERT_ID();

This will get you back the PRIMARY KEY value of the last row that you inserted:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client.

So the value returned by LAST_INSERT_ID() is per user and is unaffected by other queries that might be running on the server from other users.

Solution 2

BEWARE !! of LAST_INSERT_ID() if trying to return this primary key value within PHP.

I know this thread is not tagged PHP, but for anybody who came across this answer looking to return a MySQL insert id from a PHP scripted insert using standard mysql_query calls - it wont work and is not obvious without capturing SQL errors.

The newer mysqli supports multiple queries - which LAST_INSERT_ID() actually is a second query from the original.

IMO a separate SELECT to identify the last primary key is safer than the optional mysql_insert_id() function returning the AUTO_INCREMENT ID generated from the previous INSERT operation.

Solution 3

From the LAST_INSERT_ID() documentation:

The ID that was generated is maintained in the server on a per-connection basis

That is if you have two separate requests to the script simultaneously they won't affect each others' LAST_INSERT_ID() (unless you're using a persistent connection perhaps).

Solution 4

Here what you are looking for !!!

select LAST_INSERT_ID()

This is the best alternative of SCOPE_IDENTITY() function being used in SQL Server.

You also need to keep in mind that this will only work if Last_INSERT_ID() is fired following by your Insert query. That is the query returns the id inserted in the schema. You can not get specific table's last inserted id.

For more details please go through the link The equivalent of SQLServer function SCOPE_IDENTITY() in mySQL?

Solution 5

You will receive these parameters on your query result:

    "fieldCount": 0,
    "affectedRows": 1,
    "insertId": 66,
    "serverStatus": 2,
    "warningCount": 1,
    "message": "",
    "protocol41": true,
    "changedRows": 0

The insertId is exactly what you need.

(NodeJS-mySql)

View more solutions

Comments

[Amy Neville]

yes but what if in the interim (between queries in the process list) some other row has been inserted? Is there any way to write the insert query so that it outputs this?

[Amy Neville]

Ok, I see...the last insert id of the query is recorded even if it isn't logged in the result...$mysqli->insert_id

[Duncan Lock]

This will get you back the PRIMARY KEY value of the last row that you inserted, because it's per connection - each connection to the server will maintain it's own value for this. I've updated the answer to clarify this.

[bad_keypoints]

So suppose 3 users simultaneously posted their forms and my database has to enter them. I want to add a row corresponding to each newly created ID of table1 in table2. Is concurrency taken care of or will I have to do it in PHP manually, for incoming database write requests?

[Duncan Lock]

If, at the point that you do the table1 insert, the database knows all the information that you want to insert into table2, then you could use a trigger to do this, or combine this into a query. If not, then you'd need to use multiple queries - i.e. do it in PHP. It depends on what the data is and where it's coming from for the second insert.

[Houston]

nice... plain and simple explanation

[Explosion Pills]

LAST_INSERT_ID is a per-connection MySQL function. If you query for the last insert ID it is possible a separate connection will have performed a write and you will have the wrong ID.

[RozzA]

@ExplosionPills mysql_insert_id() also works on a per connection basis, but it also suffers strange behaviour as seen here stackoverflow.com/a/897374/1305910 in the comment by Cliffordlife -- nevermind we should all have been using mysqli

[MikeT]

how does that work with a failed insert ie insert ignore or insert on duplicate key? ie the insert failed ie does LAST_INSERT_ID() return the failed ID, null or the last successful id that's actually unrelated to the current query

[Duncan Lock]

According to the documentation (dev.mysql.com/doc/refman/5.7/en/…): "The value of LAST_INSERT_ID() remains unchanged if no rows are successfully inserted." - so, it will return the value of the last successfully inserted row.

[CarCar]

This one doesn't seem to be a good idea? If 2 clients call that function at the same time, they'll think they're inserting the same id when in reality they are not.... one will be slightly slower on the insert and get the next ID without it knowing.

[john ktejik]

can you clarify what you mean when you stay 'it wont work'?

[Photographer Britt]

I actually stumbled here looking for the php version of this answer thanks to google, so I voted this answer up. While technically the OP didn't ask for php, this could be helpful to a lot of people who stumble here as well.

[bgies]

I think it is dubious if your table has a trigger on it that does an insert into another table.... LAST_INSERT_ID() will return the id value of the other table..

[JuanSedano]

Thank you very much Arnav! I

[DeadSec]

Dude, you deserve a medal for this comment. This post was the best thing that happened today.

[Enrico]

You can use a PDO Object and get the last insert ID. You can also use SELECT LAST_INSERT_ID() inside the same transaction (it is very important to use the same transaction if you have several people inserting). Your proposed method have no guarantees - although unlikely, you could have collision on the keys. Also, adding all data via update means you can't have non-null fields, and this leads to worse data integrity as well. Finally, something may break betweek the insert of the random key and the update, and you are left with an unwanted state and no data integrity.

[Keiron Stoddart]

How about wrapping the insert statement and the SELECT LAST_INSERT_ID() in a stored procedure?

[Duncan Lock]

@KeironStoddart It works the same way: "MySQL :: MySQL 5.6 Reference Manual :: 20.2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()" dev.mysql.com/doc/refman/5.6/en/…

[Keiron Stoddart]

@DuncanLock, right on - I appreciate the reference. I was just commenting on the request to do the insert and the id select at the same time.

[Jack]

I don't know why the poster suggests insertion and update as two different operations; when I had to use this approach I generated the random key by code and used it in the insert query along with all the records fields. Honestly with UUID it's statistically impossible to have collisions (16^32 possibilities). I found this approach a valid solution for the OP issue

[Thomas Fox]

I would have thought that this should be the accepted answer. Transaction might not be necessary but it seems to be the safest and clearest approach to me.

[Oliver Voutat]

Isn't the more secure way doing this in a transaction inside a stored procedure?