MySQL模拟Oracle序列sequence-优快云博客

本文介绍了一个自增序列的实现方案，通过创建一个包含序列名称、当前值和增量字段的表，并定义了currval和nextval两个函数来获取当前值和递增下一个值。

DROP TABLE IF EXISTS sys_sequence ;

CREATE TABLE sys_sequence (
seq_name VARCHAR (50) NOT NULL,
curr_value BIGINT NOT NULL DEFAULT 0,
increment_by INT NOT NULL DEFAULT 1,
PRIMARY KEY (seq_name)
) ENGINE = INNODB ;

INSERT INTO sys_sequence VALUES ('SEQ_TEST_NO',10000,1);

DELIMITER $$
DROP FUNCTION IF EXISTS currval $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    FUNCTION currval(v_seq_name VARCHAR (50))
    RETURNS BIGINT
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
   DECLARE v_currval BIGINT;
      SET v_currval = 1 ;
      SELECT
        curr_value INTO v_currval
      FROM
        sys_sequence
      WHERE seq_name = v_seq_name ;
      RETURN v_currval ;
    END$$

DELIMITER ;

-- SELECT `currval`('SEQ_TEST_NO');

DELIMITER $$
DROP FUNCTION IF EXISTS `nextval` $$

CREATE FUNCTION `nextval` (`v_seq_name` VARCHAR (50)) RETURNS BIGINT (20) CONTAINS SQL
BEGIN
UPDATE
    sys_sequence
SET
    `curr_value` = last_insert_id(`curr_value` + `increment_by`)
WHERE `seq_name` = v_seq_name ;
RETURN last_insert_id();
END $$

DELIMITER ;