MySQL存储过程实现Oracle邻接模型树形处理的方法实例

小浪SEO博客 专注SEO优化思维、SEO技术的实战分享类博客~

项目需求用到了邻接模型,但是是采用开源的Mysql,而Mysql没有这个功能,Oracle数据库提供了现在的分析方法 connect by 处理邻接模型,不过mysql支持存储过程,可以建立存储过程实现Oracle的分析功能.

数据库对层次结构的处理模型有好多种,可以根据自己的需求来设计模型,当然最简单的也是最容易设计的模型就是所谓的邻接模型,在这方面,其他数据库比如Oracle 提供了现成的分析方法 connect by,而MySQL在这方面就显得有些薄弱了,不过可以用MySQL的存储过程实现ORACLE类似的分析功能.

这样,先来创建一个简单的数表,代码如下:

  1. createtablecountry(idnumber(2)notnull,namevarchar(60)notnull);
  3. createtablecountry_relation(idnumber(2),parentidnumber(2));
  5. createtablecountry_relation(idnumber(2),parentidnumber(2));

插入一些数据,代码如下:

  1. --Tablecountry.
  2. insertintocountry(id,name)values(0,'Earth');
  3. insertintocountry(id,name)values(2,'NorthAmerica');
  4. insertintocountry(id,name)values(3,'SouthAmerica');
  5. insertintocountry(id,name)values(4,'Europe');
  6. insertintocountry(id,name)values(5,'Asia');
  7. insertintocountry(id,name)values(6,'Africa');
  8. insertintocountry(id,name)values(7,'Australia');
  9. insertintocountry(id,name)values(8,'Canada');
  10. insertintocountry(id,name)values(9,'CentralAmerica');
  11. insertintocountry(id,name)values(10,'IslandNations');
  12. insertintocountry(id,name)values(11,'UnitedStates');
  13. insertintocountry(id,name)values(12,'Alabama');
  14. insertintocountry(id,name)values(13,'Alaska');
  15. insertintocountry(id,name)values(14,'Arizona');
  16. insertintocountry(id,name)values(15,'Arkansas');
  17. insertintocountry(id,name)values(16,'California');
  20. --Tablecountry_relation.
  21. insertintocountry_relation(id,parentid)values(0,NULL);
  22. insertintocountry_relation(id,parentid)values(2,0);
  23. insertintocountry_relation(id,parentid)values(3,0);
  24. insertintocountry_relation(id,parentid)values(4,0);
  25. insertintocountry_relation(id,parentid)values(5,0);
  26. insertintocountry_relation(id,parentid)values(6,0);
  27. insertintocountry_relation(id,parentid)values(7,0);
  28. insertintocountry_relation(id,parentid)values(8,2);
  29. insertintocountry_relation(id,parentid)values(9,2);
  30. insertintocountry_relation(id,parentid)values(10,2);
  31. insertintocountry_relation(id,parentid)values(11,2);
  32. insertintocountry_relation(id,parentid)values(12,11);
  33. insertintocountry_relation(id,parentid)values(13,11);
  34. insertintocountry_relation(id,parentid)values(14,11);
  35. insertintocountry_relation(id,parentid)values(15,11);
  36. insertintocountry_relation(id,parentid)values(16,11);

在Oracle 里面,对这些操作就比较简单了,都是系统提供的,比如下面四种情形.

1).查看深度,代码如下:

  1. selectmax(level)"level"fromCOUNTRY_RELATIONastartwitha.parentidisNULL
  2. connectbyPRIORa.id=a.PARENTID
  3. orderbylevel;
  5. level
  6. ----------
  7. 4
  9. --已用时间:00:00:00.03

2).查看叶子节点,代码如下:

  1. selectnamefrom
  2. (
  3. selectb.name,connect_by_isleaf"isleaf"
  4. fromCOUNTRY_RELATIONainnerjoincountrybon(a.id=b.id)
  5. startwitha.parentidisNULLconnectbypriora.id=a.PARENTID
  6. )TwhereT."isleaf"=1;
  9. NAME
  10. --------------------------------------------------
  11. Canada
  12. CentralAmerica
  13. IslandNations
  14. Alabama
  15. Alaska
  16. Arizona
  17. Arkansas
  18. California
  19. SouthAmerica
  20. Europe
  21. Asia
  22. Africa
  23. Australia
  25. --已选择13行。
  27. --已用时间:00:00:00.01

