Rechercher dans le manuel MySQL
28.7.7.38 mysql_insert_id()
uint64_t mysql_insert_id(MYSQL *mysql)
Description
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 anAUTO_INCREMENT
column. This is true whether the value is automatically generated by storing the special valuesNULL
or0
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 generatedAUTO_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 usesLAST_INSERT_ID(
,expr
)mysql_insert_id()
returnsexpr
.INSERT
statements that generate anAUTO_INCREMENT
value 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 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, returnexpr
)expr
, even if there was anAUTO_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 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.
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
Section 12.15, “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.
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
, and
COM_INIT_DB
.
A user-specified
AUTO_INCREMENT
value in a multiINSERT
statement falls within the range between the currentAUTO_INCREMENT
value and the sum of the current and number of rows affected values.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-mysql-insert-id.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.