MySQL序列解決方案

MySQL自增長與Oracle序列的區別:
自增長只能用於表中的其中一個欄位
自增長只能被分配給固定表的固定的某一欄位，不能被多個表共用.
自增長會把一個未指定或NULL值的欄位自動填上.

在mysql中添加序列，請看下面的執行個體：
在MYSQL裡有這樣一張表:

CREATE TABLE Movie(id           INT NOT NULL AUTO_INCREMENT,name     VARCHAR(60) NOT NULL,released YEAR NOT NULL,PRIMARY KEY (id)) ENGINE=InnoDB;

 

INSERT INTO Movie (name,released) VALUES ('Gladiator',2000);  INSERT INTO Movie (id,name,released) VALUES (NULL,'The Bourne Identity',1998);

 

在ORACLE是這樣的:

CREATE TABLE Movie(id          INT NOT NULL,name     VARCHAR2(60) NOT NULL,released INT NOT NULL,PRIMARY KEY (id));CREATE SEQUENCE MovieSeq;

 

INSERT INTO Movie (id,name,released) VALUES (MovieSeq.NEXTVAL,'Gladiator',2000);

在oracle下為表添加一個觸發器，就可以實現mysql自增長功能:

CREATE OR REPLACE TRIGGER BRI_MOVIE_TRGBEFORE INSERT ON MovieFOR EACH ROWBEGIN  SELECT MovieSeq.NEXTVAL INTO :new.id FROM DUAL;END BRI_MOVIE_TRG;RUN;

這樣，外掛程式記錄就可以成為MYSQL風格：

INSERT INTO Movie (name,released) VALUES ('The Lion King',1994);

下面我們來看看如何在mysql資料裡使用Oracle序列文法.NEXTVAL 和 .CURVAL.
我們假設在mysql中序列的文法是:

NEXTVAL(’sequence’);CURRVAL(’sequence’);SETVAL(’sequence’,value); 

下面就是CURRRVAL的實現方案:

DROP TABLE IF EXISTS sequence;CREATE TABLE sequence (name              VARCHAR(50) NOT NULL,current_value INT NOT NULL,increment       INT NOT NULL DEFAULT 1,PRIMARY KEY (name)) ENGINE=InnoDB;INSERT INTO sequence VALUES ('MovieSeq',3,5);DROP FUNCTION IF EXISTS currval;DELIMITER $CREATE FUNCTION currval (seq_name VARCHAR(50))RETURNS INTEGERCONTAINS SQLBEGIN  DECLARE value INTEGER;  SET value = 0;  SELECT current_value INTO value  FROM sequence  WHERE name = seq_name;  RETURN value;END$DELIMITER ;

測試一下結果：

mysql> SELECT currval('MovieSeq');+---------------------+| currval('MovieSeq') |+---------------------+|                   3 |+---------------------+1 row in set (0.00 sec)mysql> SELECT currval('x');+--------------+| currval('x') |+--------------+|            0 |+--------------+1 row in set, 1 warning (0.00 sec)mysql> show warnings;+---------+------+------------------+| Level   | Code | Message          |+---------+------+------------------+| Warning | 1329 | No data to FETCH |+---------+------+------------------+1 row in set (0.00 sec)

nextval

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

 

mysql> select nextval('MovieSeq');+---------------------+| nextval('MovieSeq') |+---------------------+|                  15 |+---------------------+1 row in set (0.09 sec)mysql> select nextval('MovieSeq');+---------------------+| nextval('MovieSeq') |+---------------------+|                  20 |+---------------------+1 row in set (0.01 sec)mysql> select nextval('MovieSeq');+---------------------+| nextval('MovieSeq') |+---------------------+|                  25 |+---------------------+1 row in set (0.00 sec)

setval

 

DROP FUNCTION IF EXISTS setval;DELIMITER $CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER)RETURNS INTEGERCONTAINS SQLBEGIN   UPDATE sequence   SET          current_value = value   WHERE name = seq_name;   RETURN currval(seq_name);END$DELIMITER ;

 

mysql> select setval('MovieSeq',150);+------------------------+| setval('MovieSeq',150) |+------------------------+|                    150 |+------------------------+1 row in set (0.06 sec)mysql> select curval('MovieSeq');+---------------------+| currval('MovieSeq') |+---------------------+|                 150 |+---------------------+1 row in set (0.00 sec)mysql> select nextval('MovieSeq');+---------------------+| nextval('MovieSeq') |+---------------------+|                 155 |+---------------------+1 row in set (0.00 sec)