如何获取自增id

去"O"的业务支撑的时候,遇到一个开发询问如何获取到MySQL 的自增id,这里做一个总结,我们可以通过四种方式来获取MySQL 的自增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语句时表的自增id的值。不受其他的会话插入影响。

如果是新建的表,它的值为0

root@rac3 [yangyi]>create table t2(id int not null auto_increment primary key ,col varchar(10))

Query OK, 0 rows affected (0.11 sec)

root@rac3 [yangyi]>select last_insert_id()

+------------------+

| last_insert_id() |

+------------------+

|0 |

+------------------+

1 row in set (0.01 sec)

如果向表中插入的记录,id自增,它的值为max(id),如果指定插入的id ,它的值为上一次插入的最大值。

root@rac3 [yangyi]>insert into t3 values(null,'sss')

Query OK, 1 row affected (0.00 sec)

root@rac3 [yangyi]>select last_insert_id()

+------------------+

| last_insert_id() |

+------------------+

|1 |

+------------------+

1 row in set (0.00 sec)

root@rac3 [yangyi]>insert into t3 values(2,'sss')

Query OK, 1 row affected (0.00 sec)

root@rac3 [yangyi]>select last_insert_id()

+------------------+

| last_insert_id() |

+------------------+

|1 |

+------------------+

1 row in set (0.00 sec)

使用select last_insert_id()时要注意,当一次插入多条记录时,只是获得第一次插入的id值,务必注意！

root@rac3 [yangyi]>select last_insert_id()

+------------------+

| last_insert_id() |

+------------------+

|3 |

+------------------+

1 row in set (0.01 sec)

root@rac3 [yangyi]>insert into t1 values(null,'sss'),(null,'dd'),(null,'aaa')

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

root@rac3 [yangyi]>select * from t1

+----+------+

| id | col |

+----+------+

| 1 | sss |

| 2 | sss |

| 3 | sss |

| 4 | sss |

| 5 | dd |

| 6 | aaa |

+----+------+

6 rows in set (0.00 sec)

root@rac3 [yangyi]>select last_insert_id()

+------------------+

| last_insert_id() |

+------------------+

|4 |

+------------------+

1 row in set (0.00 sec)

last_insert_id 是与表无关的,如果向表a插入数据后,再向表b插入数据,last_insert_id 返回表b的Id值!

root@rac3 [yangyi]>insert into t2 values(null,'sss')

Query OK, 1 row affected (0.00 sec)

root@rac3 [yangyi]>select last_insert_id()

+------------------+

| last_insert_id() |

+------------------+

| 11 |

+------------------+

1 row in set (0.00 sec)

root@rac3 [yangyi]>insert into t1 values(null,'sss')

Query OK, 1 row affected (0.00 sec)

root@rac3 [yangyi]>select @@identity

+------------+

| @@identity |

+------------+

| 3 |

+------------+

1 row in set (0.00 sec)

二 使用@@identity

@@identity是表示的是最近一次向具有identity属性(即自增列)的表插入数据时对应的自增列的值,是系统定义的全局变量。

其特性last_insert_id 一样.

三 使用max(id)

select max(id) from tab返回的是tab 的最大id值。

使用max(id) 只能针对单个表获取最大的id,而且高并发情况下，必须加上'X' 锁才能获取会话当前的最大id，显然加锁会严重影响并发性能。

四 查看表结构

root@rac3 [yangyi]>show table status like 't3' \G

*************************** 1. row ***************************

Name: t3

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 2

Avg_row_length: 8192

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: 3

Create_time: 2014-05-06 20:36:00

Update_time: NULL

Check_time: NULL

Collation: utf8_bin

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

root@rac3 [yangyi]>show create table t3 \G

*************************** 1. row ***************************

Table: t3

Create Table: CREATE TABLE `t3` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`col` varchar(10) COLLATE utf8_bin DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

1 row in set (0.00 sec)

五 总结

我们知道MySQL 使用auto_increment 属性来实现自增id,这点与oracle 的sequence 不同,auto_increment 是基于表的而非全局。从oracle 到mysql 的dba 要注意改变对sequence 使用方式的转变。

在MySQL中，使用auto_increment类型的id字段作为表的主键，并用它作为其他表的外键，形成“主从表结构”，这是数据库设计中常见的用法。但是在具体生成id的时候，我们的 *** 作顺序一般是：先在主表中插入记录，然后获得自动生成的id，以它为基础插入从表的记录。这里面有个困难，就是插入主表记录后，如何获得它对应的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 LAST_INSERT_ID()得到的结果和连接2中执行select LAST_INSERT_ID()的结果是不同的而在两个连接中执行select max(id)的结果是相同的。

其实在MSSQL中SCOPE_IDENTITY()和IDENT_CURRENT()的区别和这里是类似的。使用SCOPE_IDENTITY()可以获得插入某个IDENTITY字段的当前会话的值，而使用IDENT_CURRENT()会获得在某个IDENTITY字段上插入的最大值，而不区分不同的会话。

注：使用select last_insert_id()时要注意，当一次插入多条记录时，只是获得第一次插入的id值，务必注意!可以试试

insert into tb(c1,c2) values (c1value,c2value),(c1value1,c2value2)..。

---