mysql实现sequence-高级篇

-----mysql  创建sequence
-- 创建随机数表
drop table if exists test.sequence_test_hsg; 
create table test.sequence_test_hsg (       
seqname        VARCHAR(50) NOT NULL, -- 序列名称       
currentval     INT         NOT NULL, -- 当前值       
incrementval   INT         NOT NULL    DEFAULT 1, -- 步长(跨度)       
PRIMARY KEY (seqname)) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
-- 插入随机数信息
INSERT INTO test.sequence_test_hsg VALUES ('sq_ryid', '1000000000', '1');
-- 查询
SELECT * FROM test.sequence_test_hsg;
-- 创建获取当前随机数函数
-- DROP  FUNCTION  currval;
DELIMITER $ 
CREATE FUNCTION currval (seq_name VARCHAR(50))
RETURNS INTEGER
BEGIN
  DECLARE current INTEGER;
  SET current = 0;
  SELECT currentval INTO current
  FROM  test.sequence_test_hsg
  WHERE seqname = seq_name;
  RETURN current;
END$ 
DELIMITER ;
-- 取下一个值函数
-- DROP  FUNCTION  nextval;
DELIMITER $  
CREATE FUNCTION nextval (seq_name VARCHAR(50))  
RETURNS INTEGER  
CONTAINS SQL  
BEGIN  
   UPDATE test.sequence_test_hsg  
   SET currentval = currentval + incrementval  
   WHERE seqname = seq_name;  
   RETURN currval(seq_name);  
END$  
DELIMITER ; 
-- 查询
select currval('sq_ryid');
select nextval('sq_ryid');