mysql写function时的注意点记录，生成序号扩展函数

1、声明DECLARE变量语句必须写在函数体的最上面

2、使用变量接收select a=name from t 会提示不能返回结果集，可以改成 select name into a from t

 ==============================================================================

序号表结构       默认步长为1 ，生成数据后，直接修改表值

DROP TABLE IF EXISTS `seq`;
CREATE TABLE `seq`  (
  `Name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '表名',
  `CurrDate` date NOT NULL COMMENT '当前日期',
  `Num` int(11) NOT NULL COMMENT '序号',
  `StepLen` int(11) NULL DEFAULT 1 COMMENT '步长',
  `SeqType` int(11) NULL DEFAULT 0 COMMENT '0 默认序号 1首位36位字符',
  `FirstIdx` int(11) NULL DEFAULT 0 COMMENT '首位索引值 type1有效',
  PRIMARY KEY (`Name`, `CurrDate`, `Num`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '序号表' ROW_FORMAT = Dynamic;

简单生成序号,运行相对简单，响应快点，支持的序号数少一点

CREATE DEFINER=`root`@`%` FUNCTION `seq`(`seq_name` varchar(50),is_date_head int,`len` int) RETURNS varchar(50) CHARSET utf8
BEGIN
    -- 函数说明 为对应的序号标识生产指定长度的序号 
    -- 2024-10-19 11:13:11 Jevan
    
    -- seq_name 序号标识，一般为表名做为标识
    -- is_date_head 序号头部是否生成以的日期格式(241019)六位字符串，1是 其他则否
    -- len 要生成的序号长度(日期格式(241019)六位字符串不计在内)，如传6，则为000001

    DECLARE curr_date date;
    set curr_date = CURDATE();
    
    UPDATE seq SET Num=last_insert_id(Num + StepLen) WHERE name=seq_name and CurrDate=curr_date and SeqType=0;
    IF ROW_COUNT() = 0 then
        insert into seq (Name,CurrDate,Num,StepLen,SeqType,FirstIdx) select seq_name,curr_date,last_insert_id(1),StepLen,0,0 from seq where name=seq_name and SeqType=0 and CurrDate!=curr_date order by CurrDate desc limit 1;
        if Row_Count() =0 then
            insert into seq (Name,CurrDate,Num,StepLen,SeqType,FirstIdx) values(seq_name,curr_date,last_insert_id(1),1,0,0);
        end if;
    end if;
    
    if is_date_head =1 then
        return CONCAT(DATE_FORMAT(curr_date, '%y%m%d'),lpad(last_insert_id(),len,0));
    ELSE
        return lpad(last_insert_id(),len,0);
    end if;
END

扩展生成序号，逻辑相对复杂，数据库读取多几个步骤，相对来说序号数多点

CREATE DEFINER=`root`@`%` FUNCTION `seq_extend`(`seq_name` varchar(50),is_date_head int,`len` int) RETURNS varchar(50) CHARSET utf8
BEGIN
    -- 函数说明 为对应的序号标识生产指定长度的序号，序号首位通过36位字符(0-Z)进行升序, 
    -- 注：未处理Z后的升序；len 必须>=2
    -- 2024-10-19 11:13:11 Jevan
    
    -- seq_name 序号标识，一般为表名做为标识
    -- is_date_head 序号头部是否生成以的日期格式(241019)六位字符串，1是 其他则否
    -- len 要生成的序号长度(日期格式(241019)六位字符串不计在内)，如传6，则为Z00001
    
    DECLARE curr_idx int;
    DECLARE curr_date date;
    DECLARE char_str varchar(50);
    
    set curr_date = CURDATE();
    set curr_idx=-1;    
    set char_str = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    
  update seq set Num=last_insert_id(Num + StepLen) where `Name`=seq_name and CurrDate=curr_date and SeqType=1;
    
     IF ROW_COUNT() = 0 then
        set curr_idx=0;
        insert into seq (Name,CurrDate,Num,StepLen,SeqType,FirstIdx) select seq_name,curr_date,last_insert_id(1),StepLen,1,curr_idx from seq where name=seq_name and CurrDate!=curr_date and SeqType=1 order by CurrDate desc limit 1;
        if Row_Count() =0 then
            insert into seq (Name,CurrDate,Num,StepLen,SeqType,FirstIdx) values(seq_name,curr_date,last_insert_id(1),1,1,curr_idx);
        end if;
     end if;
     
     if curr_idx=-1 THEN
        select FirstIdx into curr_idx from seq where `Name`=seq_name and CurrDate=curr_date and SeqType=1;
     end if;
     
     if LENGTH(last_insert_id())>(len-1) THEN
        set curr_idx=curr_idx+1;
        update seq set num=last_insert_id(1),FirstIdx = FirstIdx+1 where `Name`=seq_name and CurrDate=curr_date and SeqType=1;
     end if;
     
     if is_date_head = 1 then
        return CONCAT(DATE_FORMAT(curr_date, '%y%m%d'),SUBSTRING(char_str,curr_idx+1,1),lpad(last_insert_id(),len-1,0));
     ELSE
        return CONCAT(SUBSTRING(char_str,curr_idx+1,1),lpad(last_insert_id(),len-1,0));
     end if;
END