[1219]mysql自定义函数split_part、split_last

Mysql创建自定义函数

基本语法

delimiter 自定义符号　　-- 如果函数体只有一条语句, begin和end可以省略, 同时delimiter也可以省略
　　create function 函数名(形参列表) returns 返回类型　　-- 注意是retruns
　　begin
　　　　函数体　　　　-- 函数内定义的变量如：set @x = 1; 变量x为全局变量，在函数外面也可以使用
　  return返回值
　　end
　　自定义符号
　　delimiter ;

关键字delimiter的使用：
delimiter是mysql分隔符。在mysqlclient中分隔符默认是分号（；）。
假设一次输入的语句较多，而且语句中间有分号，这时须要新指定一个特殊的分隔符。
在前一篇中有设置mysql的触发器时使用样例

mysql> delimiter //
mysql> create trigger upd_check before update on account 
    -> for each row
    -> begin
    -> if new.amount < 0 then
    ->  set new.amount=0;
    -> elseif new.amount > 100 then
    ->  set new.amount = 100;
    -> end if;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

上面就是。先将分隔符设置为 //,
直到遇到下一个 //,才总体运行语句。
运行完后。最后一行， delimiter ; 将mysql的分隔符又一次设置为分号；
假设不改动的话，本次会话中的全部分隔符都以// 为准。

创建函数示例

定义一个相加函数myfun3

-- 自定义函数
delimiter $$
create function myfun3(ia int, ib int) returns int
begin
    return ia + ib;
end
$$
delimiter ;

查看函数

show function status [like 'pattern'];　　-- 查看所有自定义函数, 自定义函数只能在本数据库使用。
show create function 函数名;　　-- 查看函数创建语句

删除函数

drop function 函数名;

调用函数

select myfun3(2,3); --参数需为对应类型

综合应用

使用全局变量

-- 计算1 ~ 指定数据之间的和

delimiter $$
create function my_sum(x int) returns int
begin
    set @i = 1;
    set @sum = 0;
    while @i <= x do
        set @sum = @sum + @i;
        set @i = @i + 1;
    end while;
    return @sum;
end
$$
delimiter ;

使用局部变量

-- 求1 ~ 指定数之前的和，但5的倍数不加

delimiter $$
create function my_sum2(x int) returns int
begin
    declare i int default 1;
    declare sum int default 0;
    sumwhile:while i <= x do
        if i % 5 = 0 then
            set i = i + 1;
            iterate sumwhile;
        end if;
        set sum = sum + i;
        set i = i + 1;
    end while;
    return sum;
end
$$
delimiter ;

---

mysql自定义函数应用

• split_part

CREATE DEFINER=`dba`@`%` FUNCTION `clyydb`.`SPLIT_PART`(
`str` varchar(255) charset utf8mb4 collate utf8mb4_general_ci ,`delim` varchar(12) charset utf8mb4 collate utf8mb4_general_ci ,`pos` int(11) ) RETURNS varchar(255) CHARSET utf8mb4
    SQL SECURITY INVOKER
RETURN replace(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),char_length(SUBSTRING_INDEX(str, delim, pos -1))+1),'-','')

使用说明

select 
	split_part('a-b-c-d','-',1) as f1,
	split_part('a-b-c-d','-',2) as f2,
	split_part('a-b-c-d','-',3) as f3,
	split_part('a-b-c-d','-',4) as f4;

• split_last

CREATE DEFINER=`dba`@`%` FUNCTION `clyydb`.`split_last`(
`str` varchar(255) charset utf8mb4 collate utf8mb4_general_ci ,`delim` varchar(1) charset utf8mb4 collate utf8mb4_general_ci ) RETURNS varchar(255) CHARSET utf8mb4
    SQL SECURITY INVOKER
begin 
    declare returnstring varchar(255);
    if char_length(str)=char_length(replace(str,delim,'')) then 
        set returnstring=str;
    else 
        set returnstring=replace(REVERSE(LEFT(REVERSE(str),INSTR(REVERSE(str),delim))),delim,'');
    end if ; 
    return returnstring;
end

使用说明

