Rectangle 27 0

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


@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

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.

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.

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.

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

Note
Rectangle 27 0

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


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
INSERT INTO
    document (Code, Title, Body)
VALUES (                
    sha1( concat (convert ( now() , char), ' ',   getAutoincrementalNextval ('document') ) ),
    'Title',
    'Body'
);

If you need the value before insert a row:

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.

You can use this in a insert:

Note
Rectangle 27 0

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


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

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.

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

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?

So this is unaffected by other queries that might be running on the server from other users.

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 ID that was generated is maintained in the server on a per-connection basis

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.

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?

Note
Rectangle 27 0

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


select LAST_INSERT_ID()

Here what you are looking for !!!

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.

Note