mysql表中的父树(while循环)

本文介绍了mysql表中的父树(while循环)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

对不起，我的英语. 我有一个像这样的mysql表

Sorry for my English. I have a mysql table like this

[ --------------------------] [ parent_id ] [ category_id ] [ --------------------------]

网站的结构如下:

Site structured like this:

0 -> 1 -> 2 -> -> 3 -> -> -> 5 -> 4

和表看起来像

0 1 0 2 2 3 0 4 3 5

如何在while循环中编写mysql以输入5并获取其父级列表直到0:

How to write mysql while loop to input 5 and get list of it's parents until 0:

3 2

我知道如何用php编写它，但是我只想对数据库进行1次查询，但是当我尝试运行官方手册中的"While"示例时，它会返回很多错误.

I know, how to write it in php, but I want to do only 1 query to database, but when I try to run "While" examples from official manuals, it returns a lot of errors.

推荐答案

每个答案都不正确，但是我已经做到了. 如果有人需要，请尝试此.

Every answers are incorrect, but I've done it. If somebody will need, try this.

DELIMITER $$ DROP PROCEDURE IF EXISTS `dbName`.`getParentsTree` $$ CREATE PROCEDURE `tableName`.`getParentsTree` (IN firstChild INT, OUT tree VARCHAR(255)) BEGIN set @newChar = (select `parent_id` from tableName where category_id = firstChild); set @fullchar = ""; set @fullchar = @fullchar + firstChild; WHILE (@newChar != 0) DO SELECT CONCAT_WS(',', @fullChar, @newChar) INTO @fullChar; set @newChar = (select `parent_id` from tableName where category_id = @newChar); END WHILE; SELECT @fullchar INTO tree; END $$ DELIMITER ; CALL dbName.getParentsTree(46, @a); SELECT @a;