MySQL – 使用存储过程和函数，递归查询指定目录下，指定元数据类型的模型数

目的：替代Java程序多次发起Select请求，提高程序响应速度。
记得针对where后的查询字段，增加相应的联合索引，依次从前到后索引生效，看需求平衡时间还是空间。

一、函数部分

1. 获取（单层）元数据目录数量

drop function if exists getSingleMetaCoreCount;
DELIMITER $$
create function getSingleMetaCoreCount(folderId CHAR(50), metaMdl char(50))
RETURNS int(50)
begin
    declare num int(50) DEFAULT 0;
    -- 记录当前目录下metaMdl元数据类型数
    select count(id) into num from pprt_dg_meta_core t where t.prt_id = folderId and meta_mdl=metaMdl;
    return num;
end

测试SQL

select getMetaCoreCount('ec38ede318864d1483fb94d60aa3ffdd', 'biz.BigTable'); -- 11
select getMetaCoreCount('ec38ede318864d1483fb94d60aa3ffdd', 'core.BasePackage'); -- 2

2.获取指定目录folderId下的metaMdl类型的元数据id字符串集合（以英文逗号分隔）👌

drop function if exists getMetaCoreIds;
DELIMITER $$
create function getMetaCoreIds(folderId CHAR(50), metaMdl char(50))
RETURNS longtext
begin
        declare folderIds longtext DEFAULT '';
        select group_concat(id) into folderIds from pprt_dg_meta_core t where t.prt_id = folderId and meta_mdl=metaMdl;
        return folderIds;
end

测试SQL

-- 样例查询
select getMetaCoreIds('ec38ede318864d1483fb94d60aa3ffdd', 'biz.BigTable');
select getMetaCoreIds('ec38ede318864d1483fb94d60aa3ffdd', 'core.BasePackage');
select id from pprt_dg_meta_core t where t.prt_id = 'ec38ede318864d1483fb94d60aa3ffdd' and meta_mdl='biz.BigTable';

3. 获取多层元数据目录数量👌

drop function if exists getMetaCoreCount;
DELIMITER $$
create function getMetaCoreCount(folderId CHAR(50), metaMdl char(50))
RETURNS int(10)
begin
    declare num int(10) default 0;
    SET @@max_sp_recursion_depth = 100;
    call getMetaCoreCount_procedure(folderId, metaMdl, num);
    return num;
end

测试SQL

-- 获取子目录id集合
select getMetaCoreIds('ec38ede318864d1483fb94d60aa3ffdd', 'core.BasePackage') into subFolderIds;

二、 存储过程部分

递归获取子目录下指定类型元数据数量👌

drop PROCEDURE if exists getMetaCoreCount_procedure;
DELIMITER $$
create PROCEDURE getMetaCoreCount_procedure(IN folderId char(50), IN metaMdl char(50), OUT sum int)
begin
    declare num int(10) DEFAULT 0;
    declare innerNum int(10) DEFAULT 0;

    declare tmpNum int(10) DEFAULT 0;
    declare tmpString char(50) DEFAULT '';

    declare subFolderIds longtext DEFAULT '';
    declare curId char(50) DEFAULT '';

    -- 记录当前目录下metaMdl元数据类型数
    select count(id) into num from pprt_dg_meta_core t where t.prt_id = folderId and meta_mdl=metaMdl;
    if num > 0 then
        select coalesce(sum,0) into tmpNum;
        set sum = tmpNum + num;-- 加和数量统计
    end if;

    -- 获取子目录id集合
    select getMetaCoreIds(folderId, 'core.BasePackage') into subFolderIds;

    while length(subFolderIds) > 0 do
        if length(subFolderIds) > 0 then 
        -- 取出id字符串集合subFolderIds中，第一个id
        select substring_index(subFolderIds, ',', 1) into curId;
        -- 递归查询这个id目录下metaMdl元数据类型数
        call getMetaCoreCount_procedure(curId, metaMdl, innerNum);
        -- 将遍历后的id从subFolderIds中删除
        SELECT REPLACE(subFolderIds, curId, '') into tmpString;
        set subFolderIds = tmpString;

        -- 加和子目录的元数据数量
        if innerNum > 0 then
            select coalesce(sum,0) into tmpNum;
            set sum = tmpNum + innerNum;
        end if;

        -- 获取subFolderIds字符串的第一个字符
        SELECT LEFT(subFolderIds, 1) into tmpString;
        -- 如果第一个字符是英文逗号，则截取字符串
        if tmpString = ',' then
            SELECT SUBSTR(subFolderIds, 2, length(subFolderIds)) into subFolderIds;
        end if;

        end if;
    end while;
end

三、MySQL内置函数部分

-- 从字符串中提取子字符串（从位置2开始，提取第一个字符后面的全部字符）
SELECT SUBSTR("SQL Tutorial", 2, length("SQL Tutorial"));

-- 从字符串中提取子字符串（从位置1开始，提取1个字符）
SELECT SUBSTR("SQL Tutorial", 1, 1);

-- 获取字符串第一个字符
SELECT LEFT("SQL Tutorial", 1);

-- 替换函数
SELECT REPLACE("SQL Tutorial", "SQL", "HTML");

-- 如果为空值，返回1
select coalesce(null,1)

-- 判断入参变量是否为空，为空返回1，非空返回0
select isnull(null);

-- 查看线程相关参数值
show variables like '%thread%';

-- 查看最大递归深度，需要设置，不设置不可使用存储过程的递归调用
show VARIABLES like 'max_sp_recursion_depth'

-- 查看连接数
show status like 'connections';

-- 设置最大递归深度为100
SET @@max_sp_recursion_depth = 100;