mysql自定义函数收集

代码：

查找字符串 in_string 中，存在多少个字符串 in_find_str

delimiter $$
DROP FUNCTION IF EXISTS `fn_findCharCount` $$
CREATE FUNCTION fn_findCharCount
(
in_string VARCHAR(500),
in_find_str VARCHAR(50)
)
RETURNS INT
BEGIN

    DECLARE tcount INT DEFAULT 0;
    DECLARE new_str VARCHAR(500);
    DECLARE scount INT;

    SET new_str = in_string;

    SELECT INSTR(new_str,in_find_str) INTO scount;

WHILE scount>0 DO
SET tcount = tcount+1;
SELECT SUBSTRING(new_str FROM (scount+1) FOR CHAR_LENGTH(new_str)) INTO new_str;
SELECT INSTR(new_str,in_find_str) INTO scount;
END WHILE;
    
    RETURN(tcount);

END$$
delimiter ;

产生范围在 start_num 和 end_num 之间的随机数：

delimiter $$
DROP FUNCTION IF EXISTS `fn_rand_num` $$
CREATE FUNCTION fn_rand_num (
    start_num INTEGER,
    end_num INTEGER
) RETURNS INTEGER
BEGIN
    RETURN FLOOR(start_num + RAND() * (end_num - start_num + 1));
END$$;
delimiter ;

删除函数：DROP FUNCTION IF EXISTS `fn_findCharCount`;

执行函数：select fn_findCharCount('abcd','b');