Shulou(Shulou.com)05/31 Report--

This article introduces how to recursively query parent ID or child ID in MySQL through custom functions, the content is very detailed, interested friends can refer to, I hope it can be helpful to you.

I. Environmental preparation

1. Create a table

CREATE TABLE `table_ name` (`id` int (11) NOT NULL AUTO_INCREMENT, `status` int (255) NULL DEFAULT NULL, `pid` int (11) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic

2. Insert data

INSERT INTO `table_ name` VALUES (1,12,0); INSERT INTO `table_ name` VALUES (2,4,1); INSERT INTO `table_ name` VALUES (3,8,2); INSERT INTO `table_ name` VALUES (4,16,3); INSERT INTO `table_ name`VALUES (5,32,3); INSERT INTO `table_ name`VALUES (6,64,3); INSERT INTO `table_ name` VALUES (7,128,6); INSERT INTO `table_ name` VALUES (8,256,7); INSERT INTO `table_ name` VALUES (9,512,8) INSERT INTO `table_ name` VALUES (10, 1024, 9); INSERT INTO `table_ name` VALUES (11, 2048, 10)

2. The compilation of MySQL function

1. Query all parent nodes of the current node

Delimiter / / CREATE FUNCTION `getParentList` (root_id BIGINT) RETURNS VARCHAR (1000) BEGIN DECLARE k INT DEFAULT 0; DECLARE fid INT DEFAULT 1; DECLARE str VARCHAR (1000) DEFAULT'$'; WHILE rootId > 0 DO SET fid= (SELECT pid FROM table_name WHERE root_id=id); IF fid > 0 THEN SET str = concat (str,',',fid); SET root_id= fid ELSE SET root_id=fid; END IF; END WHILE; RETURN str; END / / delimiter

2. Query all the children of the current node

Delimiter / / CREATE FUNCTION `getChildList` (root_id BIGINT) RETURNS VARCHAR (1000) BEGIN DECLARE str VARCHAR (1000); DECLARE cid VARCHAR (1000); DECLARE k INT DEFAULT 0; SET str ='$'; SET cid = CAST (root_id AS CHAR); 12 WHILE cid IS NOT NULL DO IF k > 0 THEN SET str = CONCAT (str,',',cid); END IF SELECT GROUP_CONCAT (id) INTO cid FROM table_name WHERE FIND_IN_SET (pid,cid) > 0; SET k = k + 1; END WHILE; RETURN str; END / / delimiter

III. Testing

1. Get all the parents of the current node

SELECT getParentList (10)

2. Get all the bytes of the current node

SELECT getChildList (3); on how to recursively query parent ID or child ID through custom functions in MySQL, that's it. I hope the above content can be helpful to everyone and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.