mysql生成序列_mysql 自动生成编号函数

根据需求，保存表数据时需要自动生成一个编号，格式如：AA-2020-03-31-0001  (AA-yyyy-MM-dd-序号)。数据库用的mysql，所以创建一个mysql函数。

1、建表：

create table sys_sequence_number(

sequenceType varchar(30) not null,

val int not null,

len int not null

);

2、建函数

DELIMITER $$

DROP FUNCTION IF EXISTS getSequenceNo $$

create function getSequenceNo(pSequenceType varchar(30),pLen int) returns varchar(60)

begin

declare strZero varchar(20) default ‘00000000000000000000‘;

declare strSequenceNo varchar(60) default ‘‘;

declare iVal int default 0;

declare iLen int default 0;

declare c int default 0;

select count(1) into c from sys_sequence_number where sequenceType=pSequenceType;

if(c<1)

then

insert into sys_sequence_number(sequenceType,val,len)

values(pSequenceType,0,pLen);

end if;

update sys_sequence_number set val=val+1 where sequenceType=pSequenceType;

select val,len into iVal,iLen from sys_sequence_number where sequenceType=pSequenceType;

set strSequenceNo=concat(substr(strZero,1,iLen-length(iVal)),convert(iVal,char(10)));

return concat(concat(pSequenceType,‘-‘),strSequenceNo);

end $$

DELIMITER ;

3、在mysql上执行测试： select  getSequenceNo(concat(‘AA-‘,date_format(now(), ‘%Y-%m-%d‘)),4)