[DB][MySql]關於取得自增字段的值、及@@IDENTITY與並發性問題

[DB][MySql]關於取得自增字段的值、及@@IDENTITY與並發性問題

對於剛從Oracle轉向MySql的人都會為，MySql中沒有Oracle裡的Sequence而感到困惑。MySql中沒有了Sequence，那麼MySql的主鍵用什麼方式來實現最好呢？

主要有以下幾種方式：

1、自增字段作為主鍵。【推薦方案】

MySql雖然比Oracle少了Sequence，但是多了字段的自增長特性。

插入完了以後可以通過執行【SELECT @@IDENTITY】獲取上一條插入語句中生成的自增長字段的值。

這個語句很特別，沒有關聯到特定的SQL語句，會 讓人感覺迷糊，他到底是怎麼獲取值的。在並發情況下會不會獲取其他線程執行後的值。

答案是有可能的，但是不用怕、是可控的。只有不當的編碼才會導致取到其他線程的值。先來說一下原理：

SUMMARY
The Jet OLE DB version 4.0 provider supports the SELECT @@Identity query that allows you to retrieve the value of the auto-increment field generated on your connection. Auto-increment values used on other connections to your database do not affect the results of this specialized query. This feature works with Jet 4.0 databases but not with older formats.

大致意思是【SELECT @@IDENTITY】獲取的是當前數據庫連接的前一次執行的值。其他連接執行的值不會影響當前線程。時下流行的框架（如Spring-jdbc、mybatis、hibernate）的數據庫連接都是存在ThreadLocal中的、是線程隔離的，所以不會獲取到其他線程中的【SELECT @@IDENTITY】值。當多線程編程時、強制把數據庫連接傳給各個線程同時執行時才會取到其他線程的【SELECT @@IDENTITY】。

2、在MySql中模擬Sequence

第一步：創建--Sequence 管理表

DROP TABLE IF EXISTS sequence;  
CREATE TABLE WFO_SEQ(  
         name VARCHAR(50) NOT NULL,  
         current_value INT NOT NULL,  
         increment INT NOT NULL DEFAULT 1,  
         PRIMARY KEY (name)  
) ENGINE=InnoDB;  

第二步：創建--取當前值的函數

DROP FUNCTION IF EXISTS currval;  
DELIMITER $  
CREATE FUNCTION currval (seq_name VARCHAR(50))  
         RETURNS INTEGER  
         LANGUAGE SQL  
         DETERMINISTIC  
         CONTAINS SQL  
         SQL SECURITY DEFINER  
         COMMENT ''  
BEGIN  
         DECLARE value INTEGER;  
         SET value = 0;  
         SELECT current_value INTO value  
                   FROM WFO_SEQ
                   WHERE name = seq_name;  
         RETURN value;  
END  
$  
DELIMITER ;  

第三步：創建--取下一個值的函數

DROP FUNCTION IF EXISTS nextval;    
DELIMITER $    
CREATE FUNCTION nextval (seq_name VARCHAR(50))    
         RETURNS INTEGER    
         LANGUAGE SQL    
         DETERMINISTIC    
         CONTAINS SQL    
         SQL SECURITY DEFINER    
         COMMENT ''    
BEGIN    
         DECLARE C_V INTEGER;
         
         UPDATE WFO_SEQ SET CURRENT_VALUE = CURRENT_VALUE + INCREMENT WHERE NAME = SEQ_NAME;
         
         SET    C_V = CURRVAL(SEQ_NAME);
         IF     C_V = -1 THEN 
           INSERT INTO WFO_SEQ(NAME,  CURRENT_VALUE,  INCREMENT) 
                VALUES(SEQ_NAME, 1, 1);
           RETURN 1;
         END IF;
         RETURN C_V;
END    
$    
DELIMITER ;  

第四步：創建--更新當前值的函數

DROP FUNCTION IF EXISTS setval;  
DELIMITER $  
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER)  
         RETURNS INTEGER  
         LANGUAGE SQL  
         DETERMINISTIC  
         CONTAINS SQL  
         SQL SECURITY DEFINER  
         COMMENT ''  
BEGIN  
         UPDATE WFO_SEQ
                   SET current_value = value  
                   WHERE name = seq_name;  
         RETURN currval(seq_name);  
END  
$  
DELIMITER ;  

第五步：測試函數功能

SELECT SETVAL('TestSeq', 10);---設置指定sequence的初始值
SELECT CURRVAL('TestSeq');--查詢指定sequence的當前值
SELECT NEXTVAL('TestSeq');--查詢指定sequence的下一個值