MySQL :: MySQL 8.4 C API Developer Guide :: 5.4.42 mysql_insert_id() (original) (raw)

5.4.42 mysql_insert_id()

uint64_t
mysql_insert_id(MYSQL *mysql)

Description

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 an AUTO_INCREMENT field, or have used INSERT 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:

• INSERT statements that store a value into an AUTO_INCREMENT column. This is true whether the value is automatically generated by storing the special valuesNULL or 0 into the column, or is an explicit nonspecial value.
• In the case of a multiple-rowINSERT statement,mysql_insert_id() returns the first automatically generatedAUTO_INCREMENT value that was successfully inserted.
  If no rows are successfully inserted,mysql_insert_id() returns 0.
• If anINSERT ... SELECT statement is executed, and no automatically generated value is successfully inserted,mysql_insert_id() returns the ID of the last inserted row.
• If anINSERT ... SELECT statement usesLAST_INSERT_ID(expr),mysql_insert_id() returns_expr_.
• INSERT statements that generate an AUTO_INCREMENT value by insertingLAST_INSERT_ID(expr) into any column or by updating any column toLAST_INSERT_ID(expr).
• If the previous statement returned an error, the value ofmysql_insert_id() is undefined.

The return value ofmysql_insert_id() can be simplified to the following sequence:

1. If there is an AUTO_INCREMENT column, and an automatically generated value was successfully inserted, return the first such value.
2. IfLAST_INSERT_ID(expr) occurred in the statement, return_expr_, even if there was anAUTO_INCREMENT column in the affected table.
3. The return value varies depending on the statement used. When called after an INSERT statement:
   • If there is an AUTO_INCREMENT column 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 anINSERT ... ON DUPLICATE KEY UPDATE statement:
   • If there is an AUTO_INCREMENT column 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 of mysql_insert_id() is affected only by statements issued within the current client connection. It is not affected by statements issued by other clients.

The LAST_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 an AUTO_INCREMENT column to a specific nonspecial value. SeeInformation Functions.

mysql_insert_id() returns0 following aCALL statement for a stored procedure that generates an AUTO_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 the AUTO_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.

Note

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 cause mysql_insert_id() to return 0 after execution of commands including but not necessarily limited to COM_PING,COM_REFRESH, andCOM_INIT_DB.

Return Values

Described in the preceding discussion.

Errors

• ER_AUTO_INCREMENT_CONFLICT
  A user-specified AUTO_INCREMENT value in a multi INSERT statement falls within the range between the currentAUTO_INCREMENT value and the sum of the current and number of rows affected values.