mysql 自定义 sequence 存储过程

mysql本身是没有sequence的但是我可以自己建立sequence存储过程.

首先我们先建立相关的表,和存储过程.

第一步是创建规则表.

 

drop table if exists `seq`;
create table `seq` (
  `id` 	bigint(11)		not null 		auto_increment,
  `name` 	varchar(255) 	not null		comment 'sequence名称',
  `max` 	bigint(11)		not null default '1' 	comment '最大id', 
  `length`int(2) 		not null default '1' 	comment '生成序列后的长度,以0补全',
  `next` 	int(2) 		not null default '1' 	comment '增长的长度',
  `rules` 	varchar(255) 	default null 	comment '规则以###max_id###做为替换',
  primary key (`id`),
  unique key `fk_name` (`name`)
) engine=innodb auto_increment=1 default charset=utf8;

 

第二步是创建sequence方法.计算方法.

 

drop function if exists bj;
delimiter $
create function bj (seq_name varchar(50))
returns varchar(20)
contains sql
begin
	declare seq_rules varchar(255);
	declare seq_length int(2);
	declare seq_max bigint(20);

	declare max_id_length int(20);
	declare max_id varchar(20);

	select rules,seq.`length`,`max` into seq_rules, seq_length,seq_max from seq where name =  seq_name ;

	set max_id = seq_max;
	set max_id_length = character_length(max_id);

	#长度不够补0
	while seq_length > max_id_length do
		set max_id = concat('0',max_id);
		set max_id_length = character_length(max_id);
	end while; 

	#替换规则
	if seq_rules is not null and instr(seq_rules,'###max_id###') > 0   then
		set max_id = replace(seq_rules,'###max_id###',max_id);
	end if;

	return max_id;
end$
delimiter ;

 

第三步是创建sequence方法.调用方法.

 

 

drop function if exists nextval;
delimiter $
create function nextval (seq_name varchar(50))
returns varchar(20)
contains sql
begin
   update seq set `max` = `max` + next  where name = seq_name;
   return bj(seq_name);
end$
delimiter ;

 

 

好上边没问题了 我们建立几条测试数据看看。

 

#纯数字序列
insert into `seq` (`name`,`max`,`length`,`next`,`rules`) values ('seq_test1', '0', '1', '1', null );
#带有规则序列
insert into `seq` (`name`,`max`,`length`,`next`,`rules`) values ('seq_test2', '0', '3', '2','seq###max_id###');

select nextval('seq_test1');
select nextval('seq_test2');