虫二的专栏~~在路上~~~

河流之所以能够到达目的地,是因为它懂得怎样避开障碍。走过路过,别忘留言支持呀。...

MySQL模拟Oracle序列sequence

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 ;


阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/xzknet/article/details/78679355
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