[DB][MySql]關於取得自增欄位的值、及@@IDENTITY 與並發性問題，mysql@@identity

[DB][MySql]關於取得自增欄位的值、及@@IDENTITY 與並發性問題，mysql@@identity

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

主要有以下幾種方式：

1、自增欄位作為主鍵。【推薦方案】

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

     插入完了以後可以通過執行【SELECT @@IDENTITY】擷取上一條插入語句中產生的自增長欄位的值。

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

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

SUMMARYThe 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的下一個值