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');