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)