Mysql預存程序（五）——SEQUENCE的實現

在
oracle
中，
sequence
提供多表多欄位可共用一個不重複值。
Mysql
中存在自增列，基本可以滿足
PK
的要求。但自增列存在限制：

a.
只能用於表中的一個欄位，一張不能同時存在兩個以上的自增列
;

b.
自增列必須被定義為
key
（
PK
或
FK
）
;

c.
自增列不能被多個表共用
;

d.
當
insert
語句不包括自增欄位或將其值設定為
NULL
時，該值會自動填上。

                  

在不要求欄位順序遞增的情況下，可以在
Mysql
中實現序列：

        
Sql
code 5-1:

                  
DROP TABLE IF EXISTS sequence; </p><p>-- 建sequence表，指定seq列為無符號大整型，可支援無符號值：0(default)到18446744073709551615（0到2^64–1）。<br />CREATE TABLE sequence (<br /> name VARCHAR(50) NOT NULL,<br /> current_value BIGINT UNSIGNED NOT NULL DEFAULT 0,<br /> increment INT NOT NULL DEFAULT 1,<br /> PRIMARY KEY (name) -- 不允許重複seq的存在。<br />) ENGINE=InnoDB; </p><p>DELIMITER / </p><p>DROP FUNCTION IF EXISTS currval /</p><p>CREATE FUNCTION currval(seq_name VARCHAR(50))<br />RETURNS BIGINT<br />BEGIN<br /> DECLARE value BIGINT;<br /> SELECT current_value INTO value<br /> FROM sequence<br /> WHERE upper(name) = upper(seq_name); -- 大小寫不區分.<br /> RETURN value;<br />END;<br />/</p><p>DELIMITER ; </p><p>DELIMITER /</p><p>DROP FUNCTION IF EXISTS nextval /</p><p>CREATE FUNCTION nextval (seq_name VARCHAR(50))<br />RETURNS BIGINT<br />BEGIN<br /> DECLARE value BIGINT;<br /> UPDATE sequence<br /> SET current_value = current_value + increment<br /> WHERE upper(name) = upper(seq_name);<br /> RETURN currval(seq_name);<br />END;<br />/</p><p>DELIMITER ; </p><p>DELIMITER /</p><p>DROP FUNCTION IF EXISTS setval / </p><p>CREATE FUNCTION setval (seq_name VARCHAR(50), value BIGINT)<br />RETURNS BIGINT<br />BEGIN<br /> UPDATE sequence<br /> SET current_value = value<br /> WHERE upper(name) = upper(seq_name);<br /> RETURN currval(seq_name);<br />END;<br />/</p><p>DELIMITER ;

 在
SQL
中使用序列：

        
Sql
code 5-2:

建立序列，往sequence表插入值即可：<br />mysql> insert into sequence set name='myseq';<br />查看當前已建序列：<br />mysql> select * from sequence;<br />+-------+---------------+-----------+<br />| name | current_value | increment |<br />+-------+---------------+-----------+<br />| myseq | 0 | 1 |<br />+-------+---------------+-----------+<br />1 row in set (0.00 sec)<br />獲得序列的下一個值，第一次使用，因此值為1：<br />mysql> select nextval('myseq');<br />+------------------+<br />| nextval('myseq') |<br />+------------------+<br />| 1 |<br />+------------------+<br />1 row in set (0.00 sec)

 在預存程序中使用序列（以
sql
code 1-1
中建立的
testproc
表為例），此預存程序返回插入後的
ID
，如果插入失敗，則返回
-1
：

   

sql code 5-3:

create procedure test_sequence(IN i_name VARCHAR(100),<br /> OUT o_ret BIGINT)<br />BEGIN<br /> DECLARE EXIT HANDLER FOR SQLWARNING,NOT FOUND,SQLEXCEPTION set o_ret = -1;<br /> set o_ret = nextval('myseq');<br /> INSERT INTO testproc VALUES (o_ret,i_name);<br /> INSERT INTO testproc3 VALUES (o_ret,i_name);<br />END;