uint64_tmysql_insert_id(MYSQL *mysql) Returns the value generated for anAUTO_INCREMENT column by the previousINSERT orUPDATE statement. Use this function after you have performed anINSERT statement into a table that contains anAUTO_INCREMENT field, or have usedINSERT orUPDATE to set a column value withLAST_INSERT_ID(.expr)
The return value ofmysql_insert_id() is always zero unless explicitly updated under one of the following conditions:
INSERTstatements that store a value into anAUTO_INCREMENTcolumn. This is true whether the value is automatically generated by storing the special valuesNULLor0into the column, or is an explicit nonspecial value.In the case of a multiple-row
INSERTstatement,mysql_insert_id()returns the first automatically generatedAUTO_INCREMENTvalue that was successfully inserted.If no rows are successfully inserted,
mysql_insert_id()returns 0.If an
INSERT ... SELECTstatement is executed, and no automatically generated value is successfully inserted,mysql_insert_id()returns the ID of the last inserted row.If an
INSERT ... SELECTstatement usesLAST_INSERT_ID(,expr)mysql_insert_id()returnsexpr.INSERTstatements that generate anAUTO_INCREMENTvalue by insertingLAST_INSERT_ID(into any column or by updating any column toexpr)LAST_INSERT_ID(.expr)If the previous statement returned an error, the value of
mysql_insert_id()is undefined.
The return value ofmysql_insert_id() can be simplified to the following sequence:
If there is an
AUTO_INCREMENTcolumn, and an automatically generated value was successfully inserted, return the first such value.If
LAST_INSERT_ID(occurred in the statement, returnexpr)expr, even if there was anAUTO_INCREMENTcolumn in the affected table.The return value varies depending on the statement used. When called after an
INSERTstatement:If there is an
AUTO_INCREMENTcolumn in the table, and there were some explicit values for this column that were successfully inserted into the table, return the last of the explicit values.
When called after an
INSERT ... ON DUPLICATE KEY UPDATEstatement:If there is an
AUTO_INCREMENTcolumn in the table and there were some explicit successfully inserted values or some updated values, return the last of the inserted or updated values.
mysql_insert_id() returns0 if the previous statement does not use anAUTO_INCREMENT value. If you must save the value for later, be sure to callmysql_insert_id() immediately after the statement that generates the value.
The value ofmysql_insert_id() is affected only by statements issued within the current client connection. It is not affected by statements issued by other clients.
TheLAST_INSERT_ID() SQL function will contain the value of the first automatically generated value that was successfully inserted.LAST_INSERT_ID() is not reset between statements because the value of that function is maintained in the server. Another difference frommysql_insert_id() is thatLAST_INSERT_ID() is not updated if you set anAUTO_INCREMENT column to a specific nonspecial value. SeeInformation Functions.
mysql_insert_id() returns0 following aCALL statement for a stored procedure that generates anAUTO_INCREMENT value because in this casemysql_insert_id() applies toCALL and not the statement within the procedure. Within the procedure, you can useLAST_INSERT_ID() at the SQL level to obtain theAUTO_INCREMENT value.
The reason for the differences betweenLAST_INSERT_ID() andmysql_insert_id() is thatLAST_INSERT_ID() is made easy to use in scripts whilemysql_insert_id() tries to provide more exact information about what happens to theAUTO_INCREMENT column.
The OK packet used in the client/server protocol holds information such as is used for session state tracking. When clients read the OK packet to know whether there is a session state change, this resets values such as the last insert ID and the number of affected rows. Such changes causemysql_insert_id() to return 0 after execution of commands including but not necessarily limited toCOM_PING,COM_REFRESH, andCOM_INIT_DB.
A user-specified
AUTO_INCREMENTvalue in a multiINSERTstatement falls within the range between the currentAUTO_INCREMENTvalue and the sum of the current and number of rows affected values.