去"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】如何獲取自增id

相關文章