mysql 如何设置自动ORACLE一样的增长序列 sequence

1.创建表

drop table if exists sequence;
create table sequence (
seq_name VARCHAR(50) NOT NULL, -- 序列名称
current_val INT NOT NULL DEFAULT 0, -- 当前值
increment_val INT NOT NULL DEFAULT 1, -- 步长(跨度)
PRIMARY KEY (seq_name) );

2. 插入数据
INSERT INTO sequence VALUES ('seq_test1_num1', '0', '1');
INSERT INTO sequence VALUES ('seq_test1_num2', '0', '2');

3.创建当前值
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;

4.测试当前值

 

select currval('seq_test1_num1');

5.创建下一个值

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;

6.测试下一个值

 

select nextval('seq_test1_num1');