mysql序列创建，带最大值及循环

mysql序列创建，带最大值及循环

需要将oracle库换成mysql，由于mysql中没有序列，所以需新建序列表模拟oracle序列处理。

新建序列表

CREATE TABLE `sequence`
(  
	`seq_name` VARCHAR(50) NOT NULL,  -- sequence名称
	`current_value` INT (11) NOT NULL,  -- 当前sequence值
	`max_value` INT (11) NOT NULL, -- 最大sequence值
	`increment` INT (11) NOT NULL DEFAULT 1,  -- 增长系数
	PRIMARY KEY (seq_name)  
) ENGINE=InnoDB;  

新增系统序列

Insert into `sequence` values('sys_seq',1,10,1);

创建获取序列当前值方法

DROP FUNCTION IF EXISTS currval;
-- 当前值
DELIMITER $
CREATE FUNCTION currval (seqname VARCHAR(50))
returns INTEGER
CONTAINS SQL
BEGIN
-- 获取当前值的函数
DECLARE current_v INTEGER;
SET current_v = 0;
SELECT current_value INTO current_v
FROM `sequence`
WHERE seq_name = seqname;
RETURN current_v;
end; 
$
DELIMITER ;

创建获取序列下一值方法

DROP FUNCTION IF EXISTS nextval;
-- 下一个值
DELIMITER $
CREATE FUNCTION nextval (seqname VARCHAR(50))
RETURNS INTEGER
CONTAINS SQL
BEGIN
-- 获取下一个值;
DECLARE current_v,increment_v,max_v INTEGER;
-- DECLARE increment_v INTEGER;
-- DECLARE max_v INTEGER;
select current_value,`increment`,max_value into current_v,increment_v,max_v from `sequence` where seq_name = seqname;
-- select `increment` into increment_v from `sequence`;
-- select max_value into max_v from `sequence`;
UPDATE `sequence` SET current_value = current_v+ increment_v
WHERE seq_name = seqname;
if(current_v = max_v) then
    UPDATE `sequence` SET current_value = 1;
end if;
RETURN currval(seqname);
end;
$
DELIMITER ;

方法测试

select currval('sys_seq');
select nextval('sys_seq');

经测试自增和循环均无问题

其他问题

创建方法时报错解决 ERROR 1418 (HY000)

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

解决方法：

set global log_bin_trust_function_creators=TRUE;

原因查询：
当二进制日志启用后，这个变量就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。 设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。 如果变量设置为1，MySQL不会对创建存储函数实施这些限制。 此变量也适用于触发器的创建。 请参见第23.7节“Binary Logging of Stored Programs”。

带初始值

CREATE TABLE `sequence`
(  
	`seq_name` VARCHAR(50) NOT NULL,  -- sequence名称
	`init_value` INT (11) NOT NULL, -- sequence初始值
	`current_value` INT (11) NOT NULL,  -- 当前sequence值
	`max_value` INT (11) NOT NULL, -- 最大sequence值
	`increment` INT (11) NOT NULL DEFAULT 1,  -- 增长系数
	PRIMARY KEY (seq_name)  
) ENGINE=InnoDB;  
 
Insert into `sequence` values('sys_seq',1,10,1);
Insert into `sequence` values('sys_seq1',1,10,1);


set global log_bin_trust_function_creators=TRUE;

select * from `sequence` where seq_name='sys_seq1'

DROP FUNCTION IF EXISTS currval;
-- 当前值
DELIMITER $
CREATE FUNCTION currval (seqname VARCHAR(50))
returns INTEGER
CONTAINS SQL
BEGIN
-- 获取当前值的函数
DECLARE current_v INTEGER;
SET current_v = 0;
SELECT current_value INTO current_v
FROM `sequence`
WHERE seq_name = seqname;
RETURN current_v;
end; 
$
DELIMITER ;

DROP FUNCTION IF EXISTS nextval;
-- 下一个值
DELIMITER $
CREATE FUNCTION nextval (seqname VARCHAR(50))
RETURNS INTEGER
CONTAINS SQL
BEGIN
-- 获取下一个值;
DECLARE current_v,increment_v,max_v INTEGER;
-- DECLARE increment_v INTEGER;
-- DECLARE max_v INTEGER;
select init_value,current_value,`increment`,max_value into init_v,current_v,increment_v,max_v from `sequence` WHERE seq_name = seqname;
-- select `increment` into increment_v from `sequence`;
-- select max_value into max_v from `sequence`;
UPDATE `sequence` SET current_value = current_v+ increment_v
WHERE seq_name = seqname;
if(current_v = max_v) then
    UPDATE `sequence` SET current_value = init_v WHERE seq_name = seqname;
end if;
RETURN currval(seqname);
end;
$
DELIMITER ;



select currval('sys_seq');
select nextval('sys_seq1');