mysql创建序列

create table sequence(--创建序列表
   name varchar(50) not null primary key,
   current_value BIGINT not null DEFAULT 0,
   increment int not null DEFAULT 1,
   max_value BIGINT,  -- 最大值
   initial_value BIGINT, -- 初始值，当当前值大于最大值，回到初始值
   key (name)
);

DELIMITER ;; 
CREATE FUNCTION nextval (seq_name VARCHAR(50))  
RETURNS BIGINT  
CONTAINS SQL  
BEGIN  
   UPDATE sequence  
   SET current_value = current_value + increment  
   WHERE name = seq_name;  
   RETURN currval(seq_name);  
END;;  


drop function currval;
DELIMITER ;; 
CREATE FUNCTION currval (seq_name VARCHAR(50))  
RETURNS INTEGER  
CONTAINS SQL  
BEGIN  
  SELECT current_value,max_value,initial_value INTO @current_value, @max_value, @initial_value
  FROM sequence  
  WHERE name = seq_name;
  if(@current_value>@max_value) then 
     UPDATE sequence  
     SET current_value = initial_value  
     WHERE name = seq_name;
     set @current_value=@initial_value;
  end if;
  RETURN @current_value;  
END;;

--查询所有序列
SELECT * from sequence;
--使用序列
SELECT nextval('sq_test_id');