Oracle conversion MySql recursive start with detailed explanation

Oracle conversion MySql to recursive start with

Oracle conversion Mysql start with

There are actually many details about Oracle to mysql, so I will not describe them one by one here. Let’s sort out some “hindrances” here first!

Start with…

  select * from 
  	grade_group gg 
  start with gg.group_code=#{groupCode} 
  connect by  gg.parent_id= prior 
  order by gg.group_level

Convert to MySql to

  SELECT
      *
  FROM
      grade_group
  WHERE FIND_IN_SET(ID, getGradeGroupChild(#{groupCode}))
  ORDER BY group_level

It's not just a simple change to SQL

It can be seen that a function needs to support "getGradeGroupChild"

This function is provided below

DROP FUNCTION IF EXISTS `getGradeGroupChild`;
DELIMITER $$ 
CREATE FUNCTION `getGradeGroupChild`(GROUPCODE VARCHAR(100)) RETURNS VARCHAR(10000) CHARSET utf8
BEGIN
   DECLARE sChildList VARCHAR(10000);
   DECLARE sChildTemp VARCHAR(1000);
   --I'm going to pass it in for the first timeGROUPCODE Convert toid，Then go to recursive query
   SELECT id INTO sChildTemp FROM grade_group WHERE GROUP_CODE = GROUPCODE;
   WHILE sChildTemp IS NOT NULL DO
	 IF (sChildList IS NOT NULL) THEN 
		SET sChildList = CONCAT(sChildTemp,',',sChildList); 
	 ELSE 
		SET sChildList = CONCAT(sChildTemp); 
	 END IF; 
     SELECT GROUP_CONCAT(id) INTO sChildTemp FROM grade_group 
     WHERE FIND_IN_SET(PARENT_ID, sChildTemp)&gt;0;
   END WHILE;
   RETURN sChildList;
 END$$
DELIMITER ;

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.