MySQL递归存储过程删除记录

Question

MySQL递归存储过程删除记录

4

我有一张表格，名为Models，其中包括以下（相关的）属性：

-- -----------------------------------------------------
-- Table `someDB`.`Models`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `someDB`.`Models` (
  `model_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type_id` SMALLINT UNSIGNED NOT NULL,
  -- someOtherAttributes
  PRIMARY KEY (`model_id`),
ENGINE = InnoDB;


+---------+---------+
| model_id| type_id |
+---------+---------+
| 1       | 4       | 
| 2       | 4       | 
| 3       | 5       | 
| 4       | 3       | 
+---------+---------+

还有一个表 Model_Hierarchy，它显示了父子关系（仅显示相关属性）：

-- -----------------------------------------------------
-- Table `someDB`.`Model_Hierarchy`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `someDB`.`Model_Hierarchy` (
  `parent_id` MEDIUMINT UNSIGNED NOT NULL,
  `child_id` MEDIUMINT UNSIGNED NOT NULL,
  -- someOtherAttributes,

  INDEX `fk_Model_Hierarchy_Models1_idx` (`parent_id` ASC),
  INDEX `fk_Model_Hierarchy_Models2_idx` (`child_id` ASC),
  PRIMARY KEY (`parent_id`, `child_id`),
  CONSTRAINT `fk_Model_Hierarchy_Models1`
    FOREIGN KEY (`parent_id`)
    REFERENCES `someDB`.`Models` (`model_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Model_Hierarchy_Models2`
    FOREIGN KEY (`child_id`)
    REFERENCES `someDB`.`Models` (`model_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


+-----------+----------+
| parent_id | child_id |
+-----------+----------+
| 1         | 2        | 
| 2         | 4        | 
| 3         | 4        | 
+-----------+----------+

如果存在Model不是另一个类型为5的Model的父或子级（在某个时刻），则该模型无效，因此应将其删除。

这意味着Model 1，2应该被删除，因为在任何时候都没有将它们作为父或子级与type_id = 5的模型相关联。

此层次结构中有N个级别，但没有循环关系（即1->2; 2->1的情况不存在）。

如何实现呢？有什么想法吗？

- emihir0

你应该交换父节点和子节点的名称（在树中，具有多个子节点的节点称为父节点，而不是反过来），实际上你不需要一个单独的表。我假设你想在选择的时间点调用此清理（而不是在数据每次更改时）？如果你愿意将你的树更改为嵌套集或闭包树，则可以使用单个查询完成；对于你当前的树类型，我认为没有存储过程无法完成。我的第一个想法是添加一个标记列（或使用临时表）并在（n）次循环期间标记所有良好的节点。 - Solarflare

1

@Drew，我现在已经添加了表的创建语句。如果有任何不清楚的地方，请告诉我。 - emihir0

如果a->b表示a是b的父母，那么c->b也是什么呢？对我来说，它应该是一个具有id主键和parent_id列的自连接层次结构。无论如何，是否允许使用alter table在Model_Hierarchy上添加2个列，每个表中有多少行，最大深度是多少？ - Drew

@Drew parent_id和child_id都指向Models表中的model_id - 它们是FK（元组组合是Model_Hierarchy的PK）。type_id在任何情况下都不会显示在Model_Hierarchy中。更好地理解，type_id 5指的是“真正可销售的产品”，因此，如果一个model不包含任何可销售的产品（即包装）或者model的父级在某个时候不是可销售的产品（即一些未使用的子组件），那么我想删除它。我可以在FE中使用代码来完成这个任务，但比起在BE中完成，速度会慢得多。 - emihir0

@Drew MH 在父级和子级中都指代 M，而不是指代自己。这个想法是创建一个过程，使得“对于 Models 中的每个 model，如果该 model 不包含（或者不被）另一个 type_id 为 5 的 model 所包含，则删除它”。这种“包含/被包含”的关系可以在 Model_Hierarchy 表中找到。 - emihir0

显示剩余11条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Drew · Accepted Answer

代码中散布着注释。

架构：

CREATE TABLE `Models`
(   -- Note that for now the AUTO_INC is ripped out of this for ease of data insertion
    -- otherwise we lose control at this point (this is just a test)
    -- `model_id` MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `model_id` MEDIUMINT UNSIGNED PRIMARY KEY,
    `type_id` SMALLINT UNSIGNED NOT NULL
)ENGINE = InnoDB;

CREATE TABLE `Model_Hierarchy`
(   -- OP comments state these are more like components
    --
    -- @Drew imagine b being a product and a and c being two different ways to package it. 
    -- Hence b is contained in both a and c respectively and separately (ie. customer can buy 
    -- both a and c), however, any change (outside of the scope of this question) to b is 
    -- reflected to both a and c. `Model_Hierarchy can be altered, yes (the project is 
    -- in an early development). Max tree depth is unknown (this is for manufacturing... 
    -- so a component can consist of a component... that consist of further component etc. 
    -- no real limit). How many rows? Depends, but I don't expect it to exceed 2^32.
    --
    --
    -- Drew's interpretation of the the above: `a` is a parent of `b`, `c` is a parent of `b`
    --
    `parent_id` MEDIUMINT UNSIGNED NOT NULL,
    `child_id` MEDIUMINT UNSIGNED NOT NULL,

    INDEX `fk_Model_Hierarchy_Models1_idx` (`parent_id` ASC),
    INDEX `fk_Model_Hierarchy_Models2_idx` (`child_id` ASC),
    PRIMARY KEY (`parent_id`, `child_id`),
    key(`child_id`,`parent_id`), -- NoteA1 pair flipped the other way (see NoteA2 in stored proc)

    CONSTRAINT `fk_Model_Hierarchy_Models1`
    FOREIGN KEY (`parent_id`)
    REFERENCES `Models` (`model_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,

    CONSTRAINT `fk_Model_Hierarchy_Models2`
    FOREIGN KEY (`child_id`)
    REFERENCES `Models` (`model_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)ENGINE = InnoDB;

CREATE TABLE `GoodIds`
(   -- a table to determine what not to delete from models
    `id` int auto_increment primary key,
    `model_id` MEDIUMINT UNSIGNED,
    `has_been_processed` int not null,
    dtFinished datetime null,
    -- index section (none shown, developer chooses later, as he knows what is going on)
    unique index(model_id), -- supports the "insert ignore" concept
    -- FK's below:
    foreign key `fk_abc_123` (model_id) references Models(model_id)
)ENGINE = InnoDB;

要从头开始重新开始：

-- ------------------------------------------------------------
-- reverse order is happier
drop table `GoodIds`;
drop table `Model_Hierarchy`;
drop table `Models`;
-- ------------------------------------------------------------

加载测试数据：

insert Models(model_id,type_id) values
(1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,5),(10,1),(11,1),(12,1);
-- delete from Models; -- note, truncate does not work on parents of FK's

insert Model_Hierarchy(parent_id,child_id) values
(1,2),(1,3),(1,4),(1,5),
(2,1),(2,4),(2,7),
(3,2),
(4,8),(4,9),
(5,1),
(6,1),(6,2),
(7,1),(7,10),
(8,1),(8,12),
(9,11),
(10,11),
(11,12);

-- Set 2 to test (after a truncate / copy paste of this below to up above):
(1,2),(1,3),(1,4),(1,5),
(2,1),(2,4),(2,7),
(3,2),
(4,8),(4,9),
(5,1),
(6,1),(6,2),
(7,1),(7,10),
(8,1),(8,12),
(9,1),
(10,11),
(11,12);
-- truncate table Model_Hierarchy;
-- select * from Model_Hierarchy;
-- select * from Models where type_id=5;

存储过程：

DROP PROCEDURE if exists loadUpGoodIds;
DELIMITER $$
CREATE PROCEDURE loadUpGoodIds()
BEGIN
    DECLARE bDone BOOL DEFAULT FALSE;
    DECLARE iSillyCounter int DEFAULT 0;

    TRUNCATE TABLE GoodIds;
    insert GoodIds(model_id,has_been_processed) select model_id,0 from Models where type_id=5;    
    WHILE bDone = FALSE DO
        select min(model_id) into @the_Id_To_Process from GoodIds where has_been_processed=0;
        IF @the_Id_To_Process is null THEN
            SET bDone=TRUE;
        ELSE
            -- First, let's say this is the parent id.
            -- Find the child id's that this is a parent of
            -- and they qualify as A Good Id to save into our Good table 
            insert ignore GoodIds(model_id,has_been_processed,dtFinished) 
            select child_id,0,null
            from Model_Hierarchy
            where parent_id=@the_Id_To_Process;

            -- Next, let's say this is the child id.
            -- Find the parent id's that this is a child of
            -- and they qualify as A Good Id to save into our Good table 
            insert ignore GoodIds(model_id,has_been_processed,dtFinished) 
            select child_id,0,null
            from Model_Hierarchy
            where child_id=@the_Id_To_Process;

            -- NoteA2: see NoteA1 in schema
            -- you can feel the need for the flipped pair composite key in the above

            UPDATE GoodIds set has_been_processed=1,dtFinished=now() where model_id=@the_Id_To_Process;
        END IF;

        -- safety bailout during development:
        SET iSillyCounter = iSillyCounter + 1;
        IF iSillyCounter>10000 THEN
            SET bDone=TRUE;
        END IF;

    END WHILE;
END$$
DELIMITER ;

测试：

call loadUpGoodIds();

-- select count(*) from GoodIds; -- 9 / 11 / 12
select * from GoodIds limit 10;
+----+----------+--------------------+---------------------+
| id | model_id | has_been_processed | dtFinished          |
+----+----------+--------------------+---------------------+
|  1 |        9 |                  1 | 2016-06-28 20:33:16 |
|  2 |       11 |                  1 | 2016-06-28 20:33:16 |
|  4 |       12 |                  1 | 2016-06-28 20:33:16 |
+----+----------+--------------------+---------------------+

清理调用可以折叠到存储过程中：

-- The below is what to run
-- delete from Models where model_id not in (select null); -- this is a safe call (will never do anything)
-- the above is just a null test

delete from Models where model_id not in (select model_id from GoodIds);
-- Error 1451: Cannot delete or update a parent row: a FK constraint is unhappy
-- hey the cascades did not work, can figure that out later
-- Let go bottom up for now. Meaning, to honor FK constraints, kill bottom up.
delete from Model_Hierarchy where parent_id not in (select model_id from GoodIds);
-- 18 rows deleted
delete from Model_Hierarchy where child_id not in (select model_id from GoodIds);
-- 0 rows deleted
delete from Models where model_id not in (select model_id from GoodIds);
-- 9 rows deleted / 3 remain
select * from Models;
+----------+---------+
| model_id | type_id |
+----------+---------+
|        9 |       5 |
|       11 |       1 |
|       12 |       1 |
+----------+---------+