创建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 VALUES ('num1', '0', '1');
INSERT INTO sequence VALUES ('num2', '0', '2');

创建currval函数

DELIMITER $$
DROP FUNCTION IF EXISTS currval$$
CREATE FUNCTION currval(v_seq_name varchar(20)) RETURNS integer
BEGINdeclare value integer;set value = 0;select current_val into value  from sequence where seq_name = v_seq_name;return value;
END $$
DELIMITER ;

创建nextval 函数

DELIMITER $$
DROP FUNCTION IF EXISTS nextval$$
create function nextval (v_seq_name VARCHAR(50))returns integer
beginupdate sequence set current_val = current_val + increment_val  where seq_name = v_seq_name;return currval(v_seq_name);
END $$
DELIMITER ;

使用

SELECT nextval('num1') ;
SELECT nextval('num2') ;

参考文章

参考文章