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

User · Answer

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'
);

User · Answer

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

User · Answer

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

User · Answer

i used return  this- gt db- gt insert id    for Codeigniter

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

simply use   last id   mysqli insert id  conn

User · Answer

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

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

Examples related to mysql

Examples related to insert

Examples related to key

Examples related to auto-increment