oracle 模拟 mysql,mysql透过表和function模拟oracle的sequence

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