3).查看ROOT节点,代码如下:

  1. selectconnect_by_rootb.name
  2. fromCOUNTRY_RELATIONainnerjoincountrybon(a.id=b.id)
  3. startwitha.parentidisNULLconnectbya.id=a.PARENTID
  4. --phpfensi.com
  5. CONNECT_BY_ROOTB.NAME
  6. --------------------------------------------------
  7. Earth
  9. --已用时间:00:00:00.01

4).查看路径,代码如下:

  1. selectsys_connect_by_path(b.name,'/')"path"
  2. fromCOUNTRY_RELATIONainnerjoincountrybon(a.id=b.id)
  3. startwitha.parentidisNULLconnectbypriora.id=a.PARENTID
  4. orderbylevel,a.id;
  6. path
  7. --------------------------------------------------
  8. /Earth
  9. /Earth/NorthAmerica
  10. /Earth/SouthAmerica
  11. /Earth/Europe
  12. /Earth/Asia
  13. /Earth/Africa
  14. /Earth/Australia
  15. /Earth/NorthAmerica/Canada
  16. /Earth/NorthAmerica/CentralAmerica
  17. /Earth/NorthAmerica/IslandNations
  18. /Earth/NorthAmerica/UnitedStates
  19. /Earth/NorthAmerica/UnitedStates/Alabama
  20. /Earth/NorthAmerica/UnitedStates/Alaska
  21. /Earth/NorthAmerica/UnitedStates/Arizona
  22. /Earth/NorthAmerica/UnitedStates/Arkansas
  23. /Earth/NorthAmerica/UnitedStates/California
  25. --已选择16行。
  27. --已用时间:00:00:00.01

接下来我们看看在MySQL 里面如何实现上面四种情形,前三种都比较简单,可以很容易写出SQL.

1)查看深度,代码如下:

  1. mysql>SELECTCOUNT(DISTINCTIFNULL(parentid,-1))ASLEVELFROMcountry_relation
  2. ;
  3. +-------+
  4. |LEVEL|
  5. +-------+
  6. |4|
  7. +-------+
  8. 1rowinset(0.00sec)

2)查看ROOT节点,代码如下:

  1. mysql>SELECTb.`name`ASroot_nodeFROM
  2. ->(
  3. ->SELECTidFROMcountry_relationWHEREparentidISNULL
  4. ->)ASa,countryASbWHEREa.id=b.id;
  5. +-----------+
  6. |root_node|
  7. +-----------+
  8. |Earth|
  9. +-----------+
  10. 1rowinset(0.00sec)

3).查看叶子节点,代码如下:

  1. mysql>SELECTb.`name`ASleaf_nodeFROM
  2. ->(
  3. ->SELECTidFROMcountry_relationWHEREidNOTIN(SELECTIFNULL(parentid,
  4. -1)FROMcountry_relation)
  5. ->)ASa,countryASbWHEREa.id=b.id;
  6. +-----------------+
  7. |leaf_node|
  8. +-----------------+
  9. |SouthAmerica|
  10. |Europe|
  11. |Asia|
  12. |Africa|
  13. |Australia|
  14. |Canada|
  15. |CentralAmerica|
  16. |IslandNations|
  17. |Alabama|
  18. |Alaska|
  19. |Arizona|
  20. |Arkansas|
  21. |California|
  22. +-----------------+
  23. 13rowsinset(0.00sec)
  26. mysql>

4)查看路径

