mysql - 计算mysql中的第n级子元素

Question

我无法计算每个根类别中的第 n 级子元素我的类别 View 如

id      parent_id   name
1            0      Fashion
2            0      Beauty   
3            0      Travel   
4            0      Food   
1006         1      Clothes   
1009         1      Shoes   
1011         1      Bags
1088         1009   Casual
1089         1009   Sport
2033         1088   Sneaker
2034         1088   Simple
----         ----   ------
----         ----   ------

我想获取每个根元素从上到下的分层类别id

like fashion->Clothes,Shoes,Bags->Casual,Sport->Sneaker,Simple 1,1006,1009,1011,1088,1089,2033,2034

我在 mysql 中使用函数，但是当我获取结果时它花费了无限的时间

CREATE DEFINER=`root`@`localhost` FUNCTION `GetAllNode`(GivenID INT) RETURNS text CHARSET latin1
    DETERMINISTIC
BEGIN
    DECLARE rv,q,queue,queue_children TEXT;
    DECLARE queue_length,front_id,pos INT;
    SET rv = '';
    SET queue = GivenID;
    SET queue_length = 1;
    WHILE queue_length > 0 DO
        SET front_id = FORMAT(queue,0);
        IF queue_length = 1 THEN
            SET queue = '';
        ELSE
            SET pos = LOCATE(',',queue) + 1;
            SET q = SUBSTR(queue,pos);
            SET queue = q;
        END IF;
        SET queue_length = queue_length - 1;
        SELECT IFNULL(qc,'') INTO queue_children
        FROM (SELECT GROUP_CONCAT(CAST(id AS CHAR(50))) AS qc
        FROM `categories` WHERE `parent_id` = front_id) A ;
        IF LENGTH(queue_children) = 0 THEN
            IF LENGTH(queue) = 0 THEN
                SET queue_length = 0;
            END IF;
        ELSE
            IF LENGTH(rv) = 0 THEN
                SET rv = queue_children;
            ELSE
                SET rv = CONCAT(rv,',',queue_children);
            END IF;
            IF LENGTH(queue) = 0 THEN
                SET queue = queue_children;
            ELSE
                SET queue = CONCAT(queue,',',queue_children);
            END IF;
            SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
        END IF;
    END WHILE;
    RETURN rv;
END

Query to fetch result from function GetAllNode()
SELECT id ,GetAllNode(id) FROM categories WHERE id=1

