CREATE DEFINER = `root`@`localhost` FUNCTION `x_numer`(`numer` int,`denom` int) RETURNSint(11) BEGIN DECLARE ret_num INT; DECLARE ret_den INT; SET ret_num := numer+1; SET ret_den := denom*2; WHILE floor(ret_num/2) = ret_num/2 DO SET ret_num := ret_num/2; SET ret_den := ret_den/2; END WHILE; RETURN ret_num; END;
x点的分母为:
1 2 3 4 5 6 7 8 9 10 11 12 13
CREATE DEFINER = `root`@`localhost` FUNCTION `x_ denom`(`numer` int,`denom` int) RETURNSint(11) BEGIN DECLARE ret_num INT; DECLARE ret_den INT; SET ret_num := numer+1; SET ret_den := denom*2; WHILE floor(ret_num/2) = ret_num/2 DO SET ret_num := ret_num/2; SET ret_den := ret_den/2; END WHILE; RETURN ret_den; END;
Y点的分子:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
CREATE DEFINER = `root`@`localhost` FUNCTION `y_numer`(`numer` int,`denom` int) RETURNSint(11) BEGIN DECLARE num INT; DECLARE den INT; SET num := x_numer(numer, denom); SET den := x_denom(numer, denom); WHILE den < denom DO SET num := num*2; SET den := den*2; END WHILE; SET num := (numer - num); WHILE floor(num/2) = num/2 DO SET num := num/2; SET den := den/2; END WHILE; RETURN num; END;
Y 的分母:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
CREATE DEFINER = `root`@`localhost` FUNCTION `y_denom`(`numer` int,`denom` int) RETURNSint(11) BEGIN DECLARE num INT; DECLARE den INT; SET num := x_numer(numer, denom); SET den := x_denom(numer, denom); WHILE den < denom DO SET num := num*2; SET den := den*2; END WHILE; SET num := (numer - num); WHILE floor(num/2) = num/2 DO SET num := num/2; SET den := den/2; END WHILE; RETURN den; END;
接下来我们来测试下,X与Y是否能解码出来:
1 2 3
SELECT CONCAT(x_numer (11, 8),'/',x_denom (11, 8)) AS X, CONCAT(y_numer (11, 8),'/',y_denom (11, 8)) AS Y
CREATE DEFINER = `root`@`localhost` FUNCTION `parent_numer`(`numer` int,`denom` int) RETURNSint(11) BEGIN DECLARE ret_num INT; DECLARE ret_den INT; IF numer=3THEN RETURN denom/2; END IF; SET ret_num := (numer-1)/2; SET ret_den := denom/2; WHILE floor((ret_num-1)/4) = (ret_num-1)/4 DO SET ret_num := (ret_num+1)/2; SET ret_den := ret_den/2; END WHILE; RETURN ret_num; END;
SELECT CONCAT(parent_numer(11,8),'/',parent_denom(11,8)) AS parent
计算当前节点在同级所在的位置:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
CREATE DEFINER = `root`@`localhost` FUNCTION `parent_denom`(`numer` int,`denom` int) RETURNSint(11) BEGIN DECLARE ret_num INT; DECLARE ret_den INT; IF numer=3THEN RETURNNULL; END IF; SET ret_num := (numer-1)/2; SET ret_den := denom/2; WHILE floor((ret_num-1)/4) = (ret_num-1)/4 DO SET ret_num := (ret_num+1)/2; SET ret_den := ret_den/2; END WHILE; RETURN ret_den; END;
CREATE DEFINER = `root`@`localhost` FUNCTION `path`(`numer` int,`denom` int) RETURNSvarchar(255) BEGIN IF numer isNULLTHEN RETURN''; END IF; RETURN path(parent_numer(numer, denom),parent_denom(numer, denom))|| ‘.’ || sibling_number(numer, denom); END;
按照以上方法添加后进行测试,返回 **[Err] 1424 – Recursive stored functions and triggers are not allowed.**即MySQL的自定义函数不支持递归查询。
CREATE DEFINER = `root`@`localhost` FUNCTION `path`(`numer` int,`denom` int) RETURNSvarchar(255) BEGIN DECLARE numer_temp INT; DECLARE denom_temp INT; DECLARE path_result VARCHAR(255); DECLARE path_temp VARCHAR(255); DECLARE sn VARCHAR(255); SET path_temp :=''; WHILE numer ISNOTNULL DO IF path_temp ='' THEN SET path_result := sibling_number(numer, denom); ELSE SET path_result := CONCAT(sibling_number(numer, denom),'.',path_temp); END IF; SET path_temp := path_result; SET numer_temp := parent_numer(numer, denom); SET denom_temp := parent_denom(numer, denom); SET numer := numer_temp; SET denom := denom_temp; END WHILE; RETURN path_result; END;
SELECT path (11, 8) 的结果为 1.2
计算节点层级的方法:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
CREATE DEFINER = `root`@`localhost` FUNCTION `node_level`(`numer` int,`denom` int) RETURNSint(11) BEGIN DECLARE ret_num INT; DECLARE ret_den INT; DECLARE ret INT; SET ret =1; IF numer=3THEN return1; END IF; WHILE numer!=3 DO SET ret_num := parent_numer(numer, denom); SET ret_den := parent_denom(numer, denom); SET numer := ret_num; SET denom := ret_den; SET ret := ret +1; END WHILE; RETURN ret; END;
我们知道了如何将编码过的节点转成目录形式,如何逆转呢?以下是方法:
先添加2个辅助函数:
1 2 3 4 5
CREATE DEFINER = `root`@`localhost` FUNCTION `child_numer`(`num` int,`den` int,`child` int) RETURNSint(11) BEGIN RETURN num *power(2, child) +3-power(2, child); END;
1 2 3 4 5
CREATE DEFINER = `root`@`localhost` FUNCTION `child_denom`(`num` int,`den` int,`child` int) RETURNSint(11) BEGIN RETURN den*power(2, child); END;
再来编写逆转函数:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
CREATE DEFINER = `root`@`localhost` FUNCTION `path_numer`(`path` varchar(255)) RETURNSint(11) BEGIN DECLARE num INT; DECLARE den INT; DECLARE postfix VARCHAR(255); DECLARE sibling VARCHAR(255); SET num :=1; SET den :=1; SET postfix := CONCAT(path,'.'); WHILE length(postfix) >1 DO SET sibling := SUBSTR(postfix, 1, instr(postfix,'.')-1); SET postfix := SUBSTR(postfix, instr(postfix,'.')+1); SET num := child_numer(num,den,sibling+0); SET den := child_denom(num,den,sibling+0); END WHILE; RETURN num; END;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
CREATE DEFINER = `root`@`localhost` FUNCTION `path_denom`(`path` varchar(255)) RETURNSint(11) BEGIN DECLARE num INT; DECLARE den INT; DECLARE postfix VARCHAR(255); DECLARE sibling VARCHAR(255); SET num :=1; SET den :=1; SET postfix := CONCAT(path,'.'); WHILE length(postfix) >1 DO SET sibling := SUBSTR(postfix, 1, instr(postfix,'.')-1); SET postfix := SUBSTR(postfix, instr(postfix,'.')+1); SET num := child_numer(num,den,sibling+0); SET den := child_denom(num,den,sibling+0); END WHILE; RETURN den; END;