MySQL下建立序列及建立自訂函數方法介紹

標籤：style   blog   http   color   io   ar   使用   java   for   

        工作過程中需要將基於DB2資料庫的應用以及資料移轉到MySQL中去，在原應用中，大量使用了SEQUENCE，考慮盡量減少代碼的修改，決定在遷移後的應用中繼續保留SEQUENCE的使用，這就要求在MySQL中尋找替代SEQUENCE的解決方案。

        在DB2中建立一個SEQUENCE的方法如下：

DROP SEQUENCE TRZ_MEMBER.SEQ_TRZ_MEMBER_NO;CREATE SEQUENCE TRZ_MEMBER.SEQ_TRZ_MEMBER_NOAS BIGINTINCREMENT BY 1START WITH 10000000000MAXVALUE 99999999999NO CYCLECACHE 20ORDER;

MySQL自增長與Oracle（DB2）序列的區別:

自增長只能用於表中的其中一個欄位；

自增長只能被分配給固定表的固定的某一欄位，不能被多個表共用；

自增長會把一個未指定或NULL值的欄位自動填上。

 

要想在MySQL中替代SEQUENCE功能需要做一下幾件事：

1：建立SEQUENCE表，儲存多條SEQUENCE資訊；

2：完成自訂函數的定義，在程式中通過該函數完成組建的序列的擷取；

 

接下來將介紹兩種產生方式，一種是非並發方式，一種是並發方式，前一種不能夠處理並發訪問中存在的問題，後一種則能夠處理，兩種方式的第一步都相同，就是建立SEQUENCE表：

 

非並發方式：

        一：建立SEQUENCE表：

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

 

        該表用來儲存多條sequence資訊，每條sequence為一個序列，其效果等同於之前介紹的DB2中的sequence。假設需要替換DB2中的SEQ_TRZ_MEMBER_NO（如前定義），則插入MySQL中表sequence的插入語句如下：

INSERT INTO sequence VALUES (‘SEQ_TRZ_MEMBER_NO‘,10000000000,1);

       

        二：建立MySQL自訂函數，用以擷取當前sequence值：

1：首先明確的是，自訂函數是對MySQL提供的函數庫的一種補充，用來完成自訂功能，建立MySQL函數必須通過MySQL Commond Line鍵入命令列的方式進行建立，而不能通過第三方提供的圖形化資料庫操作軟體來建立；

2：首先提供一個範例，好有一個總體的認識：

DELIMITER $$DROP FUNCTION IF EXISTS currval;  CREATE FUNCTION currval (seq_name VARCHAR(50))  RETURNS BIGINT  BEGIN    DECLARE c_value BIGINT DEFAULT  0;    SET c_value = 0;    SELECT current_value into c_value  FROM sequence    WHERE name = seq_name;    RETURN c_value;  END $$DELIMITER ;

該函數的功能為返回指定序列的當前值。

 

其中第一行代碼（DELIMITER $$ ）定義一個結束標識符，因為MySQL預設是以分號作為SQL語句的結束符的，而函數體內部要用到分號，所以會跟預設的SQL結束符發生衝突，所以需要先定義一個其他的符號作為SQL的結束符。沒有加這個定義的話會報如下錯誤：

錯誤碼: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘end‘ at line 1

 

第二行（DROP FUNCTION IF EXISTS currval; ）是刪除同名的類，不然如果已經存在了同名函數，會報如下錯誤：

錯誤碼: 1304 FUNCTION currval already exists

 

第三，第四行定義函數名稱和函數傳回值；

而函數體必須定義在Begin和End中間。

通過MySQL Commond Line執行後效果如下所示：

 

通過如下語句驗證效果：

該函數只完成了擷取當前序列值的作用，還需要定義一個函數來完成擷取下一個序列值的功能，代碼如下所示：

DROP FUNCTION IF EXISTS nextval;  DELIMITER $$  CREATE FUNCTION nextval (seq_name VARCHAR(50))  RETURNS BIGINT  CONTAINS SQL  BEGIN     UPDATE sequence     SET          current_value = current_value + increment     WHERE name = seq_name;     RETURN currval(seq_name);  END $$  DELIMITER ;

該函數用來擷取下一個序列值，在MySQL Commond Line中執行後的結果如下所示：

通過如下語句驗證函式是否生效：

SELECT NEXTVAL(‘SEQ_TRZ_MEMBER_NO‘);

至此非並發方式的sequence產生方式就實現完了。要想使得sequence的產生能夠處理並發的方式，只需要少做修改即可。

 

並發產生方式：

sequence的並發產生方式同非並發產生方式都需要建立sequence表，如下：

一：建立SEQUENCE表：

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

插入定義的序列：

INSERT INTO sequence VALUES (‘SEQ_TRZ_MEMBER_NO‘,10000000000,1);

 

二：自訂函數實現：

DROP FUNCTION IF EXISTS seq;  DELIMITER $$  CREATE FUNCTION seq(seq_name char (20)) returns BIGINTBEGIN UPDATE sequence SET current_value=last_insert_id(current_value+increment) WHERE name=seq_name; RETURN last_insert_id();END $$DELIMITER;

 

函數內部調用了MySQL內部提供的last_insert_id（）函數完成並發控制。

而有關於last_insert_id的相關資料請參考：
http://it.100xuexi.com/view/otdetail/20120619/73a6cc8f-36b8-4b70-8904-57c18d3ab385.html

 

文中代碼部分引自：http://meetrice.iteye.com/blog/89426

http://www.blogjava.net/Skynet/archive/2011/03/23/301847.html

MySQL下建立序列及建立自訂函數方法介紹