首先创建sequence表
分别对应:seq_name-序列名称,min_val-最小值,max_val-最大值,current_val-当前值,increment_val-增长步数
CREATE TABLE
my_sequence
(
seq_name VARCHAR(50) NOT NULL,
min_val INT NOT NULL,
max_val INT NOT NULL,
current_val INT NOT NULL,
increment_val INT DEFAULT 1 NOT NULL,
PRIMARY KEY (seq_name)
)
ENGINE=MYISAM DEFAULT CHARSET=utf8;
insert into my_sequence (seq_name, min_val, max_val, current_val, increment_val) values ('my_seq', 1000, 99999999, 1000, 1);
创建function getnextval() 用于获取当前序列号
DELIMITER //
CREATE FUNCTION getnextval(NAME VARCHAR(50))
RETURNS INTEGER
BEGIN
DECLARE my_cur INT; -- 当前值
DECLARE my_maxvalue INT; -- 接收最大值
DECLARE my_increment INT; -- 接收增长步数
SET my_increment = (SELECT increment_val FROM my_sequence WHERE seq_name = NAME);
SET my_maxvalue = (SELECT max_val FROM my_sequence WHERE seq_name = NAME);
SET my_cur = (SELECT current_val FROM my_sequence WHERE seq_name = NAME);
UPDATE my_sequence -- 更新当前值
SET current_val = my_cur + increment_val
WHERE seq_name = NAME ;
IF(my_cur + my_increment >= my_maxvalue) THEN -- 判断是都达到最大值
UPDATE my_sequence
SET current_val = min_val
WHERE seq_name = NAME ;
END IF;
RETURN my_cur;
END;
//
DELIMITER ;
说明:DELIMITER //修改sql执行结束标识;
当前序列达到最大值时,重新刷新到最小值开始,实现sequence循环使用。
SELECT getnextval('my_seq');