多模式mysql更新中的软删除

Question

我现在的处境是：

背景

1. 我有两个名为"prod“和"stg”的数据库模式。
2. "prod“包含两个名为”父“和”子“的表。
3. "stg“只有”父“表。
4. “父”表定义在"prod“和"stg”模式之间是相同的。
5. 在删除记录的情况下，“父”表被定义为软删除(逻辑上是删除，即将delete_flg设置为"1")，而“子”表是真正的删除(物理上删除记录)。

目标

我正试图实现以下目标:当“prod”、“父”和“stg”都被删除时(无论物理上还是逻辑上，或者一方不存在)，然后自动将删除操作(物理删除)级联到“prod”中的记录。"SP_ID“与”父“中的值匹配。

例如，假设我有

"prod"."parent"
+----+---------+--------+
| SP_ID | SP_NAME | DELETE_FLG |
+----+---------+--------+
|  1 |       1 |      1 |
+----+---------+--------+



"prod"."parent"
+----+---------+--------+
| SP_ID | SP_NAME | DELETE_FLG |
+----+---------+--------+
|  1 |       1 |      1 |
+----+---------+--------+

"stg"."parent"
+----+---------+--------+
| SP_ID | SP_NAME | DELETE_FLG |
+----+---------+--------+
|  1 |       1 |      0 |
+----+---------+--------+


"prod"."child"
+----+---------+
| SP_ID | JOB_KEY |
+----+---------+
|  1 |       key |
+----+---------+

，如果我执行sql“stg”.“父”设置为DELETE_FLG = 1，其中SP_ID = 1，逻辑上删除了具有SP_ID 1的“父”表中的最后一个“现有”记录，则“prod”中的记录也将由mysql自动删除。

问题

我一直在考虑将子表中的SP_ID作为引用父级可选(https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html)中的表的外键，但是，( a)我不知道是否可以在不同的网络模式中引用多个表；( b)看来mysql只支持级联相同的操作，即删除父表，然后删除父表或父表的更新，然后更新子表。但是在我的例子中，我想要一个关于父母的更新，然后删除孩子。

有人能帮我一下吗？在mysql中可以实现这一点吗？还是我必须在应用层做这个？

表定义

CREATE TABLE `prod`.`parent` (
  `SP_ID` varchar(20) NOT NULL COMMENT '',
  `SP_NAME` varchar(100) NOT NULL COMMENT '',
  `DELETE_FLG` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
  PRIMARY KEY (`SP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''


CREATE TABLE `prod`.`child` (
  `SP_ID` varchar(20) NOT NULL COMMENT '',
  `JOB_KEY` varchar(11) NOT NULL,
  PRIMARY KEY (`SP_ID`,`JOB_KEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''



CREATE TABLE `stg`.`parent` (
  `SP_ID` varchar(20) NOT NULL COMMENT '',
  `SP_NAME` varchar(100) NOT NULL COMMENT '',
  `DELETE_FLG` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
  PRIMARY KEY (`SP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''

Answer 1

有了使用触发器的提示，这就是我的解决方案:向prod和stg父表添加2个触发器(一个在更新后，on在删除后)。

​

# after update trigger
CREATE DEFINER=`root`@`localhost` TRIGGER `stg`.`parent_AFTER_UPDATE` AFTER UPDATE ON `parent` FOR EACH ROW
BEGIN
IF (
select
count(*)
from(
select 
*
from `prod`.`parent`
where `prod`.`parent`.id = old.id and `prod`.`parent`.delete_flg = 0
Union all
select 
*
from `stg`.`parent`
where `stg`.`parent`.id = old.id and `stg`.`parent`.delete_flg = 0
) as a
) = 0 THEN 
DELETE FROM `prod`.`child` WHERE `prod`.`child`.id = old.id;
END IF;
END



# after delete trigger
CREATE DEFINER=`root`@`localhost` TRIGGER `stg`.`parent_AFTER_DELETE` AFTER DELETE ON `parent` FOR EACH ROW
BEGIN
IF (
select
count(*)
from(
select 
*
from `prod`.`parent`
where `prod`.`parent`.id = old.id and `prod`.`parent`.delete_flg = 0
Union all
select 
*
from `stg`.`parent`
where `stg`.`parent`.id = old.id and `stg`.`parent`.delete_flg = 0
) as a
) = 0 THEN 
DELETE FROM `prod`.`child` WHERE `prod`.`child`.id = old.id;
END IF;
END

​