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');