MySQL实现类似Oracle序列_sequence

1. 创建一张sequence表,并给定初始值

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

INSERT INTO sequence (seq_name, current_val, increment_val) VALUES ('test', '0', '1');

2.创建一个函数,用于更新并获取sequence表中的当前值

create function currval(v_seq_name VARCHAR(50))  
returns integer 
begin 
	declare value integer;  
	set value = 0;  
  update sequence 
  set current_val = (current_val+increment_val)  
  where seq_name = v_seq_name; -- 先更新当前值
  select current_val into value  
	from sequence 
	where seq_name = v_seq_name;   -- 在获取当前值
  return value;
end;

3.使用序列

SELECT currval('test');