MySQL序列解决方案

MySQL自增长与Oracle序列的区别:
自增长只能用于表中的其中一个字段
自增长只能被分配给固定表的固定的某一字段，不能被多个表共用.
自增长会把一个未指定或NULL值的字段自动填上.
在mysql中添加序列，请看下面的实例：
在MYSQL里有这样一张表:
 
Java代码
 
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;
 
Java代码
 
INSERT INTO Movie (name,released) VALUES ('Gladiator',2000);  
INSERT INTO Movie (id,name,released) VALUES (NULL,'The Bourne Identity',1998);  
INSERT INTO Movie (name,released) VALUES ('Gladiator',2000);
INSERT INTO Movie (id,name,released) VALUES (NULL,'The Bourne Identity',1998);
 
在ORACLE是这样的:
 
Java代码
 
CREATE TABLE Movie(  
id INT NOT NULL,  
name VARCHAR2(60) NOT NULL,  
released INT NOT NULL,  
PRIMARY KEY (id)  
);  
 
CREATE SEQUENCE MovieSeq;  
 
CREATE TABLE Movie(
id INT NOT NULL,
name VARCHAR2(60) NOT NULL,
released INT NOT NULL,
PRIMARY KEY (id)
);
 
CREATE SEQUENCE MovieSeq;
 
Java代码
 
INSERT INTO Movie (id,name,released) VALUES (MovieSeq.NEXTVAL,'Gladiator',2000);  
INSERT INTO Movie (id,name,released) VALUES (MovieSeq.NEXTVAL,'Gladiator',2000);
 
在oracle下为表添加一个触发器，就可以实现mysql自增长功能:
 
Java代码
 
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;  
 
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风格：
 
Java代码
 
INSERT INTO Movie (name,released) VALUES ('The Lion King',1994);  
INSERT INTO Movie (name,released) VALUES ('The Lion King',1994);
 
下面我们来看看如何在mysql数据里使用Oracle序列语法.NEXTVAL 和 .CURVAL.
我们假设在mysql中序列的语法是:
 
NEXTVAL(’sequence’);
CURRVAL(’sequence’);
SETVAL(’sequence’,value);
 
下面就是CURRRVAL的实现方案:
 
Java代码
 
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 =