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

MysqlInsertKeyAuto Increment

Mysql Problem Overview


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?

Mysql Solutions


Solution 1 - Mysql

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 - Mysql

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 - Mysql

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 - Mysql

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 https://stackoverflow.com/questions/560783/the-equivalent-of-sqlserver-function-scope-identity-in-mysql

Solution 5 - Mysql

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)

Solution 6 - Mysql

If in python using pymysql, from the cursor you can use cursor.lastrowid.

It is a documented extension in PEP-249 DB API standard, and also works with other Python MySQL implementations.

Solution 7 - Mysql

You need to use the LAST_INSERT_ID() function with transaction:

START TRANSACTION;
  INSERT INTO dog (name, created_by, updated_by) VALUES ('name', 'migration', 'migration');
  SELECT LAST_INSERT_ID();
COMMIT;

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

This function will be return last inserted primary key in table.

Solution 8 - Mysql

If you need the value before insert a row:

CREATE FUNCTION `getAutoincrementalNextVal`(`TableName` VARCHAR(50))
    RETURNS BIGINT
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

    DECLARE Value BIGINT;

    SELECT
        AUTO_INCREMENT INTO Value
    FROM
        information_schema.tables
    WHERE
        table_name = TableName AND
        table_schema = DATABASE();

    RETURN Value;

END

You can use this in a insert:

INSERT INTO
    document (Code, Title, Body)
VALUES ( 				
    sha1( concat (convert ( now() , char), ' ',   getAutoincrementalNextval ('document') ) ),
    'Title',
	'Body'
);

Solution 9 - Mysql

Simply use:

$last_id = mysqli_insert_id($conn);

Solution 10 - Mysql

If you are using PHP: On a PDO object you can simple invoke the lastInsertId method after your insert.

Otherwise with a LAST_INSERT_ID you can get the value like this: SELECT LAST_INSERT_ID();

Solution 11 - Mysql

i used return $this->db->insert_id(); for Codeigniter

Solution 12 - Mysql

Do this:

$idc = DB::table('tb_clients')->insertGetId([
                'ide'                   => $ide,
                'nome'                  => $nome,
                'email'                 => $email 
            ]);

on $idc you will get the last id

Solution 13 - Mysql

I just want to share my approach to this in PHP, some of you may found it not an efficient way but this is a 100 better than other available options.

generate a random key and insert it into the table creating a new row. then you can use that key to retrieve the primary key. use the update to add data and do other stuff.

doing this way helps to secure a row and have the correct primary key.

I really don't recommend this unless you don't have any other options.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionAmy NevilleView Question on Stackoverflow
Solution 1 - MysqlDuncan LockView Answer on Stackoverflow
Solution 2 - MysqlMartin Sansone - MiOEEView Answer on Stackoverflow
Solution 3 - MysqlExplosion PillsView Answer on Stackoverflow
Solution 4 - MysqlPVRView Answer on Stackoverflow
Solution 5 - MysqlMESepehrView Answer on Stackoverflow
Solution 6 - MysqlmojokenView Answer on Stackoverflow
Solution 7 - MysqlUladzislau VavilauView Answer on Stackoverflow
Solution 8 - MysqlPaulo A. CostaView Answer on Stackoverflow
Solution 9 - MysqlCurious FlowerView Answer on Stackoverflow
Solution 10 - MysqlOlafnyrView Answer on Stackoverflow
Solution 11 - MysqlHamza SaeedView Answer on Stackoverflow
Solution 12 - MysqlLuciano CoelhoView Answer on Stackoverflow
Solution 13 - MysqlSingh GView Answer on Stackoverflow