The 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
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?
- There is no way to return anything from an
INSERTquery like that; why do you think it might produce the wrong result?
- Well, if the process is executed by 2 people seperately then you could get an overlapping process list I think – since you have to execute 2 separate queries?
- Possible duplicate of PHP/MySQL insert row then get ‘id’
- @JimFell yes it’s basically the same question, but this one has much better answers in general
- Use postgresql then do like this INSERT INTO table (col1,col2) VALUES (val1,val2) RETURNING id or INSERT INTO table (col1,col2) VALUES (val1,val2) RETURNING * or UPDATE table SET col1=val1, col2=val2 WHERE statement RETURNING id or UPDATE table SET (col1,col2)=(val1,val2) WHERE statement RETURNING id or UPDATE table SET (col1,col2)=(val1,val2) WHERE statement RETURNING *
The Answer 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
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.
The Answer 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.
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
The Answer 3
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).
The Answer 4
Here what you are looking for !!!
This is the best alternative of
SCOPE_IDENTITY() function being used in
You also need to keep in mind that this will only work if
Last_INSERT_ID() is fired following by your
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?
The Answer 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
insertId is exactly what you need.
The Answer 6
If in python using pymysql, from the cursor you can use cursor.lastrowid
The Answer 7
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' );
The Answer 8
simply use “$last_id = mysqli_insert_id($conn);”
The Answer 9
Otherwise with a LAST_INSERT_ID you can get the value like this:
The Answer 10
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;
This function will be return last inserted primary key in table.
The Answer 11
return $this->db->insert_id(); for Codeigniter
The Answer 12
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.