mysql FIND_IN_SET 尝试性优化

原函数

CREATE DEFINER=`root`@`localhost` FUNCTION `getDepartList`(departid VARCHAR(2000)) RETURNS varchar(1000) CHARSET utf8mb4
BEGIN

  DECLARE pTemp VARCHAR(1000);  
  DECLARE cTemp VARCHAR(1000);  
  
  SET pTemp = '$';  
  SET cTemp =CAST(departId AS CHAR);  

  WHILE cTemp is not null DO  
     SET pTemp = concat(pTemp,',',cTemp);  
     SELECT group_concat(ID) INTO cTemp FROM t_s_depart   
     WHERE FIND_IN_SET(parentdepartid,cTemp)>0;
  END WHILE;  

  RETURN pTemp;  

END

 

1.尝试使用索引【使用Explain 目测有点效果】

2.优化函数

　　1.使用=条件替代【有些sb，命中索引，但查询次数过多，效率并没有多大提升】

CREATE DEFINER = 'root'@'%'
FUNCTION gxgx.getDepartList(departid VARCHAR(100))
  RETURNS VARCHAR(5000) CHARSET utf8mb4
BEGIN

  DECLARE tempParentDepartid varchar(32);
  DECLARE resultTemp VARCHAR(5000);  
  DECLARE cTemp VARCHAR(1000);  
  DECLARE countNum int(9);
  DECLARE iteratorNum int(9);


  SET tempParentDepartid = CAST(departId AS char);
  SET resultTemp = CONCAT('$',',',tempParentDepartid);
  SET countNum = 2;
  SET iteratorNum = 2;

  WHILE iteratorNum <= countNum  DO
    SET tempParentDepartid = SUBSTRING_INDEX(SUBSTRING_INDEX(resultTemp,',',iteratorNum),',',-1);
    SELECT GROUP_CONCAT(ID) INTO cTemp FROM t_s_depart WHERE parentdepartid = tempParentDepartid;
    IF cTemp IS NOT NULL THEN
    SET resultTemp = CONCAT(resultTemp,',',cTemp);
    SET countNum = LENGTH(resultTemp) - LENGTH(REPLACE(resultTemp,',','')) + 1;
    END IF;
    SET iteratorNum = iteratorNum + 1;
        -- set cTemp = null;
    -- SELECT ID INTO cTemp FROM TMP LIMIT 1;
  END WHILE;  
    -- drop temporary table if exists tmp;
  RETURN resultTemp;  

END

　　2.使用临时表【一旦函数出错，可能临时表没有删除导致函数无法正确执行，该函数在开发过程中废弃，因此不保证正确】

CREATE DEFINER = 'root'@'%'
FUNCTION gxgx.getDepartList(departid VARCHAR(100))
  RETURNS VARCHAR(5000) CHARSET utf8mb4
BEGIN
  DECLARE pTemp VARCHAR(5000);  
  DECLARE cTemp VARCHAR(100);  
  CREATE TEMPORARY TABLE tmp (
  id varchar(100) primary key
  );
  SET pTemp = '$';
  SET cTemp = CAST(departId AS char);
  INSERT INTO tmp(id) VALUES(cTemp); 
  WHILE cTemp is not NULL DO  
    DELETE FROM tmp WHERE ID = cTemp;
    SET pTemp = CONCAT(pTemp,',',cTemp);
    INSERT INTO tmp(id) SELECT ID FROM t_s_depart WHERE parentdepartid = cTemp;
        -- set cTemp = null;
    SELECT ID INTO cTemp FROM TMP LIMIT 1;
  END WHILE;  
    drop temporary table if exists tmp;
  RETURN pTemp;  

END

　　3.使用IN条件

CREATE DEFINER = 'root'@'localhost'
FUNCTION gxgx.getDepartList(departid varchar(100))
  RETURNS VARCHAR(6000) CHARSET utf8mb4
BEGIN

  DECLARE pTemp varchar(6000);
  DECLARE cTemp varchar(6000);

  SET pTemp = '$';
  SET cTemp = CAST(departid AS char);

  WHILE cTemp IS NOT NULL DO
    SET pTemp = CONCAT(pTemp, ',', cTemp);
    SELECT
      GROUP_CONCAT(ID) INTO cTemp
    FROM t_s_depart
    WHERE parentdepartid IN (SELECT DISTINCT
        (SUBSTRING_INDEX (SUBSTRING_INDEX (cTemp, ',', b.help_topic_id + 1), ',', -1)) AS parentdepartid
      FROM mysql.help_topic AS b
      WHERE b.help_topic_id < (CHAR_LENGTH(cTemp) - CHAR_LENGTH(REPLACE(cTemp, ',', '')) + 1));
  END WHILE;

  RETURN pTemp;

END