mysql通过表和function模拟oracle的sequence

1、新建表

CREATE TABLE `sequence` (
  `seq_name` varchar(45) NOT NULL COMMENT 'sequence名',
  `seq_value` bigint(20) NOT NULL COMMENT 'sequence值',
  `increment` int(3) NOT NULL DEFAULT '1' COMMENT '步进',
  PRIMARY KEY (`seq_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

2、插入初始数据

INSERT INTO `sequence` VALUES ('SEQ_CERT', '26', '1');

3、编写function

delimiter //

CREATE FUNCTION nextValue (s varchar(50)) RETURNS int(10)

BEGIN
    DECLARE r integer;
    set r = 0;
    update sequence set seq_value = seq_value + increment where seq_name = s;
    select seq_value into r from sequence where seq_name = s; 
    return r;
END

//
delimiter ;

4、然后就可以使用function获取数据。

select nextValue('SEQ_CERT');