MYSQL的自增ID

MYSQL的自增ID

MYSQL获取自增ID的四种方法
1. select max(id) from tablename
2.SELECT LAST_INSERT_ID() 函数
LAST_INSERT_ID 是与table无关的，如果向表a插入数据后，再向表b插入数据，LAST_INSERT_ID会改变。
在多用户交替插入数据的情况下max(id)显然不能用。这时就该使用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返回一个列表。
3. select @@IDENTITY;
@@identity 是表示的是最近一次向具有identity属性(即自增列)的表插入数据时对应的自增列的值是系统定义的全局变量。一般系统定义的全局变量都是以@@开头，用户自定义变量以@开头。
比如有个表A，它的自增列是id，当向A表插入一行数据后，如果插入数据后自增列的值自动增加至101，则通过select @@identity得到的值就是101。使用@@identity的前提是在进行insert操作后，执行select @@identity的时候连接没有关闭，否则得到的将是NULL值
4. SHOW TABLE STATUS;
得出的结果里边对应表名记录中有个Auto_increment字段，里边有下一个自增ID的数值就是当前该表的最大自增ID.
@@identity和LAST_INSERT_ID()区别
mysql5.5的chm帮助是这么说的:
identity
This variable is a synonym for the last_insert_id variable. It exists for compatibility with other database systems. You can read its value with SELECT @@identity, and set it using SET identity.
再看看:
last_insert_id
The value to be returned from LAST_INSERT_ID(). This is stored in the binary log when you use LAST_INSERT_ID() in a statement that updates a table. Setting this variable does not update the value returned by the mysql_insert_id() C API function.
@@identity是LAST_INSERT_ID() 的同义词，没有什么很大的差别，但是继续看下面对LAST_INSERT_ID()理解。
last_insert_id()新理解
官方的说明:The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.
last_insert_id()函数的返回值不是基于整个数据库的插入语句。
而是基于单个连接客户端之间所执行的insert语句最近一条，而且客户端之间是不会影响的，它是连接级别的函数，只对当前用户的连接有效。
在MySQL中，使用auto_increment类型的id字段作为表的主键。
通常的做法，是通过“select max(id) from tablename”的做法，但是显然这种做法需要考虑并发的情况，需要在事务中对主表加以“X锁“，待获得max(id)的值以后，再解锁。
这种做法需要的步骤比较多，有些麻烦，而且并发性也不好。有没有更简单的做法呢？答案之一是通过select LAST_INSERT_ID()这个操作。
乍一看，它和select max(id)很象，但实际上它是线程安全的。也就是说它是具体于数据库连接的。下面通过实验说明：
(1)、在连接1中向A表插入一条记录，A表包含一个auto_increment类型的字段。
(2)、在连接2中向A表再插入一条记录。
(3)、结果：在连接1中执行select 得到的结果和连接2中执行select LAST_INSERT_ID()的结果是不同的；而在两个连接中执行select max(id)的结果是相同的。LAST_INSERT_ID()
其实在MSSQL中SCOPE_IDENTITY()和IDENT_CURRENT()的区别和这里是类似的。使用SCOPE_IDENTITY()可以获得插入某个IDENTITY字段的当前会话的值而使用IDENT_CURRENT()会获得在某个IDENTITY字段上插入的最大值而不区分不同 的会话。
结论：@@identity是LAST_INSERT_ID() 的同义词，但是@@identity 系统级别的变量，而LAST_INSERT_ID()是用户变量（连接级别变量）相对更加安全，建议使用LAST_INSERT_ID()
参考网址：
MYSQL获取自增ID的四种方法：http://www.2cto.com/database/201304/199707.html
对mysql中last_insert_id()的新理解：http://sucre.blog.51cto.com/1084905/723808
@@identity和LAST_INSERT_ID()区别:http://bbs.csdn.net/topics/390659372
MYSQL中文参考手册:http://www.yesky.com/imagesnew/software/mysql/manual_toc.html
MYSQL的自增ID2024-07-20