mysql模拟oracle序列

/*****************************************

由于mysql和oracle不太一样，不支持直接的sequence，所以需要创建一张table来模拟sequence的功能

*****************************************/

/* 1. 创建--Sequence 管理表*/

DROP TABLE IF EXISTS sequence;
CREATE TABLE sequence (
  seq_name VARCHAR(50) NOT NULL  COMMENT '序列名称',
  current_value INT NOT NULL COMMENT '当前值',
  increment INT NOT NULL DEFAULT 1 COMMENT '步长',
  PRIMARY KEY (seq_name)
) ENGINE=InnoDB;

/* 2. 创建--取当前值的函数*/

DROP FUNCTION IF EXISTS currval;
DELIMITER $
CREATE FUNCTION currval(seqName VARCHAR(50))
  RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
  SQL SECURITY DEFINER
  COMMENT ''
  BEGIN
    DECLARE value INTEGER;
    SET value = 0;
    SELECT current_value INTO value
    FROM sequence
    WHERE seq_name = seqName;
    RETURN value;
  END
$
DELIMITER ;

/* 3. 创建--取下一个值的函数*/

DROP FUNCTION IF EXISTS nextval;
DELIMITER $
CREATE FUNCTION nextval (seqName VARCHAR(50))
  RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
  SQL SECURITY DEFINER
  COMMENT ''
  BEGIN
    UPDATE sequence
    SET current_value = current_value + increment
    WHERE seq_name = seqName;
    RETURN currval(seqName);
  END
$
DELIMITER ;

/* 4. 创建--更新当前值的函数*/

DROP FUNCTION IF EXISTS setval;
DELIMITER $
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER)
  RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
  SQL SECURITY DEFINER
  COMMENT ''
  BEGIN
    UPDATE sequence
    SET current_value = value
    WHERE name = seq_name;
    RETURN currval(seq_name);
  END
$
DELIMITER ;

• INSERT INTO sequence VALUES ('TestSeq', 0, 1);----添加一个sequence名称和初始值，以及自增幅度
• SELECT SETVAL('TestSeq', 10);---设置指定sequence的初始值
• SELECT CURRVAL('TestSeq');--查询指定sequence的当前值
• SELECT NEXTVAL('TestSeq');--查询指定sequence的下一个值