mysql表中的父树(while循环) [英] Parents tree in mysql table (while-loop)

问题描述

对不起，我的英语. 我有一个像这样的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;

这篇关于mysql表中的父树(while循环)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！