Additional languages
« 24.2.3.36 mysql_init()
24.2.3.38 mysql_kill() »
Section Navigation      [Toggle]

24.2.3.37. mysql_insert_id()

my_ulonglong mysql_insert_id(MYSQL *mysql)

Description

Returns the value generated for an AUTO_INCREMENT column by the previous INSERT statement. Use this function after you have performed an INSERT statement into a table that contains an AUTO_INCREMENT field.

The return value of mysql_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 values NULL or 0 into the column, or is an explicit non-special value.

  • In the case of a multiple-row INSERT statement, the return value of mysql_insert_id() depends on the MySQL server version.

    In MySQL 5.1.12 and later, mysql_insert_id() returns the first automatically generated AUTO_INCREMENT value that was successfully inserted. In MySQL 5.1.11 and earlier, mysql_insert_id() returns the first automatically generated AUTO_INCREMENT value, regardless of whether the insertion of that value was successful.

    If no rows are successfully inserted, then mysql_insert_id() returns 0.

  • Starting in MySQL 5.1.12, if an INSERT ... SELECT statement is executed, and no automatically generated value is successfully inserted, then mysql_insert_id() returns the id of the last inserted row.

  • Starting in MySQL 5.1.12, if an INSERT ... SELECT statement uses LAST_INSERT_ID(expr), then mysql_insert_id() returns expr.

  • INSERT statements that generate an AUTO_INCREMENT value by inserting LAST_INSERT_ID(expr) into any column.

  • INSERT statements that generate an AUTO_INCREMENT value by updating any column to LAST_INSERT_ID(expr).

  • The value of mysql_insert_id() is not affected by statements such as SELECT that return a result set.

  • If the previous statement returned an error, the value of mysql_insert_id() is undefined.

For 5.1.12 and later, the return value of mysql_insert_id() can be simplified to the following sequence:

  1. If there is an a auto increment columne, and an automatically generated value was successfully inserted, return the value of the first such value.

  2. If there was a LAST_INSERT_ID(EXPR) in the statement, return EXPR, even if there was an auto increment column in the affected table.

  3. The return value varies depending on the statement used. When called after an INSERT INTO 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, then return the last of the explicit values.

    When called after an INSERT ... ON DUPLICATE KEY statement:

    • If there is an auto increment column and the table and there were some explicit succesfully inserted values, or some updated rows, then return the last of the inserted or updated values.

Note

mysql_insert_id() returns 0 if the previous statement does not use an AUTO_INCREMENT value. If you need to save the value for later, be sure to call mysql_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.

See Section 12.11.3, “Information Functions”.

Also note that the value of the SQL LAST_INSERT_ID() function will contain the value of the first automatically generated value that was successfully inserted (starting from 5.1.12) or the first automatically generated value if any rows were successfully inserted (before 5.1.12). Another difference is that LAST_INSERT_ID() is not updated if you set an AUTO_INCREMENT column to a specific non-special value.

The reason for the difference between LAST_INSERT_ID() and mysql_insert_id() is that LAST_INSERT_ID() is made easy to use in scripts while mysql_insert_id() tries to provide a little more exact information of what happens to the AUTO_INCREMENT column.

Return Values

Described in the preceding discussion.

Errors

None.