How to Get the Unique ID for the Last Inserted Row
If you insert a record into a table that contains an AUTO_INCREMENT
column, you can obtain the value stored into that column by calling the mysql_insert_id()
function.
You can check from your C applications whether a value was stored in an AUTO_INCREMENT
column by executing the following code (which assumes that you've checked that the statement succeeded). It determines whether the query was an INSERT
with an AUTO_INCREMENT
index:
if ((result = mysql_store_result(&mysql)) == 0 &&
mysql_field_count(&mysql) == 0 &&
mysql_insert_id(&mysql) != 0)
{
used_id = mysql_insert_id(&mysql);
}
When a new AUTO_INCREMENT
value has been generated, you can also obtain it by executing a SELECT LAST_INSERT_ID()
statement with mysql_query()
and retrieving the value from the result set returned by the statement.
When inserting multiple values, the last automatically incremented value is returned.
For LAST_INSERT_ID()
, the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT
column with a nonmagic value (that is, a value that is not NULL
and not 0
). Using LAST_INSERT_ID()
and AUTO_INCREMENT
columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed.
If you want to use the ID that was generated for one table and insert it into a second table, you can use SQL statements like this:
INSERT INTO foo (auto,text)
VALUES(NULL,'text'); # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
VALUES(LAST_INSERT_ID(),'text'); # use ID in second table
mysql_insert_id()
returns the value stored into an AUTO_INCREMENT
column, whether that value is automatically generated by storing NULL
or 0
or was specified as an explicit value. LAST_INSERT_ID()
returns only automatically generated AUTO_INCREMENT
values. If you store an explicit value other than NULL
or 0
, it does not affect the value returned by LAST_INSERT_ID()
.
For more information on obtaining the last ID in an AUTO_INCREMENT
column:
-
For information on
LAST_INSERT_ID()
, which can be used within an SQL statement, see Section 12.15, “Information Functions”. -
For information on
mysql_insert_id()
, the function you use from within the C API, see Section 28.7.7.38, “mysql_insert_id()”. -
For information on obtaining the auto-incremented value when using Connector/J, see Retrieving
AUTO_INCREMENT
Column Values through JDBC. -
For information on obtaining the auto-incremented value when using Connector/ODBC, see Obtaining Auto-Increment Values.
注:
LAST_INSERT_ID支持并发:
LAST_INSERT_ID是基于Connection的,只要每个线程都使用独立的Connection对象,LAST_INSERT_ID函数将返回该Connection对AUTO_INCREMENT列最新的insert or update*作生成的第一个record的ID。这个值不能被其它客户端(Connection)影响,保证了你能够找回自己的 ID 而不用担心其它客户端的活动,而且不需要加锁。使用单INSERT语句插入多条记录, LAST_INSERT_ID返回一个列表。
max(id) 在并发的情况下获取到的数据不准确
转载:https://dev.mysql.com/doc/refman/8.0/en/getting-unique-id.html