select 
	split_last('a-b-c-d','-') as f1,
	split_last('a-b-c-','-') as f2;

• clean_partitions

CREATE DEFINER=`fanruan_dba`@`%` PROCEDURE `clyydb`.`clean_partitions`(in table_name varchar(128))
BEGIN
    DECLARE start_date VARCHAR(50);
    DECLARE end_date VARCHAR(50);
    DECLARE partition_name VARCHAR(50);

    -- 获取当前日期
    SET @current_date = CURDATE();

    -- 开始日期:INFORMATION_SCHEMA.PARTITIONS 表中最小分区 设置默认值20210101
    -- 结束日期:以当前时间往前三个月

    -- 查询最小分区 作为开始时间
    SET @str_sql=concat('SELECT coalesce(min(substring(partition_name,6,13)),''20210101'') into @tmp_startDate FROM INFORMATION_SCHEMA.PARTITIONS where TABLE_NAME =''', table_name,'''');
     PREPARE stmt FROM @str_sql;
     EXECUTE stmt; 
     DEALLOCATE PREPARE stmt;

    SET start_date = DATE_FORMAT(@tmp_startDate, '%Y-%m-%d');

    SET end_date = DATE_SUB(@current_date, INTERVAL 92 day);
     -- select @str_sql,start_date,end_date,table_name,@current_date;

    SET @partition_name = CONCAT('part_', DATE_FORMAT(start_date, '%Y%m%d'));
    SET @str_sql = '';
    SET @partition_list ='';
     
    WHILE start_date <= end_date DO
        -- 获取当月的最后一天
       SET @month_lastday = LAST_DAY(start_date);
      
      -- 判断分区是否存在
       SET @tmp_sql = concat('SELECT COUNT(1) INTO @partition_count FROM information_schema.PARTITIONS 
                            where TABLE_NAME =''', table_name,''' and PARTITION_NAME =''',@partition_name,'''');
        
       PREPARE stmt FROM @tmp_sql;
       EXECUTE stmt; 
       DEALLOCATE PREPARE stmt;
   
      -- 分区日期不等于当月最后一天并且分区存在
        IF  start_date <> @month_lastday  and @partition_count > 0  then
            SET @partition_list = CONCAT(@partition_list,',', @partition_name);
            
        end IF;
       
        SET start_date = start_date + INTERVAL 1 DAY;
        SET @partition_name = CONCAT('part_', DATE_FORMAT(start_date, '%Y%m%d'));
        
    END WHILE;
   
     -- 删除历史分区操作
    SET @partition_list = substring(@partition_list,2,length(@partition_list));
       IF  @partition_list <> ''  then
            SET @str_sql = CONCAT('ALTER TABLE ',table_name,' DROP PARTITION  ', @partition_list,';');
            PREPARE stmt FROM @str_sql;
            EXECUTE stmt; 
            DEALLOCATE PREPARE stmt;
        end IF;
END

使用说明

call clean_partitions('表名');

例子：
call clean_partitions('ads_report_24_hour_cabin_daily_1d_test');

---

mysql运行语句时出现 FUNCTION *** does not exist

这个错误通常是因为你在使用一个不存在的函数。可能是因为你没有正确地创建函数，或者你没有正确地调用函数。

以下是一些可能的解决方案：

1. 确认函数是否存在。你可以使用 SHOW FUNCTION STATUS 命令来查看所有已经创建的函数。

2. 确认函数的名称是否正确。如果你在调用函数时拼写错误，那么就会出现这个错误。

3. 确认函数的参数是否正确。如果你在调用函数时传递了错误的参数，那么就会出现这个错误。

4. 确认函数是否在正确的数据库中。如果你在调用函数时没有指定数据库名称，那么就会默认在当前数据库中查找函数。如果函数不在当前数据库中，那么就会出现这个错误。

5. 确认你是否有足够的权限来访问函数。如果你没有足够的权限来访问函数，那么就会出现这个错误。

如果你仍然无法解决这个问题，那么你可以尝试重新创建函数，或者联系数据库管理员来获取帮助。

参考：https://www.itguest.com/post/edfbf2b5.html
https://blog.csdn.net/qq_43581790/article/details/126800065