这一块没有简单的SQL实现,不过可以用MySQL的存储过程来实现同样的功能,存储过程代码如下:

  1. DELIMITER$$
  2. USE`t_girl`$$
  3. DROPPROCEDUREIFEXISTS`sp_show_list`$$
  4. CREATEDEFINER=`root`@`localhost`PROCEDURE`sp_show_list`()
  5. BEGIN
  6. --Createdbyytt2014/11/04.
  7. --Isequaltooracle'sconnectbysyntax.
  8. --Body.
  9. DROPTABLEIFEXISTStmp_country_list;
  10. CREATETEMPORARYTABLEtmp_country_list(node_levelINTUNSIGNEDNOTNULL,node_pathVARCHAR(1000)NOTNULL);
  11. --Gettherootnode.
  12. INSERTINTOtmp_country_listSELECT1,CONCAT('/',id)FROMcountry_relationWHEREparentidISNULL;
  13. --Loopwithinallparentnode.
  14. cursor1:BEGIN
  15. DECLAREdone1INTDEFAULT0;
  16. DECLAREi1INTDEFAULT1;
  17. DECLAREv_parentidINTDEFAULT-1;
  18. DECLAREv_node_pathVARCHAR(1000)DEFAULT'';
  19. DECLAREcr1CURSORFORSELECTparentidFROMcountry_relationWHEREparentidISNOTNULLGROUPBYparentidORDERBYparentidASC;
  20. DECLARECONTINUEHANDLERFORNOTFOUNDSETdone1=1;
  22. OPENcr1;
  24. loop1:LOOP
  25. FETCHcr1INTOv_parentid;
  26. IFdone1=1THEN
  27. LEAVEloop1;
  28. ENDIF;
  29. SETi1=i1+1;
  31. label_path:BEGIN
  32. DECLAREdone2INTDEFAULT0;
  33. DECLARECONTINUEHANDLERFORNOTFOUNDSETdone2=1;
  34. --Gettheupperpath.
  35. SELECTnode_pathFROMtmp_country_listWHEREnode_level=i1-1ANDLOCATE(v_parentid,node_path)>0INTOv_node_path;
  36. --Escapetheouternotfoundexception.
  37. IFdone2=1THEN
  38. SETdone2=0;
  39. ENDIF;
  40. INSERTINTOtmp_country_list
  41. SELECTi1,CONCAT(IFNULL(v_node_path,''),'/',id)FROMcountry_relationWHEREparentid=v_parentid;
  42. END;
  43. ENDLOOP;
  45. CLOSEcr1;
  47. END;
  48. --Updatenode'sidtoitsrealname.
  49. update_name_label:BEGIN
  50. DECLAREcntINTDEFAULT0;
  51. DECLAREi2INTDEFAULT0;
  52. SELECTMAX(node_level)FROMtmp_country_listINTOcnt;
  53. WHILEi2<cnt
  54. DO
  55. UPDATEtmp_country_listASa,countryASb
  56. SETa.node_path=REPLACE(a.node_path,CONCAT('/',b.id),CONCAT('/',b.name))
  57. WHERELOCATE(CONCAT('/',b.id),a.node_path)>0;
  58. SETi2=i2+1;
  59. ENDWHILE;
  60. END;
  62. SELECTnode_pathFROMtmp_country_list;
  63. END$$
  66. DELIMITER;

调用结果,代码如下:

  1. mysql>CALLsp_show_list();
  2. +-----------------------------------------------+
  3. |node_path|
  4. +-----------------------------------------------+
  5. |/Earth|
  6. |/Earth/NorthAmerica|
  7. |/Earth/SouthAmerica|
  8. |/Earth/Europe|
  9. |/Earth/Asia|
  10. |/Earth/Africa|
  11. |/Earth/Australia|
  12. |/Earth/NorthAmerica/Canada|
  13. |/Earth/NorthAmerica/CentralAmerica|
  14. |/Earth/NorthAmerica/IslandNations|
  15. |/Earth/NorthAmerica/UnitedStates|
  16. |/Earth/NorthAmerica/UnitedStates/Alabama|
  17. |/Earth/NorthAmerica/UnitedStates/Alaska|
  18. |/Earth/NorthAmerica/UnitedStates/Arizona|
  19. |/Earth/NorthAmerica/UnitedStates/Arkansas|
  20. |/Earth/NorthAmerica/UnitedStates/California|
  21. +-----------------------------------------------+
  22. 16rowsinset(0.04sec)
  24. QueryOK,0rowsaffected(0.08sec)
  26. mysql>

相关广告
  • MySQL存储过程实现Oracle邻接模型树形处理的方法实例 MySQL存储过程实现Oracle邻接模型树形处理的方法实例 MySQL存储过程实现Oracle邻接模型树形处理的方法实例
相关阅读

MySQL存储过程实现Oracle邻接模型树形处理的方法实例

2019/10/10 17:32:08 | 谷歌SEO算法 | 搜索引擎