MySQL 中的树形数据处理示例

创建测试表

CREATETABLE`areatest`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`parent_id`int(11)NOTNULLDEFAULT'0'COMMENT'父ID',
`order_id`int(11)NOTNULLDEFAULT'0'COMMENT'排序ID',
`name`varchar(50)NOTNULLCOMMENT'名称',
PRIMARYKEY(`id`)
)ENGINE=MyISAMAUTO_INCREMENT=0DEFAULTCHARSET=utf8COMMENT='地区';
insertinto`areatest`values(1,0,1,'北京'),(2,0,3,'江西'),(3,0,2,'上海'),(4,2,1,'南昌'),(5,2,2,'九江'),(6,4,1,'永修'),(7,4,2,'星子'),(8,2,3,'赣州');

创建树形层次分析存储过程

DELIMITER$$
DROPPROCEDUREIFEXISTS`sp_areatest_child_nodes`$$
/*获取指定父ID树形层次数据放到child_nodes临时表*/
CREATEPROCEDURE`sp_areatest_child_nodes`(IN$parent_idINT/*父ID*/)
BEGIN
DECLARELEVELSMALLINTDEFAULT0;/*层次*/
DECLAREcouINTDEFAULT0;
DROPTABLEIFEXISTSchild_nodes_temp;/*单层次临时表*/
DROPTABLEIFEXISTSchild_nodes;/*层次临时表*/
CREATETEMPORARYTABLEchild_nodes_temp(`id`INT,`level`INT,`order_id`VARCHAR(1000))DEFAULTCHARSET=utf8;
CREATETEMPORARYTABLEchild_nodes(`id`INT,`level`INT,`order_id`VARCHAR(1000))DEFAULTCHARSET=utf8;
INSERTINTOchild_nodes_tempSELECT`id`,LEVEL,CAST(RIGHT(100+`order_id`,2)ASCHAR)FROM`areatest`WHERE`parent_id`=$parent_id;
SELECTROW_COUNT()INTOcou;
INSERTINTOchild_nodesSELECT*FROMchild_nodes_temp;
WHILEcou>0DO
SETLEVEL=LEVEL+1;
TRUNCATETABLEchild_nodes_temp;
INSERTINTOchild_nodes_tempSELECTa.`id`,LEVEL,CONCAT(b.order_id,CAST(RIGHT(100+a.`order_id`,2)ASCHAR))FROM`areatest`a,child_nodesbWHEREa.`parent_id`=b.`id`ANDb.`level`=LEVEL-1;
SELECTROW_COUNT()INTOcou;
INSERTINTOchild_nodesSELECT*FROMchild_nodes_temp;
ENDWHILE;
DROPTABLEchild_nodes_temp;
END$$
DELIMITER;

创建树形数据查询存储过程

DELIMITER$$
DROPPROCEDUREIFEXISTS`sp_areatest_tree`$$
/*获取指定父ID树形数据*/
CREATEPROCEDURE`sp_areatest_tree`(IN$parent_idINT/*父ID*/)
BEGIN
CALLsp_areatest_child_nodes($parent_id);
SELECTa.id,CASEb.parent_idWHEN0THENb.nameELSECONCAT(SPACE(a.level*2),'|-',b.name)ENDAS`name`
FROMchild_nodesaINNERJOIN`areatest`bONa.id=b.id
ORDERBYa.order_id;
DROPTABLEchild_nodes;
END$$
DELIMITER;

测试1

CALL sp_areatest_tree(0);

测试2

CALL sp_areatest_tree(2);