drop table if exists node;
CREATE TABLE `node` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `pid` int(11) unsigned NOT NULL DEFAULT '0',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '名稱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='節(jié)點表';

drop table if exists node_tree_paths;
CREATE TABLE `node_tree_paths` (
  `ancestor` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '祖先節(jié)點',
  `descendant` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '后代節(jié)點',
  `distance` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '祖先距離后代的距離',
  PRIMARY KEY (`ancestor`,`descendant`),
  KEY `descendant` (`descendant`),
  CONSTRAINT `ancestor` FOREIGN KEY (`ancestor`) REFERENCES `node` (`id`),
  CONSTRAINT `descendant` FOREIGN KEY (`descendant`) REFERENCES `node` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='節(jié)點關系表';

drop procedure if exists AddNode;
CREATE PROCEDURE `AddNode`(_parent_name varchar(255), _node_name varchar(255))
BEGIN
    DECLARE _ancestor INT;
    DECLARE _descendant INT;
    DECLARE _parent INT;
    IF NOT EXISTS(SELECT id From node WHERE name = _node_name)
    THEN
				-- 入庫 			
        INSERT INTO node (name) VALUES(_node_name);
				-- 入庫ID
				SET _descendant = (select @@IDENTITY);
				-- 自己到自己的鏈信息
        INSERT INTO node_tree_paths (ancestor,descendant,distance) VALUES(_descendant,_descendant,0);
				-- 上級是否存在
        IF EXISTS (SELECT id FROM node WHERE name = _parent_name)
        THEN
            SET _parent = (SELECT id FROM node WHERE name = _parent_name);
            INSERT INTO node_tree_paths (ancestor,descendant,distance) SELECT ancestor,_descendant,distance+1 from node_tree_paths where descendant = _parent;
        END IF;
    END IF;
END

call AddNode('', '中國');
call AddNode('中國', '華東');
call AddNode('中國', '華南');
call AddNode('中國', '華西');
call AddNode('中國', '華北');

call AddNode('華東', '江蘇');
call AddNode('華東', '浙江');
call AddNode('華東', '山東');
call AddNode('華東', '安徽');
call AddNode('華東', '江西');

call AddNode('江蘇', '南京');
call AddNode('南京', '六合區(qū)');

SELECT
	n3.name 
FROM
	node n1
	INNER JOIN node_tree_paths n2 ON n1.id = n2.ancestor
	INNER JOIN node n3 ON n2.descendant = n3.id 
WHERE
	n1.name = '華東' 
	AND n2.distance != 0

SELECT
    n3.name
FROM
    node n1
INNER JOIN node_tree_paths n2 ON n1.id = n2.ancestor
INNER JOIN node n3 ON n2.descendant = n3.id
WHERE
    n1.name = '華東'
AND n2.distance = 1

SELECT
    n2.*, n3.name
FROM
    node n1
INNER JOIN node_tree_paths n2 ON n1.id = n2.descendant
INNER JOIN node n3 ON n2.ancestor = n3.id
WHERE
    n1.name = '六合區(qū)'
ORDER BY
    n2.distance DESC