Mysql过程模拟DELETE ON CASCADE

Question

我需要写一个mysql过程来删除s_conf，这是通过s_conf_cpsc其他s_conf的父，每个s_conf_at上都有子行。每个s_conf都有一个id_a，它是一个字符串。这个过程应该检查s_conf是否是唯一的子节点，然后删除，否则对它不做任何操作，然后继续处理另一个。它应该删除s_conf, s_conf_cpsc和s_conf_at上的所有相关行(除非满足先前的条件)。

到目前为止我有这个

CREATE PROCEDURE delete_s_conf_recursive(IN ida VARCHAR(255))
BEGIN
DECLARE _id INT;
DECLARE _id_a VARCHAR(255);
DECLARE done INT DEFAULT FALSE;

DECLARE cur CURSOR FOR 
SELECT ss.id_a, ss.id FROM s_conf AS s 
LEFT JOIN s_conf_cpsc AS scc ON scc.id_conf = s.id AND s.id_a =  ida 
LEFT JOIN s_conf AS ss ON scc.id_conf_h = ss.id
WHERE ss.id_a IS NOT NULL;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
read_loop: LOOP
FETCH cur INTO _id_a, _id;
IF done THEN
LEAVE read_loop;
END IF;

CALL delete_s_conf_recursive(_id_a);

END LOOP;
CLOSE cur;

SET @q = (SELECT COUNT(*) FROM s_conf_cpsc LEFT JOIN s_conf ON s_conf_cpsc.id_conf_h = s_conf.id  AND s_conf.id_a = ida WHERE s_conf.id_a = ida);

IF (@q < 2)  THEN 

DELETE  s_conf_tipo_at FROM s_conf_tipo_at
LEFT JOIN s_conf ON s_conf_tipo_at.id_conf = s_conf.id  AND s_conf.id_a = ida
WHERE s_conf.id_a = ida;
DELETE  s_conf_cpsc FROM s_conf_cpsc  
LEFT JOIN s_conf ON s_conf_cpsc.id_conf = s_conf.id  AND s_conf.id_a = ida
WHERE s_conf.id_a = ida;
DELETE FROM s_conf
WHERE s_conf.id_a = ida;

END IF;

END $$
DELIMITER ;

但是在试图删除之前，它没有检查s_conf是否是唯一子节点。这些表是ON DELETE RESTRICT的，不应该被修改。

示例:我想删除s_conf 1，它有子节点2和3。但3也是4的子元素。所以这个过程应该删除s_conf_cpsc 1-2和1-3，但只删除2的s_conf_at，因为3仍然是4的子结点。

Mysql版本为5.6

Answer 1

你能试一下吗?

CREATE PROCEDURE delete_s_conf_recursive(IN ida VARCHAR(255))
BEGIN
DECLARE _id INT;
DECLARE _id_a VARCHAR(255);
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR 
SELECT ss.id_a, ss.id FROM s_conf AS s 
LEFT JOIN s_conf_cpsc AS scc ON scc.id_conf = s.id AND s.id_a = ida 
LEFT JOIN s_conf AS ss ON scc.id_conf_h = ss.id
WHERE ss.id_a IS NOT NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO _id_a, _id;
IF done THEN
LEAVE read_loop;
END IF;
CALL delete_s_conf_recursive(_id_a);
END LOOP;
CLOSE cur;
SET @q = (SELECT COUNT(*) FROM s_conf_cpsc WHERE id_conf_h = (SELECT id FROM s_conf WHERE id_a = ida));
IF (@q = 0) THEN 
DELETE FROM s_conf_at WHERE id_a = ida;
DELETE FROM s_conf_cpsc WHERE id_conf = (SELECT id FROM s_conf WHERE id_a = ida);
DELETE FROM s_conf WHERE id_a = ida;
END IF;
END $$
DELIMITER ;

主要区别在于它直接使用子查询检查s_conf_cpsc表中的子行数。它通过计算s_conf_cpsc中id_conf_h列与s_conf表中的行id与输入ida匹配的行数来实现这一点。如果计数为0，表示s_conf行没有子表，则继续删除s_conf_at、s_conf_cpsc和s_conf表中的相关行。

Answer 2

我发现一个更简单的方法是将函数分为两个部分，一个将递归地处理从父级和要删除的子级接收id，以便检查作为父级传递的id是否是唯一的，如果不删除该关系而不触及s_conf_at，其他函数将调用第一个，但只会处理传递的第一个id。

结果如下

DROP PROCEDURE IF EXISTS delete_s_conf_recursive_ch;
DELIMITER $$
CREATE PROCEDURE delete_s_conf_recursive(IN ida VARCHAR(255))
BEGIN
DECLARE _id INT;
DECLARE _id_a VARCHAR(255);
DECLARE done INT DEFAULT FALSE;

DECLARE cur CURSOR FOR 
SELECT ss.id_a, ss.id FROM s_conf AS s 
LEFT JOIN s_conf_cpsc AS scc ON scc.id_conf = s.id AND s.id_a =  ida 
LEFT JOIN s_conf AS ss ON scc.id_conf_h = ss.id
WHERE ss.id_a IS NOT NULL;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO _id_a, _id;
IF done THEN
LEAVE read_loop;
END IF;

CALL delete_s_conf_recursive_ch(_id_a, ida);

END LOOP;
CLOSE cur;

DELETE  s_conf_at FROM s_conf_at
LEFT JOIN s_conf ON s_conf_at.id_conf = s_conf.id  AND s_conf.id_a = ida
WHERE s_conf.id_a = ida;

DELETE FROM s_conf
WHERE s_conf.id_a = ida;

END $$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE eliminar_s_conf_hijo(IN ida VARCHAR(255), ida_p VARCHAR(255))
BEGIN
DECLARE _id INT;
DECLARE _id_a VARCHAR(255);
DECLARE done INT DEFAULT FALSE;
DECLARE q INT;

DECLARE cur CURSOR FOR 
SELECT ss.id_a, ss.id FROM s_conf AS s 
LEFT JOIN s_conf_cpsc AS scc ON scc.id_conf = s.id AND s.id_a =  ida 
LEFT JOIN s_conf AS ss ON scc.id_conf_h = ss.id
WHERE ss.id_a IS NOT NULL;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO _id_a, _id;
IF done THEN
LEAVE read_loop;
END IF;

CALL eliminar_s_conf_hijo(_id_a, ida);

END LOOP;
CLOSE cur;

SELECT COUNT(*) INTO q FROM s_conf_cpsc WHERE s_conf_cpsc.id_conf_h IN (SELECT id FROM s_conf WHERE s_conf.id_a = ida);
DELETE FROM s_conf_cpsc
WHERE s_conf_cpsc.id_conf IN (SELECT id FROM s_conf WHERE s_conf.id_a = ida_p) 
AND s_conf_cpsc.id_conf_h IN (SELECT id FROM s_conf WHERE s_conf.id_a = ida);

IF q < 2  THEN 

DELETE  s_conf_at FROM s_conf_at
LEFT JOIN s_conf ON s_conf_at.id_conf = s_conf.id  AND s_conf.id_a = ida
WHERE s_conf.id_a = ida;

DELETE FROM s_conf
WHERE s_conf.id_a = ida;

END IF;

END $$
DELIMITER ;