mysql实现oracle序列_mysql实现oracle序列

mysql实现oracle序列的方案

1.建表,表结构为:

drop table if exists sequence;

create table sequence (

seq_name VARCHAR(50) NOT NULL, -- 序列名称

current_val INT NOT NULL, --当前值

increment_val INT NOT NULL DEFAULT 1, --步长(跨度)

PRIMARY KEY (seq_name)

);

2.实现currval的模拟方案

create function currval(v_seq_name VARCHAR(50))

returns integer

begin

declare value integer;

set value = 0;

select current_val into value

from sequence

where seq_name = v_seq_name;

return value;

end;

3.实现nextval的模拟方案

create function nextval (v_seq_name VARCHAR(50))

returns integer

begin

update sequence

set current_val = current_val + increment_val

where seq_name = v_seq_name;

return currval(v_seq_name);

end;

4.增加设置值的函数

create function setval(v_seq_name VARCHAR(50), v_new_val INTEGER)

returns integer

begin

update sequence

set current_val = v_new_val

where seq_name = v_seq_name;

return currval(seq_name);

end;

5.实例

insert into sequence (seq_name, current_val) values ('seq1', 100);

#获取当前值

select currval('seq1');

#获取下一个值

select nextval('seq1');