MySQL序列解决方案

MySQL自增长与Oracle序列的区别: 自增长只能用于表中的其中一个字段 自增长只能被分配给固定表的固定的某一字段，不能被多个表共用. 自增长会把一个未指定或NULL值的字段自动填上. 在mysql中添加序列，请看下面的实例： 在MYSQL里有这样一张表:

CREATETABLEMovie(
idINTNOTNULLAUTO_INCREMENT,
nameVARCHAR(60)NOTNULL,
releasedYEARNOTNULL,
PRIMARYKEY(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;

INSERTINTOMovie(name,released)VALUES('Gladiator',2000);
INSERTINTOMovie(id,name,released)VALUES(NULL,'TheBourneIdentity',1998);

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

在ORACLE是这样的:

CREATETABLEMovie(
idINTNOTNULL,
nameVARCHAR2(60)NOTNULL,
releasedINTNOTNULL,
PRIMARYKEY(id)
);
CREATESEQUENCEMovieSeq;

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

INSERTINTOMovie(id,name,released)VALUES(MovieSeq.NEXTVAL,'Gladiator',2000);

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

在oracle下为表添加一个触发器，就可以实现mysql自增长功能:

CREATEORREPLACETRIGGERBRI_MOVIE_TRG
BEFOREINSERTONMovie
FOREACHROW
BEGIN
SELECTMovieSeq.NEXTVALINTO:new.idFROMDUAL;
ENDBRI_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风格：

INSERTINTOMovie(name,released)VALUES('TheLionKing',1994);

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

下面我们来看看如何在mysql数据里使用Oracle序列语法.NEXTVAL 和 .CURVAL. 我们假设在mysql中序列的语法是:

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

下面就是CURRRVAL的实现方案:

DROPTABLEIFEXISTSsequence;
CREATETABLEsequence(
nameVARCHAR(50)NOTNULL,
current_valueINTNOTNULL,
incrementINTNOTNULLDEFAULT1,
PRIMARYKEY(name)
)ENGINE=InnoDB;
INSERTINTOsequenceVALUES('MovieSeq',3,5);
DROPFUNCTIONIFEXISTScurrval;
DELIMITER$
CREATEFUNCTIONcurrval(seq_nameVARCHAR(50))
RETURNSINTEGER
CONTAINSSQL
BEGIN
DECLAREvalueINTEGER;
SETvalue=0;
SELECTcurrent_valueINTOvalue
FROMsequence
WHEREname=seq_name;
RETURNvalue;
END$
DELIMITER;

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>SELECTcurrval('MovieSeq');
+---------------------+
|currval('MovieSeq')|
+---------------------+
|3|
+---------------------+
1rowinset(0.00sec)
mysql>SELECTcurrval('x');
+--------------+
|currval('x')|
+--------------+
|0|
+--------------+
1rowinset,1warning(0.00sec)
mysql>showwarnings;
+---------+------+------------------+
|Level|Code|Message|
+---------+------+------------------+
|Warning|1329|NodatatoFETCH|
+---------+------+------------------+
1rowinset(0.00sec)

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

DROPFUNCTIONIFEXISTSnextval;
DELIMITER$
CREATEFUNCTIONnextval(seq_nameVARCHAR(50))
RETURNSINTEGER
CONTAINSSQL
BEGIN
UPDATEsequence
SETcurrent_value=current_value+increment
WHEREname=seq_name;
RETURNcurrval(seq_name);
END$
DELIMITER;

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>selectnextval('MovieSeq');
+---------------------+
|nextval('MovieSeq')|
+---------------------+
|15|
+---------------------+
1rowinset(0.09sec)

mysql>selectnextval('MovieSeq');
+---------------------+
|nextval('MovieSeq')|
+---------------------+
|20|
+---------------------+
1rowinset(0.01sec)

mysql>selectnextval('MovieSeq');
+---------------------+
|nextval('MovieSeq')|
+---------------------+
|25|
+---------------------+
1rowinset(0.00sec)

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

DROPFUNCTIONIFEXISTSsetval;
DELIMITER$
CREATEFUNCTIONsetval(seq_nameVARCHAR(50),valueINTEGER)
RETURNSINTEGER
CONTAINSSQL
BEGIN
UPDATEsequence
SETcurrent_value=value
WHEREname=seq_name;
RETURNcurrval(seq_name);
END$
DELIMITER;

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>selectsetval('MovieSeq',150);
+------------------------+
|setval('MovieSeq',150)|
+------------------------+
|150|
+------------------------+
1rowinset(0.06sec)

mysql>selectcurval('MovieSeq');
+---------------------+
|currval('MovieSeq')|
+---------------------+
|150|
+---------------------+
1rowinset(0.00sec)

mysql>selectnextval('MovieSeq');
+---------------------+
|nextval('MovieSeq')|
+---------------------+
|155|
+---------------------+
1rowinset(0.00sec)