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_TRG   
BEFORE INSERT ON Movie   
FOR EACH ROW   
BEGIN   
  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 INTEGER   
CONTAINS SQL   
BEGIN   
  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 INTEGER   
CONTAINS SQL   
BEGIN   
   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 INTEGER   
CONTAINS SQL   
BEGIN   
   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)