mysql如何实现自增序列(2)-木庄网络博客

3.创建?C取下一个值的函数

DROP FUNCTION IF EXISTS nextval; 
DELIMITER $ 
CREATE FUNCTION nextval (seq_name VARCHAR(50)) 
     RETURNS INTEGER
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT ''
BEGIN
     UPDATE sequence
          SET current_value = current_value + increment 
          WHERE name = seq_name; 
     RETURN currval(seq_name); 
END
$ 
DELIMITER ;

4.创建?C更新当前值的函数

DROP FUNCTION IF EXISTS setval; 
DELIMITER $ 
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER) 
     RETURNS INTEGER
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT ''
BEGIN
     UPDATE sequence
          SET current_value = value 
          WHERE name = seq_name; 
     RETURN currval(seq_name); 
END
$ 
DELIMITER ;

5.测试添加实例执行sql

INSERT INTO sequence VALUES ('testSeq', 0, 1);-- 添加一个sequence名称和初始值，以及自增幅度
 
SELECT SETVAL('testSeq', 10);-- 设置指定sequence的初始值
 
SELECT CURRVAL('testSeq');-- 查询指定sequence的当前值
 
SELECT NEXTVAL('testSeq');-- 查询指定sequence的下一个值