drop PROCEDURE if EXISTS getSequence;
drop TABLE if EXISTS tb_sequence;
create PROCEDURE getSequence(in nus BIGINT)
begin
DECLARE seqid BIGINT DEFAULT 0;
DECLARE datet VARCHAR(50) DEFAULT '';
create table if not EXISTS tb_sequence(
`id` bigint(20) NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`sequence` bigint(20) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`, `sequence`) USING BTREE
);
select sequence into seqid from tb_sequence where name='sequencename';
if seqid=0 then
insert into tb_sequence(id,name,sequence)values(1,'sequencename',0);
end if;
update tb_sequence set sequence=sequence+1 where name='sequencename';
select sequence into seqid from tb_sequence where name='sequencename';
select date_format(now(),'%Y%m%d%H%i%S') INTO datet;
if nus=1 then
select CONCAT(datet,'-',seqid) as seqnum;
else
select CONCAT(datet,'',seqid) as seqnum;
end if;
END;
调用
@Select("call getSequence(#{‘param’)")