存储过程超时

Question

试图收集一些传家宝数据发送给第三方，并被引导到这个职位。

在尝试将其调整到SQL Fiddle上的用例之后，存储过程一直在超时。

所以我在本地试用了两次(通过PhpMyAdmin)。

当我试图在调用存储过程后在浏览器中重新加载PMA时，我只会得到一个永恒的“等待响应”旋转器(超过10或20分钟)。

我假设我的SP代码有问题？

CREATE TABLE foo
    (`id` int, `name` varchar(100), `parentId` int, `path` varchar(100))
//

INSERT INTO foo (`id`, `name`, `parentId`, `path`)
VALUES (1, 'discrete', 0, NULL),
       (2, 'res', 1, NULL),
       (3, 'smt', 2, NULL),
       (4, 'cap', 1, NULL),
       (5, 'ind', 1, NULL),
       (6, 'smt', 4, NULL),
       (7, 'tant', 6, NULL),
       (8, 'cer', 6, NULL)
//

CREATE PROCEDURE updatePath()
BEGIN
DECLARE cnt, n int;
    SELECT COUNT(*) INTO n FROM foo WHERE parentId = 0;
    UPDATE foo a, foo b SET a.path = b.name WHERE b.parentId IS NULL AND a.parentId = b.id;
    SELECT count(*) INTO cnt FROM foo WHERE path IS NULL;
    while cnt > n do
        UPDATE foo a, foo b SET a.path = concat(b.path, '|', b.id) WHERE b.path IS NOT NULL AND a.parentId = b.id;
        SELECT count(*) INTO  cnt FROM foo WHERE path IS NULL;
    end while;
END//

编辑

预期成果：

VALUES (1, 'discrete', 0, '1'),
       (2, 'res', 1, '1|2'),
       (3, 'smt', 2, '1|2|3'),
       (4, 'cap', 1, '1|4'),
       (5, 'ind', 1, '1|5'),
       (6, 'smt', 4, '1|4|6'),
       (7, 'tant', 6, '1|4|6|7'),
       (8, 'cer', 6, '1|4|6|8');

Answer 1

睡了一个好觉后，我接受了@Drew的领导，一次只看了一台电脑。

开始起作用了。我要把它放在这里：

CREATE TABLE foo
    (`id` int, `name` varchar(100), `parentId` int, `path` varchar(100))
//

INSERT INTO foo
    (`id`, `name`, `parentId`, `path`)
VALUES
    (1, 'dscr', 0, NULL),
    (2, 'res', 1, NULL),
    (3, 'smt', 2, NULL),
    (4, 'cap', 1, NULL),
    (5, 'ind', 1, NULL),
    (6, 'chp', 4, NULL),
    (7, 'tant', 6, NULL),
    (8, 'cer', 6, NULL)
//

CREATE PROCEDURE updatePath()
BEGIN
DECLARE cnt, n int;

    SELECT COUNT(*) INTO n FROM foo WHERE parentId = 0; -- n is now 1
    SELECT COUNT(*) INTO cnt FROM foo WHERE path IS NULL; -- cnt is now 8

    UPDATE foo child, foo parent        -- each child now has its parent and own ID's in the path
      SET child.path = CONCAT(parent.id, '|', child.id)
      WHERE parent.parentId = 0
      AND child.parentId = parent.id;

    WHILE cnt > n DO
        UPDATE foo child, foo parent    -- concat parent's path and own ID into each empty child's path
          SET child.path = concat( parent.path,'|',child.id ) 
          WHERE parent.path IS NOT NULL 
          AND child.parentId = parent.id;

        SELECT COUNT(*) INTO  cnt  -- decrement cnt
          FROM foo 
          WHERE path IS NULL;

    END WHILE;

    UPDATE foo  -- set path for any top-level categories
      SET path = id
      WHERE path IS NULL;

END//

call updatePath()//

你可以随意批评。

希望这能帮到别人一些时间。

Answer 2

您是否试图执行自引用连接来创建层次结构？

从foo a选择a.name，parentName = b.name，外部连接foo b on ( a.id = b.parentId )