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)