MySQL模拟Oracle序列sequence

没发现，这么多同学有这个需求，把文档补充一下，其实就是建立1个表，和2个存储过程。利用表的行级锁模拟每一个表的序列增减。
 

---

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 ;

测试就比较简单了

select nextval('aaaa') from dual;