MySQL自定义函数

 ⚠不推荐将业务逻辑存储在数据库中.

MySQL不仅提供了很多很方便的内置函数，用户还可以自定义函数。
不同于MongoDB对Js函数的良好支持，MySQL的自定义函数用起来感觉处处掣肘，不推荐用来处理复杂的业务逻辑。
MySQL自定义函数默认是和库进行绑定的。

增：

CREATE
    [DEFINER = user]
    FUNCTION [IF NOT EXISTS] sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

routine_body:
    Valid SQL routine statement

[创建过程和创建函数语句](https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html "创建过程和创建函数语句")

删：

DROP FUNCTION [IF EXISTS] sp_name

改：

ALTER FUNCTION func_name [characteristic ...]

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

此语句可用于更改 存储函数。可以在ALTER FUNCTION语句中指定多个更改。然而 不能更改存储函数的参数或主体 使用此语句;要进行此类更改，您必须删除 使用 DROP 重新创建函数 函数和创建 功能。

查：

show function status [like functionName];

show create function functionName;

使用实例1——姓名混淆：

create
    function changeName(name varchar(255),salt int)
    returns varchar(255)
    
reads sql data

begin
    -- 返回姓名
    declare newName varchar(255) default '';
    declare i,j,coded int default 1;
    -- 姓氏
    declare family_str varchar(334) default '赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮卞齐康伍余元卜顾孟平黄和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董梁杜阮蓝闵席季麻强贾路娄危江童颜郭梅盛林刁钟徐邱骆高夏蔡田樊胡凌霍虞万支柯昝管卢莫经房裘缪干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚程嵇邢滑裴陆荣翁荀羊於惠甄曲家封芮羿储靳汲邴糜松井段富巫乌焦巴弓牧隗山谷车侯宓蓬全郗班仰秋仲伊宫宁仇栾暴甘钭厉戎祖武符刘景詹束龙叶幸司韶郜黎蓟薄印宿白怀蒲邰从鄂索咸籍赖卓蔺屠蒙池乔阴胥能苍双闻莘党翟谭贡劳逄姬申扶堵冉宰郦雍郤璩桑桂濮牛寿通边扈燕冀郏浦尚农温别庄晏柴瞿阎充师巩厍聂晁勾敖融';
    -- 名
    declare name_str varchar(354) default '建秀军秀英维君勇丽伟静欧文伟静涛婷浩宇欣怡建华桂英勇桂英景逸军艳磊丽熹祎超婷浩欣怡国浩然梓涵国华秀兰伟英伟敏勇娟涛英敏杰婷婷宇轩诗涵和平玉兰建国玉兰强芳涛艳杰婷婷鑫静子轩梓置明桂兰建华萍刚静超燕鹏丹俊杰悦宇航子涵建平秀珍建军秀兰建军霞强敏磊雪磊敏皓轩紫涵军凤英平玉梅涛红梅鹏娜强丽帅佳子豪佳怡平玉珍建平红斌燕军芳浩梓子宇辰宸泽嘉欣佳奕轩晨涵思诺雨语睿文妍安博怡依浩沐铭诗珥俊然彤乐皓琪瑶悦艺桐熙煜一诺依诺欣怡梓涵语桐欣妍可欣语汐雨桐梦瑶阳锦伊昕恩可奕辰宇轩浩宇亦辰宇辰子墨宇航浩然梓豪亦宸若菅倩宇雪浩轩雨涵志明玉英强丽波红波丹鑫艳浩然颖俊杰雨欣德明兰英斌敏辉英杰玲帅娟鹏雨欣子涵一诺一子之川山士久冉羽弋千天月丹可宜雨欣淇晓海涛滨琪琦豪杰轩延杉平雪怡姗涵婷桐佳嘉诗雅菲绮韵舒熙瑜毓煜楠涵函洛珞博凯斯遥睿琳彤婕妤';
    declare oneCode varchar(4);
    -- 英文名不修改   length() 函数，一个汉字长度为3
    if(length(name) = char_length(name)) then
        set newName = name;
    else
    -- 进行混淆，先使用base64编码将一个汉字分为4个字符
        while i <= char_length(name) do
          set oneCode = to_base64(substring(name,i,1));
          set coded = 0;
          set j = 1;
          while j <= char_length(oneCode) do
              -- 对每个字符取ascii码，错位相加进行混淆；适当取余，防止溢出
              set coded = (coded * pow(10,i) + ascii(substring(oneCode,j))) % 10000;
              set j = j + 1;
          end while;
          -- 使用盐值进行错位
          set coded = abs((coded - salt));
          if(i = 1) then
              set newName = concat(newName,substring(family_str,coded % char_length(family_str) + 1,1));
          else
              set newName = concat(newName,substring(name_str,coded % char_length(name_str) + 1,1));
        end if;
          set i = i + 1;
      end while;
    end if;
    return newName;
end;

使用实例2——混淆电话号码

create
    function changePhone(phone varchar(50),salt int)
    returns varchar(50)
reads sql data
begin
    -- 返回号码
    declare newPhone varchar(50) default '';
    declare oneCode varchar(5);
    -- 非私人号码不修改
    if(length(phone) < 6) then
        set newPhone = phone;
    else
        -- 进行混淆，原号码后5位的自然对数与盐值的弧度值相加
        set oneCode = substring_index(truncate(ln(right(phone,5)) + radians(salt),5),'.',-1);
        set newPhone = concat(left(phone,char_length(phone)-5),oneCode);
        --  弥补数字和字符转换时，0的丢失
        while length(phone) > length(newPhone) do
            set newPhone = concat(newPhone,0);
        end while;
    end if;
    return newPhone;
end;