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');
————————————————
版权声明：本文为CSDN博主「Jack Weavi」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_32199769/article/details/113305671