mysql - 使用mysql更新具有父子关系的同一个表的列名

Question

我搜索了很多做任务但没有找到合适的解决方案。

基本上是这样的。我有一个 user_comment 表，其中有 5 列(id、parent_id、user_comments、is_deleted、modified_datetime)。有一个父子关系，如 1->2,1->3,2->4,2->5,5->7 等。
现在我从前端发送 id，我想要将 is_deleted 列更新为 1 并将所有记录的 modified_datetime 更新为这个 id 以及所有的 child 和 child 的 child 。

我正在尝试使用递归过程来完成此操作。下面是我的程序代码

     CREATE DEFINER=`root`@`localhost` PROCEDURE `user_comments`(
     IN mode varchar(45),
     IN comment_id int,
     )
     BEGIN

     DECLARE p_id INT DEFAULT NULL ;

     if(mode = 'delete')
     then
     update user_comment set is_deleted = 1, modified_datetime = now() 
      where id = comment_id ;

     select id from user_comment where parent_id = comment_id into p_id ;

     if p_id is not null
     then 
     SET @@GLOBAL.max_sp_recursion_depth = 255;
     SET @@session.max_sp_recursion_depth = 255; 
    call user_comments('delete', p_id);
    end if;
    end if;
    END

通过使用这个过程，它给我一个多行的错误。如果我返回选择查询而不将其提供给变量，则会显示选择查询的适当结果，但我必须根据获取选择查询的 ID 递归调用此过程。

我需要帮助，我已经过了 2 天了。

我也用过游标。下面是游标的代码

   CREATE DEFINER=`root`@`localhost` PROCEDURE `user_comments`(
   IN mode varchar(45),
   IN comment_id int,
   )
   BEGIN
   DECLARE p_emp int;
   DECLARE noMoreRow INT;
   DECLARE cur_emp CURSOR FOR  select id from user_comment where parent_id = comment_id ;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMoreRow = 0;


   if(mode = 'delete')
   then



   OPEN cur_emp;
     LOOPROWS: LOOP

      IF noMoreRow = 0 THEN
      update user_comment set is_deleted = 1, modified_datetime = now() where id = comment_id 
        CLOSE cur_emp;
        LEAVE LOOPROWS;
    END IF;
    FETCH cur_emp INTO p_emp;
    update user_comment set is_deleted = 1, modified_datetime = now() where id = p_emp ;
    SET @@GLOBAL.max_sp_recursion_depth = 255;
    SET @@session.max_sp_recursion_depth = 255; 
    call user_comments('delete', p_emp);

    END LOOP;
   end if;
 END

在使用游标后我得到一个线程错误。我不知道如何解决这个问题!!!

Answer 1

关于 select ... into varlist 的 Mysql 文档明确地说:

The selected values are assigned to the variables. The number of variables must match the number of columns. The query should return a single row. If the query returns no rows, a warning with error code 1329 occurs (No data), and the variable values remain unchanged. If the query returns multiple rows, error 1172 occurs (Result consisted of more than one row). If it is possible that the statement may retrieve multiple rows, you can use LIMIT 1 to limit the result set to a single row.

由于您在 OP 中写道一条评论可以是许多评论的父评论，因此使用简单变量不是解决方案。你应该使用 CURSOR相反，它可以存储整个结果集。

如上述链接中的示例代码所示，您可以遍历 cursos 中的记录，并以递归方式调用 user_comments()。

更新

如果你收到

Error Code: 1436. Thread stack overrun

错误，那么你可以做两件事:

1. 增加配置文件中的thread_stack设置并重启mysql服务器。

2. 您可以尝试简化代码以使用更少的递归，从而减少堆栈空间。例如，当您将所有子项提取到游标中，而不是为每个子项递归调用 user_comments() 时，您可以在代码中设置所有直接子项的状态并仅对孙子项递归调用该函数(如果有的话)。您还可以更改数据结构并使用 nested set模型来处理层次结构。

嵌套集模型理解起来更复杂，遍历的资源消耗更少，但维护的资源消耗更多。