mysql+mybatis实现存储过程+事务 + 多并发流水号获取

数据库存储过程

DROP PROCEDURE IF EXISTS `generate_serial_number_by_date`;
CREATE PROCEDURE `generate_serial_number_by_date`(
    IN param_key varchar(100),
    IN param_org_id bigint, 
    IN param_period_date_format varchar(20), 
      OUT result bigint,
    OUT current_datestr varchar(20))
begin 

        declare old_datestr varchar(20);
        
        START TRANSACTION; 

        if param_period_date_format='infinite' then 
            set current_datestr = '00000000';
    else
            set current_datestr = DATE_FORMAT(NOW(), param_period_date_format);
        end if;
        
        select 
                    number, datestr 
        from sys_serial_number
        where table_key = param_key 
                and org_id = param_org_id 
                and period_date_format = param_period_date_format
                into result, old_datestr
                for update;

        IF result is null then
            
            set result = 1;
            
            insert into sys_serial_number(table_key, org_id, period_date_format, datestr, number, description) 
                values(param_key, param_org_id, param_period_date_format, current_datestr, 1, 'add by procedure');
        
        elseif old_datestr != current_datestr then
            
            set result = 1;
            
            update sys_serial_number 
                    set number = 1,    
                            datestr = current_datestr 
            where table_key = param_key 
                    and org_id = param_org_id 
                    and period_date_format = param_period_date_format;
            
        end if;
        
        update sys_serial_number set number = number + 1 
            where table_key = param_key 
                and org_id = param_org_id 
                and period_date_format = param_period_date_format;
    commit;
end