AUTO_INCREMENT和LAST_INSERT_ID [英] AUTO_INCREMENT and LAST_INSERT_ID

问题描述

我正在使用AUTO_INCREMENT，我想获取插入行的ID，以便可以使用ID作为2个表之间的公共字段来更新另一个表.

I'm using AUTO_INCREMENT and I would like to get that ID of inserted row so that I could update another table using ID as common field between the 2 tables.

我知道LAST_INSERT_ID将获得最后的ID.但是，我担心的是，许多用户同时访问数据库.因此，可能会有另一个进程访问该表并同时插入新行.

I understood that LAST_INSERT_ID will get last ID. However, my concern is that, the database is accessed at same time by many users. hence, there might be another process accessed the table and also inserted a new row at same time.

LAST_INSERT_ID是否仅返回最后一个ID，而与所使用的连接无关，还是仅返回我正在使用的连接的最后一个ID?

Does LAST_INSERT_ID return just the last ID regardless of the connection used, or only return last ID for the connection that I'm using?

注意，我正在使用tomcat服务器中的连接池访问mySql数据库.

Notice, I'm accessing mySql database using connection pool in tomcat server.

在夏季，我需要在表A中插入自动递增的行，而不是在表B中插入行，而表B需要使用自动递增值链接到表A.

in summery, I need to insert a row in Table A with auto increment, than I need to insert row in table B, which need to be linked to table A using the auto increment value.

推荐答案

SELECT max(employeeid) FROM Employee;

上面的查询返回Employee表中最后插入的记录的employeeid的值，因为employeeid是一个自动递增列.这似乎可以，但是假设两个线程正在同时执行插入操作，那么您可能会得到错误的最后插入记录的ID！不用担心，MySQL提供了一个函数，该函数可返回最后插入的记录的自动增量列的值.

The above query returns the value of employeeid of last inserted record in Employee table because employeeid is an auto increment column. This seems to be OK, but suppose two threads are executing insert operation simultaneously, there is a chance that you get wrong id of last inserted record! Don’t worry, MySQL provides a function which returns the value of auto increment column of last inserted record.

SELECT LAST_INSERT_ID();

LAST_INSERT_ID()始终是特定于连接的，这意味着即使从不同的连接同时执行插入操作，它也总是返回当前特定于连接的操作的值.因此，您必须首先在Employee表中插入记录，运行上面的查询以获取id值，然后使用它插入第二个表中

LAST_INSERT_ID() is always connection specific, this means even if insert operation is carried out simultaneously from different connections, it always returns the value of current connection specific operation. So you have to first insert record in Employee table, run the above query to get the id value and use this to insert in second table

这篇关于AUTO_INCREMENT和LAST_INSERT_ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！