mysql实现交易编码生成(代替oracle的序列)

• 创建序列表

drop table if exists sequence;

create table sequence(
seq_name varchar(50) not null comment '序列名称',
current_val int not null comment '当前值',
increment_val int not null default 1 comment '步长，默认为1',
today_date date,
primary key(seq_name,today_date)
) comment '序列表';

• 插入第一个值

insert into sequence(seq_name,current_val,increment_val,today_date) values('seq_transactionno',0,1,current_date)


commit;

select * from sequence;

• 创建函数获取序列当前值

drop function if exists seq_currentval;
create function seq_currentval (v_seq_name varchar(50),v_date date)
returns int(10)
language sql
deterministic
contains sql
sql security definer
comment '获取传入序列当前值'
begin
  declare v_value int;
	set v_value=0;
  select current_val into v_value from sequence where seq_name=v_seq_name and today_date=v_date;
	return v_value;
end

• 创建函数获取下一个值

drop function if exists seq_nextval;
create function seq_nextval(v_seq_name varchar(50),v_date date)
returns int(10)
language sql
deterministic
contains sql
sql security definer
comment '获取传入序列的下一个值'
begin
	update sequence 
	set current_val=current_val+increment_val
	where seq_name=v_seq_name and today_date=v_date;
	return seq_currentval(v_seq_name,v_date);
end;

select seq_currentval('seq_transactionno',current_date)

• 测试

drop table if exists tt;
create table tt(
transactionno varchar(50),
name varchar(50)
)

drop table if exists t1;
create table t1(
name varchar(50)
)

insert into t1(name) values('3')
commit;

select * from t1;
select * from tt;

insert into tt
(transactionno,
name
)
select get_today_transactionno('seq_transactionno',date'2020-11-11'),
       t1.name
from t1



select count(*) from sequence where seq_name='seq_transactionno' and today_date=CURRENT_DATE

• 创建生成当前交易编码的函数

drop function if exists get_today_transactionno;
create function get_today_transactionno(v_seq_name varchar(50),v_date date)
returns varchar(50)
language sql
deterministic
contains sql
sql security definer
comment '获取当天的交易编号'
begin
	-- 序列赋初值，如果不存在，初始化
	declare v_count int;
	select count(*) into v_count from sequence where seq_name=v_seq_name and today_date=v_date;
	if v_count<>1 then
			insert into sequence(seq_name,current_val,increment_val,today_date) values(v_seq_name,0,1,v_date);
	end if;
	-- 返回格式化数据
	return concat('000167',date_format(v_date,'%Y%m%d'),lpad(seq_nextval('seq_transactionno',v_date),10,0));
end;

-- 测试
select  get_today_transactionno('seq_transactionno',date'2020-11-11');