MYSQL的自增ID

標籤：

  當你插入A表一條資料，插入B表的資料時需要添加對應A表中對應欄位的自增值，你會怎麼擷取到A表的自增值呢？那下面來介紹你可能不知道MySQL裡的自增值。

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的自增ID