下面的表结构和数据

     CREATE TABLE `categories` (
              `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
              `parent_id` int(10) NOT NULL DEFAULT '0',
              `name` varchar(255) NOT NULL,
              `color` varchar(10) DEFAULT '#efefef',
              `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
              `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=1084 DEFAULT CHARSET=latin1

   INSERT INTO `categories` (`id`, `parent_id`, `name`, `color`, `created_at`, `updated_at`) VALUES
(1, 0, 'Fashion', '#60c6ce', '2015-10-21 13:47:54', '2015-10-21 16:47:54'),
(2, 0, 'Beauty', '#f29cb2', '2015-11-17 12:36:03', '2015-10-15 20:54:31'),
(3, 0, 'Travel', '#26a8e0', '2015-10-21 14:00:52', '2015-10-21 17:00:52'),
(4, 0, 'Food', '#f9ba3d', '2015-08-11 16:04:38', '2015-08-11 16:04:38'),
(5, 0, 'Wellness', '#86c87b', '2015-10-21 14:38:33', '2015-10-21 17:38:33'),
(6, 0, 'Inspiration', '#97249a', '2015-11-17 12:36:07', '2015-10-15 14:39:47'),
(7, 0, 'Culture', '#ff6c18', '2015-08-11 16:06:13', '2015-08-11 16:06:13'),
(1003, 0, 'Living', '#8e9dcd', '2015-10-21 14:34:21', '2015-10-21 17:34:21'),
(1006, 1, 'Clothes', '#60c6ce', '2015-10-21 13:51:15', '2015-10-21 16:51:15'),
(1009, 1, 'Shoes', '#60c6ce', '2015-10-21 13:52:38', '2015-10-21 16:52:38'),
(1011, 1, 'Bags', '#60c6ce', '2015-10-21 13:52:02', '2015-10-21 16:52:02'),
(1012, 1, 'Accessories', '#60c6ce', '2015-10-21 13:50:56', '2015-10-21 16:50:56'),
(1013, 1, 'Designers', '#60c6ce', '2015-10-21 13:52:21', '2015-10-21 16:52:21'),
(1014, 2, 'Make Up', '#f29cb2', '2015-10-15 14:50:15', '2015-10-15 14:50:15'),
(1015, 2, 'Skin', '#f29cb2', '2015-10-15 14:50:41', '2015-10-15 14:50:41'),
(1019, 0, 'Beauty', '#f29bb2', '2015-10-21 13:55:44', '2015-10-21 16:55:44'),
(1020, 1019, 'Make Up', '#f29bb2', '2015-10-21 16:56:23', '2015-10-21 16:56:23'),
(1021, 1019, 'Skin Care', '#f29bb2', '2015-10-21 14:15:35', '2015-10-21 17:15:35'),
(1022, 1019, 'Hair', '#f29bb2', '2015-10-21 16:58:40', '2015-10-21 16:58:40'),
(1023, 1019, 'Nails', '#f29bb2', '2015-10-21 16:59:03', '2015-10-21 16:59:03'),
(1024, 1019, 'Fragrance', '#f29bb2', '2015-10-21 16:59:40', '2015-10-21 16:59:40'),
(1025, 3, 'Things To Do', '#26a8e0', '2015-10-21 17:01:26', '2015-10-21 17:01:26'),
(1026, 3, 'Hotels', '#26a8e0', '2015-10-21 17:01:54', '2015-10-21 17:01:54'),
(1027, 3, 'Restaurants', '#26a8e0', '2015-10-21 17:02:21', '2015-10-21 17:02:21'),
(1028, 3, 'City Travel Guides', '#26a8e0', '2015-10-21 17:03:02', '2015-10-21 17:03:02'),
(1029, 3, 'Local - New York', '#26a8e0', '2015-10-21 17:27:39', '2015-10-21 17:27:39'),
(1030, 3, 'Local - London', '#26a8e0', '2015-10-21 17:28:00', '2015-10-21 17:28:00'),
(1031, 3, 'Local - Paris', '#26a8e0', '2015-10-21 14:28:38', '2015-10-21 17:28:38'),
(1032, 7, 'Movies', '#ff6c18', '2015-10-21 17:29:15', '2015-10-21 17:29:15'),
(1033, 7, 'Music', '#ff6c18', '2015-10-21 17:30:05', '2015-10-21 17:30:05'),
(1034, 7, 'TV', '#ff6c18', '2015-10-21 17:30:53', '2015-10-21 17:30:53'),
(1035, 7, 'Books', '#ff6c18', '2015-10-21 17:31:09', '2015-10-21 17:31:09'),
(1036, 7, 'Theatre', '#ff6c18', '2015-10-21 17:31:35', '2015-10-21 17:31:35'),
(1037, 7, 'Shows', '#ff6c18', '2015-10-21 17:32:11', '2015-10-21 17:32:11'),
(1038, 7, 'Art', '#ff6c18', '2015-10-21 17:32:28', '2015-10-21 17:32:28'),
(1039, 7, 'Events', '#ff6c18', '2015-10-21 17:32:44', '2015-10-21 17:32:44'),
(1040, 1, 'Celebrities', '#60c6ce', '2015-10-21 17:33:33', '2015-10-21 17:33:33'),
(1041, 1003, 'Home', '#8e9dcd', '2015-10-21 17:34:40', '2015-10-21 17:34:40'),
(1042, 1003, 'Decor', '#8e9dcd', '2015-10-21 17:35:28', '2015-10-21 17:35:28'),
(1043, 1003, 'Entertaining', '#8e9dcd', '2015-10-21 17:36:06', '2015-10-21 17:36:06');

Answer 1

你的功能有问题。您正在使用SET front_id = FORMAT(queue,0);这实际上在语言环境中格式化您的队列，我想它会做类似的事情:FORMAT('1000',0) 输出=> 1,000那破坏了你的功能。只需将行 SET front_id = FORMAT(queue,0); 更改为 SET front_id = queue;

它应该可以工作!!