MySQL 5.6 C API Developer Guide
my_ulonglong mysql_insert_id(MYSQL *mysql)
Returns the value generated for an
AUTO_INCREMENT
column by the previous
INSERT
or
UPDATE
statement. Use this
function after you have performed an
INSERT
statement into a table
that contains an AUTO_INCREMENT
field, or
have used INSERT
or
UPDATE
to set a column value
with
LAST_INSERT_ID(
.
expr
)
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 nonspecial value.
In the case of a multiple-row
INSERT
statement,
mysql_insert_id()
returns
the first automatically generated
AUTO_INCREMENT
value that was
successfully inserted.
If no rows are successfully inserted,
mysql_insert_id()
returns
0.
If an
INSERT ...
SELECT
statement is executed, and no
automatically generated value is successfully inserted,
mysql_insert_id()
returns
the ID of the last inserted row.
If an
INSERT ...
SELECT
statement uses
LAST_INSERT_ID(
,
expr
)mysql_insert_id()
returns
expr
.
INSERT
statements that
generate an AUTO_INCREMENT
value by
inserting
LAST_INSERT_ID(
into any column or by updating any column to
expr
)LAST_INSERT_ID(
.
expr
)
If the previous statement returned an error, the value of
mysql_insert_id()
is
undefined.
The return value of
mysql_insert_id()
can be
simplified to the following sequence:
If there is an AUTO_INCREMENT
column,
and an automatically generated value was successfully
inserted, return the first such value.
If
LAST_INSERT_ID(
occurred in the statement, return
expr
)expr
, even if there was an
AUTO_INCREMENT
column in the affected
table.
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 an
INSERT
... 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()
returns
0
if the previous statement does not use an
AUTO_INCREMENT
value. If you must 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.
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 from
mysql_insert_id()
is that
LAST_INSERT_ID()
is not updated
if you set an AUTO_INCREMENT
column to a
specific nonspecial value. See
Information Functions.
mysql_insert_id()
returns
0
following a
CALL
statement for a stored
procedure that generates an AUTO_INCREMENT
value because in this case
mysql_insert_id()
applies to
CALL
and not the statement
within the procedure. Within the procedure, you can use
LAST_INSERT_ID()
at the SQL
level to obtain the AUTO_INCREMENT
value.
The reason for the differences 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 more exact information about what happens to the
AUTO_INCREMENT
column.