MySQL实现类似Oracle序列_sequence

数据库调优
序列 , MySql , sequence
5550 0.3~0.4 分钟 155
  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');

image.png