mysql 自定义函数

来源：互联网发布：域名和空间在哪里买编辑：程序博客网时间：2024/06/15 16:36

下面的自定义函数中，

注意delimiter 的使用，如果不懂，请看我的上一篇文章

使用了 group_concat()、find_in_set() 函数，

delimiter $$use `test`$$drop function if exists `queryOnePartnerIdById`$$create  function `queryOnePartnerIdById`(_id bigint) returns varchar(4000) charset utf8beginif _id is null or _id='' or _id=0 then    return null;        end if ;select GROUP_CONCAT(id) into @__selectTmpID from lcs_member where del_flag=0 and belong_to_partner>0 and FIND_IN_SET(belong_to_partner,_id)>0;if (@__selectTmpID!='' and @__selectTmpID is not null ) then    return  @__selectTmpID;else    return null;end if;    end$$delimiter ;

delimiter $$use `test`$$drop function if exists `queryOnePartnerUuidByUuid`$$create  function `queryOnePartnerUuidByUuid`(_uuid varchar(4000)) returns varchar(4000) charset utf8beginset _uuid=TRIM(_uuid);if _uuid is null or _uuid='' then    return null;        end if ;select GROUP_CONCAT(id) into @__userID from lcs_member where del_flag=0 and  user_uuid=_uuid;if  @__userID is null or  @__userID='' then    return null;        end if ;select GROUP_CONCAT(id),GROUP_CONCAT(user_uuid) into @__selectTmpID,@_user_uuid from lcs_member where  del_flag=0 and belong_to_partner>0 and FIND_IN_SET(belong_to_partner,@__userID)>0;if (@__selectTmpID!='' and @__selectTmpID is not null ) then     return @_user_uuid;else    return null;end if;    end$$delimiter ;

调用：

select queryOnePartnerIdById(318);select * from lcs_member   where FIND_IN_SET(id, queryOnePartnerIdById(318));

select queryOnePartnerUuidByUuid('9974A2EC6DCE8AB0E')select * from lcs_member   where FIND_IN_SET(user_uuid,  queryOnePartnerUuidByUuid('9974A2EC6DCE8AB0E'));

阅读全文

0 0