mysql修改所有子节点状态包括自己

最新推荐文章于 2023-12-26 17:19:46 发布

月慕向阳

最新推荐文章于 2023-12-26 17:19:46 发布

阅读量448

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/weixin_41796956/article/details/100140558

版权

mysql 专栏收录该内容

16 篇文章 3 订阅

订阅专栏

DROP TABLE IF EXISTS `city`;

CREATE TABLE `city` (
  `i_id` int(11) NOT NULL AUTO_INCREMENT,
  `p_id` int(11) DEFAULT NULL,
  `c_name` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `status` char(1) COLLATE utf8_bin DEFAULT '0',
  PRIMARY KEY (`i_id`)
) ENGINE=InnoDB AUTO_INCREMENT=247 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

/*Data for the table `city` */

insert  into `city`(`i_id`,`p_id`,`c_name`,`status`) values 

(1,0,'北京','0'),

(2,1,'朝阳区','0'),

(3,1,'海淀区','0'),

(4,1,'石景山区','0'),

(5,4,'永乐小区','0'),

(6,4,'N号楼','0'),

(7,5,'朝阳公园','0'),

(8,2,'幸福溪里','0'),

(9,2,'国贸中心','0');

存储过程


/* Procedure structure for procedure `changeStatus` */

/*!50003 DROP PROCEDURE IF EXISTS  `changeStatus` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `changeStatus`(in param_i_id int,in flag int)
BEGIN
  DECLARE strAllSubIds VARCHAR (100);
  DECLARE strTempPid VARCHAR (100);
  SELECT
    GROUP_CONCAT(i_id) INTO strAllSubIds
  FROM
    city
  WHERE p_id = param_i_id;
  SET strTempPid = strAllSubIds;
  WHILE
    strTempPid IS NOT NULL DO
    SELECT
      GROUP_CONCAT(i_id) INTO strTempPid
    FROM
      city
    WHERE FIND_IN_SET(p_id, strTempPid) > 0;
    IF (strTempPid IS NOT NULL)
    THEN SET strAllSubIds = CONCAT(strAllSubIds, ',', strTempPid);
    END IF;
  END WHILE;
  
  set strAllSubIds=CONCAT(strAllSubIds, ',', param_i_id);
  update city set status=flag where FIND_IN_SET(i_id,strAllSubIds);
  
  
END */$$
DELIMITER ;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;