在SQLServer/MySQL資料庫中如何取得剛插入的標識值

 

在SQLServer/MySQL資料庫中如何取得剛插入的標識值

在SQLServer資料庫中

資料庫實際應用中，我們往往需要得到剛剛插入 的標誌值來往相關表中寫入資料。但我們平常得到的真的是我們需要的那個值嗎？
（1）、有時我們會使用 SELECT @@Identity 來獲得我們剛剛插入的值，比如下面的代碼

代碼一：
use tempdb
if exists (select * from sys.objects where object_id = object_id(N'[test1]') and type in (N'u'))
drop table [test1]
go
create table test1
(
id int identity(1,1),
content nvarchar(100)
)
insert into test1 (content) values ('solorez')
select @@identity

樂觀情況下，這樣做是沒問題的，但如果我們如果先運行下面的代碼二建立一個觸發器、再運行代碼三：

代碼二：
create table test2
(
id int identity(100,1),
content nvarchar(100)
)

create trigger tri_test1_identitytest_I
on test1 after insert
as
begin
insert into test2
select content from inserted
end

代碼三：
insert into test1 (content) values ('solorez2')
select @@identity
 
我們可以看到，此時得到的標識值已經是100多了，很明顯，這是表test2的產生的標識值，已經不是我們想要的 了。
我們可以看看@@identity的定義：Identity
原來，@@identity返回的是當前事務最後插入的標識值，因為在Insert Test1表執行後，緊接著觸發了觸發器又Insert Test2表，所以，這時候@@identity的值就是表test2的產生的標識值。

（2）、這 時我們或許會用下面的方法：

代碼四：
insert into test1 (content) values ('solorez3')
SELECT IDENT_CURRENT('test1')

看來結果還比較正確，但如果我們在多次運行代碼四的同時運行下面的代碼五：

代碼五：
insert into test1 (content) values ('solorez3')

waitfor delay '00:00:20'
SELECT IDENT_CURRENT('test1')
 
結果又 不是我們想要的了！
再看看IDENT_CURRENT(Tablename) 的定義：IDENT_CURRENT(Tablename)
是 返回指定表的最後標識值。

（3）、到這裡，是該亮出答案的時候了，我們可以使用下面的代碼：

代碼六：
insert into test1 (content) values ('solorez3')
SELECT scope_identity()

這時，我們無論是添加觸發器還是運行並行插入，得到的始終是當前事務的標識值。

scope_identity()的定義：scope_identity()返回為當前會話和當前範圍中的某個表產生的最新標識值

 

三個函數的區別：

IDENT_CURRENT 返回為某個會話和用域中的指定表產生的最新標識值。

@@IDENTITY 返回為跨所有範圍的當前會話中的某個表產生的最新標識值。

SCOPE_IDENTITY 返回為當前會話和當前範圍中的某個表產生的最新標識值。

 

 

在MySQL資料庫中

一般情況下擷取剛插入的資料的id，使用select max(id) from table 是可以的。

但在多線程情況下，就不行了。

下面介紹三種方法

(1)   getGeneratedKeys()方法:

(2)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 而不用擔心其它用戶端的活動，而且不需要加鎖。

可以用 SELECT LAST_INSERT_ID(); 查詢LAST_INSERT_ID的值.

使用單INSERT語句插入多條記錄, LAST_INSERT_ID只返回插入的第一條記錄產生的值.

 (3)select @@IDENTITY:

String sql=”select @@IDENTITY”;

@@identity是表示的是最近一次向具有identity屬性(即自增列)的表插入資料時對應的自增列的值，是系統定義的全域變數。一般系統定義的全域變數都是以@@開頭，使用者自訂變數以@開頭。比如有個表A，它的自增列是id，當向A表插入一行資料後，如果插入資料後自增列的值自動增加至101，則通過select @@identity得到的值就是101。使用@@identity的前提是在進行insert操作後，執行select @@identity的時候串連沒有關閉，否則得到的將是NULL值。