MySQL---ORACLE序列解决方案

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;   

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;   


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

这样，插件记录就可以成为MYSQL风格：


下面我们来看看如何在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 AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 

INSERT INTO sequence VALUES ('PROPOSAL_NO',100000,1); 

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 ;  

demo: SELECT currval('PROPOSAL_NO'); 

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 ;  


demo :  select nextval('PROPOSAL_NO');     

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 ; 

demo: select setval('PROPOSAL_NO',150);