Mysql Function to Query All Child Nodes Based on Parent Node

The data volume is 5k+. Referring to the function given in the blog, it takes 15s to run once, which is too long. So finally I didn’t choose to traverse data in Mysql, but traversed in the class when the user selects the parent node. The overall time consumption will be much smaller, and it will not bring lag feeling to the user.

I don’t use Mysql functions much usually, so I record it for future learning use.

1. After the function is created in Mysql, it will prompt the error message This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de . Refer to blog: MySQL Create Function Error This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators
   There are two solutions:
   • Write a sentence before the function body set global log_bin_trust_function_creators=TRUE; But it needs to be run again every time after restart.
   • Set in my.cnf configuration file
     log-bin-trust-function-creators=1 Effective after restart.
2. If modifying and running after function creation, it will prompt: MySQL: create a function but it already exists
   Need to add DROP FUNCTION IF EXISTS funcName; before the function.
   Solution answer: stackflow
3. Function body, here I also half-understand, directly paste address: Mysql recursively get all child nodes under a parent node and all parent nodes on a child node
   Where IF(exp1,exp2,exp3) will execute three expressions in turn. If 1 fails, execute 2. If it also fails, execute 3.
   FIND_IN_SET : checks if there are specified characters in the modified string,
   

create table mrm_level(
    id int auto_increment,
    name char(255) not null ,
    parentId int not null,
    contentId int not null ,
    path longtext ,
    type boolean,
    primary key (id)
)engine = InnoDB default charset = utf8;




drop function if exists getChildList;
delimiter  $$
create function getChildList(pid int) returns varchar(2000)
begin
    declare str varchar(100);
    declare cid varchar(100);
    set str = ' ';
    set cid = pid;
    while cid is not null do
        set str = concat(str,',',cid);
        select group_concat(contentId) into cid from mrm_level where find_in_set(parentId,cid)>0 ;
        end while;
    set str = substring(str,2);
    return str;
end$$;
    delimiter ;

select * from mrm_level where find_in_set(id,getChildList(2)) and type =0