{"id":728,"date":"2016-08-28T17:09:48","date_gmt":"2016-08-28T09:09:48","guid":{"rendered":"http:\/\/www.xttblog.com\/?p=728"},"modified":"2016-08-28T17:09:48","modified_gmt":"2016-08-28T09:09:48","slug":"mysql%e5%ae%9e%e7%8e%b0%e6%a0%91%e5%bd%a2%e7%9a%84%e9%81%8d%e5%8e%86%e3%80%81%e9%83%a8%e9%97%a8%e9%80%92%e5%bd%92%e3%80%81%e8%8f%9c%e5%8d%95%e7%ba%a7%e8%81%94","status":"publish","type":"post","link":"https:\/\/www.xttblog.com\/?p=728","title":{"rendered":"MySQL\u5b9e\u73b0\u6811\u5f62\u7684\u904d\u5386\u3001\u90e8\u95e8\u9012\u5f52\u3001\u83dc\u5355\u7ea7\u8054"},"content":{"rendered":"
\n

\n\t\t\u5173\u4e8e\u591a\u7ea7\u522b\u83dc\u5355\u680f\u6216\u8005\u6743\u9650\u7cfb\u7edf\u4e2d\u90e8\u95e8\u4e0a\u4e0b\u7ea7\u7684\u6811\u5f62\u904d\u5386\uff0coracle\u4e2d\u6709connect by\u6765\u5b9e\u73b0\uff0cMySQL\u6ca1\u6709\u8fd9\u6837\u7684\u4fbf\u6377\u9014\u5f84\uff0c\u6240\u4ee5MySQL\u904d\u5386\u6570\u636e\u8868\u662f\u6211\u4eec\u7ecf\u5e38\u4f1a\u9047\u5230\u7684\u5934\u75db\u95ee\u9898\uff0c\u4e0b\u9762\u901a\u8fc7\u5b58\u50a8\u8fc7\u7a0b\u6765\u5b9e\u73b0\u3002\n\t<\/p>\n<\/blockquote>\n

\n\t\u5728\u5f00\u59cb\u4e4b\u524d\u6211\u4eec\u5148\u5efa\u7acb\u6d4b\u8bd5\u8868\u548c\u6570\u636e\uff0c\u5982\u4e0b\uff1a\n<\/p>\n

\r\nDROP TABLE IF EXISTS dept;   \r\nCREATE TABLE dept (   \r\n  id INT(11) NOT NULL AUTO_INCREMENT,     \r\n  dept_name VARCHAR(200) DEFAULT NULL,   \r\n  parent_id INT(11) DEFAULT NULL,   \r\n  PRIMARY KEY (id)   \r\n) ENGINE=INNODB DEFAULT CHARSET=utf8;   \r\nINSERT INTO dept(id,dept_name,parent_id)    \r\nVALUES (13,'\u7814\u53d1\u90e8\u95e8',-1),   \r\n       (14,'\u8d22\u52a1\u90e8',-1),   \r\n       (15,'\u5e02\u573a\u90e8',-1),   \r\n       (16,'\u7814\u53d1\u4e00\u90e8',13),   \r\n       (17,'\u4e0a\u6d77\u8d22\u52a1\u7ec4',14),   \r\n       (18,'\u534e\u4e1c\u5e02\u573a\u4e1a\u52a1\u7ec4',17);  <\/pre>\n
\n\t\u63a5\u4e0b\u6765\u6211\u4eec\u53ef\u4ee5\u5229\u7528\u4e34\u65f6\u8868\u548c\u9012\u5f52\u8fc7\u7a0b\u5b9e\u73b0\u6811\u7684\u904d\u5386\uff08\u6ce8\u610f\uff1amysql\u7684UDF\u4e0d\u80fd\u9012\u5f52\u8c03\u7528\uff09\n<\/p>\n
\n\t\u4ece\u67d0\u8282\u70b9\u5411\u4e0b\u904d\u5386\u5b50\u8282\u70b9\uff0c\u9012\u5f52\u751f\u6210\u4e34\u65f6\u8868\u6570\u636e
\n<\/h2>\n
\r\nDELIMITER $$     \r\nDROP PROCEDURE IF EXISTS xttblog.pro_cre_childlist$$   \r\nCREATE PROCEDURE xttblog.pro_cre_childlist(IN rootId INT,IN nDepth INT)   \r\nBEGIN   \r\n      DECLARE done INT DEFAULT 0;   \r\n      DECLARE b INT;   \r\n      DECLARE cur1 CURSOR FOR SELECT id FROM dept WHERE parent_id=rootId;   \r\n      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;   \r\n      SET max_sp_recursion_depth=12;   \r\n      INSERT INTO tmpLst VALUES (NULL,rootId,nDepth);   \r\n      OPEN cur1;   \r\n      FETCH cur1 INTO b;   \r\n      WHILE done=0 DO   \r\n              CALL pro_cre_childlist(b,nDepth+1);   \r\n              FETCH cur1 INTO b;   \r\n      END WHILE;   \r\n      CLOSE cur1;   \r\nEND$$\r\nDELIMITER $$\r\n<\/pre>\n
\n\t\u4ece\u67d0\u8282\u70b9\u5411\u4e0a\u8ffd\u6eaf\u6839\u8282\u70b9\uff0c\u9012\u5f52\u751f\u6210\u4e34\u65f6\u8868\u6570\u636e
\n<\/h2>\n
\r\nDELIMITER $$\r\nDROP PROCEDURE IF EXISTS xttblog.pro_cre_parentlist$$   \r\nCREATE PROCEDURE xttblog.pro_cre_parentlist(IN rootId INT,IN nDepth INT)   \r\nBEGIN   \r\n      DECLARE done INT DEFAULT 0;   \r\n      DECLARE b INT;   \r\n      DECLARE cur1 CURSOR FOR SELECT parent_id FROM dept WHERE id=rootId;   \r\n      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;   \r\n      SET max_sp_recursion_depth=12;   \r\n      INSERT INTO tmpLst VALUES (NULL,rootId,nDepth);   \r\n      OPEN cur1;   \r\n      FETCH cur1 INTO b;   \r\n      WHILE done=0 DO   \r\n              CALL pro_cre_parentlist(b,nDepth+1);   \r\n              FETCH cur1 INTO b;   \r\n      END WHILE;   \r\n      CLOSE cur1;   \r\nEND$$\r\nDELIMITER $$\r\n<\/pre>\n
\n\t\u5b9e\u73b0\u7c7b\u4f3cOracle SYS_CONNECT_BY_PATH\u7684\u529f\u80fd\uff0c\u9012\u5f52\u8fc7\u7a0b\u8f93\u51fa\u67d0\u8282\u70b9id\u8def\u5f84
\n<\/h2>\n
\r\nDELIMITER $$\r\nUSE db$$\r\nDROP PROCEDURE IF EXISTS pro_cre_pathlist$$\r\nCREATE PROCEDURE pro_cre_pathlist(IN nid INT,IN delimit VARCHAR(10),INOUT pathstr VARCHAR(1000))\r\nBEGIN                     \r\n      DECLARE done INT DEFAULT 0;   \r\n      DECLARE parentid INT DEFAULT 0;         \r\n      DECLARE cur1 CURSOR FOR    \r\n      SELECT t.parent_id,CONCAT(CAST(t.parent_id AS CHAR),delimit,pathstr)   \r\n        FROM dept AS t WHERE t.id = nid;   \r\n      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;   \r\n      SET max_sp_recursion_depth=12;                     \r\n      OPEN cur1;   \r\n      FETCH cur1 INTO parentid,pathstr;   \r\n      WHILE done=0 DO              \r\n              CALL pro_cre_pathlist(parentid,delimit,pathstr);   \r\n              FETCH cur1 INTO parentid,pathstr;   \r\n      END WHILE;   \r\n      CLOSE cur1;    \r\nEND$$\r\nDELIMITER $$<\/pre>\n
\n\t\u9012\u5f52\u8fc7\u7a0b\u8f93\u51fa\u67d0\u8282\u70b9name\u8def\u5f84
\n<\/h2>\n
\r\nDELIMITER $$\r\nUSE db$$\r\nDROP PROCEDURE IF EXISTS pro_cre_pnlist$$\r\nCREATE PROCEDURE pro_cre_pnlist(IN nid INT,IN delimit VARCHAR(10),INOUT pathstr VARCHAR(1000))\r\nBEGIN                     \r\n      DECLARE done INT DEFAULT 0;   \r\n      DECLARE parentid INT DEFAULT 0;         \r\n      DECLARE cur1 CURSOR FOR    \r\n      SELECT t.parent_id,CONCAT(t.dept_name,delimit,pathstr)   \r\n        FROM dept AS t WHERE t.id = nid;   \r\n      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;   \r\n      SET max_sp_recursion_depth=12;                     \r\n      OPEN cur1;   \r\n      FETCH cur1 INTO parentid,pathstr;   \r\n      WHILE done=0 DO              \r\n              CALL pro_cre_pnlist(parentid,delimit,pathstr);   \r\n              FETCH cur1 INTO parentid,pathstr;   \r\n      END WHILE;   \r\n      CLOSE cur1;    \r\nEND$$\r\nDELIMITER $$<\/pre>\n
\n\t\u8c03\u7528\u51fd\u6570\u8f93\u51faid\u8def\u5f84
\n<\/h2>\n
\r\nDELIMITER $$ \r\nDROP FUNCTION IF EXISTS db.fn_tree_path$$   \r\nCREATE FUNCTION db.fn_tree_path(nid INT,delimit VARCHAR(10)) RETURNS VARCHAR(2000) CHARSET utf8   \r\nBEGIN     \r\n  DECLARE pathid VARCHAR(1000);   \r\n  SET @pathid=CAST(nid AS CHAR);   \r\n  CALL pro_cre_pathlist(nid,delimit,@pathid);   \r\n  RETURN @pathid;   \r\nEND$$\r\nDELIMITER $$<\/pre>\n
\n\t\u8c03\u7528\u51fd\u6570\u8f93\u51faname\u8def\u5f84
\n<\/h2>\n
\r\nDELIMITER $$\r\nDROP FUNCTION IF EXISTS db.fn_tree_pathname$$   \r\nCREATE FUNCTION csdn.fn_tree_pathname(nid INT,delimit VARCHAR(10)) RETURNS VARCHAR(2000) CHARSET utf8   \r\nBEGIN     \r\n  DECLARE pathid VARCHAR(1000);   \r\n  SET @pathid='';       \r\n  CALL pro_cre_pnlist(nid,delimit,@pathid);   \r\n  RETURN @pathid;   \r\nEND$$\r\nDELIMITER $$ <\/pre>\n
\n\t\u8c03\u7528\u8fc7\u7a0b\u8f93\u51fa\u5b50\u8282\u70b9
\n<\/h2>\n
\r\nDELIMITER $$   \r\nDROP PROCEDURE IF EXISTS pro_show_childLst$$   \r\nCREATE PROCEDURE pro_show_childLst(IN rootId INT)   \r\nBEGIN   \r\n      DROP TEMPORARY TABLE IF EXISTS tmpLst;   \r\n      CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst    \r\n       (sno INT PRIMARY KEY AUTO_INCREMENT,id INT,depth INT);          \r\n      CALL pro_cre_childlist(rootId,0);   \r\n      SELECT d.id,CONCAT(SPACE(tmpLst.depth*2),'--',d.dept_name) NAME,d.parent_id,tmpLst.depth,fn_tree_path(d.id,'\/') path,fn_tree_pathname(d.id,'\/') pathname   \r\n      FROM tmpLst,dept d WHERE tmpLst.id=d.id ORDER BY tmpLst.sno;   \r\nEND$$\r\nDELIMITER $$<\/pre>\n
\n\t\u8c03\u7528\u8fc7\u7a0b\u8f93\u51fa\u7236\u8282\u70b9
\n<\/h2>\n
\r\nDELIMITER $$  \r\nDROP PROCEDURE IF EXISTS `pro_show_parentLst`$$   \r\nCREATE PROCEDURE `pro_show_parentLst`(IN rootId INT)   \r\nBEGIN   \r\n      DROP TEMPORARY TABLE IF EXISTS tmpLst;   \r\n      CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst    \r\n       (sno INT PRIMARY KEY AUTO_INCREMENT,id INT,depth INT);         \r\n      CALL pro_cre_parentlist(rootId,0);   \r\n      SELECT d.id,CONCAT(SPACE(tmpLst.depth*2),'--',d.dept_name) NAME,d.parent_id,tmpLst.depth,fn_tree_path(d.id,'\/') path,fn_tree_pathname(d.id,'\/') pathname   \r\n      FROM tmpLst,dept d WHERE tmpLst.id=d.id ORDER BY tmpLst.sno;   \r\nEND$$ \r\nDELIMITER $$<\/pre>\n
\n\t\u53c2\u8003\u7f51\u5740\uff1a<\/strong>
\n\thttp:\/\/jan.kneschke.de\/projects\/mysql\/sp\/sp_tree.sql<\/a>
\n\thttp:\/\/blog.csdn.net\/ylqmf\/article\/details\/5172901<\/a>\n<\/p>\n
\n\t\u7248\u6743\u58f0\u660e\uff1a\u672c\u6587\u4e3a\u535a\u4e3b\u539f\u521b\u6587\u7ae0\uff0c\u672a\u7ecf\u535a\u4e3b\u5141\u8bb8\u4e0d\u5f97\u8f6c\u8f7d\u3002<\/strong><\/p>\n
\"\u4e1a\u4f59\u8349\u516c\u4f17\u53f7\"\/<\/p>
\u6700\u540e\uff0c\u6b22\u8fce\u5173\u6ce8\u6211\u7684\u4e2a\u4eba\u5fae\u4fe1\u516c\u4f17\u53f7\uff1a\u4e1a\u4f59\u8349\uff08yyucao\uff09\uff01\u53ef\u52a0\u4f5c\u8005\u5fae\u4fe1\u53f7\uff1axttblog2<\/font>\u3002\u5907\u6ce8\uff1a\u201c1\u201d\uff0c\u6dfb\u52a0\u535a\u4e3b\u5fae\u4fe1\u62c9\u4f60\u8fdb\u5fae\u4fe1\u7fa4\u3002\u5907\u6ce8\u9519\u8bef\u4e0d\u4f1a\u540c\u610f\u597d\u53cb\u7533\u8bf7\u3002<\/b>\u518d\u6b21\u611f\u8c22\u60a8\u7684\u5173\u6ce8\uff01\u540e\u7eed\u6709\u7cbe\u5f69\u5185\u5bb9\u4f1a\u7b2c\u4e00\u65f6\u95f4\u53d1\u7ed9\u60a8\uff01\u539f\u521b\u6587\u7ae0\u6295\u7a3f\u8bf7\u53d1\u9001\u81f3532009913@qq.com\u90ae\u7bb1\u3002\u5546\u52a1\u5408\u4f5c\u4e5f\u53ef\u6dfb\u52a0\u4f5c\u8005\u5fae\u4fe1\u8fdb\u884c\u8054\u7cfb\uff01<\/p>
\u672c\u6587\u539f\u6587\u51fa\u5904\uff1a\u4e1a\u4f59\u8349<\/a>\uff1a » MySQL\u5b9e\u73b0\u6811\u5f62\u7684\u904d\u5386\u3001\u90e8\u95e8\u9012\u5f52\u3001\u83dc\u5355\u7ea7\u8054<\/a><\/p>","protected":false},"excerpt":{"rendered":"
\u5173\u4e8e\u591a\u7ea7\u522b\u83dc\u5355\u680f\u6216\u8005\u6743\u9650\u7cfb\u7edf\u4e2d\u90e8\u95e8\u4e0a\u4e0b\u7ea7\u7684\u6811\u5f62\u904d\u5386\uff0coracle\u4e2d\u6709connect by\u6765\u5b9e\u73b0\uff0cMySQL\u6ca1\u6709 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[83,447,448],"class_list":["post-728","post","type-post","status-publish","format-standard","hentry","category-sql","tag-mysql"],"_links":{"self":[{"href":"https:\/\/www.xttblog.com\/index.php?rest_route=\/wp\/v2\/posts\/728","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.xttblog.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.xttblog.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.xttblog.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.xttblog.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=728"}],"version-history":[{"count":2,"href":"https:\/\/www.xttblog.com\/index.php?rest_route=\/wp\/v2\/posts\/728\/revisions"}],"predecessor-version":[{"id":730,"href":"https:\/\/www.xttblog.com\/index.php?rest_route=\/wp\/v2\/posts\/728\/revisions\/730"}],"wp:attachment":[{"href":"https:\/\/www.xttblog.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=728"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.xttblog.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=728"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.xttblog.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=728"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}