使用多个模式在mysql中更新时软删除

我的处境是：
背景
我有两个数据库模式称为“prod”和“stg”。
“prod”包含两个名为“parent”和“child”的表
“stg”只有“parent”表
“父”表定义在“prod”和“stg”模式中是相同的。
在删除记录的情况下，“父”表定义为软删除（逻辑删除，即将delete\u flg设置为“1”），而“子”表定义为真删除（物理删除记录）
目标
我试图实现以下目标：当且仅当“prod”、“parent”和“stg”、“parent”都被删除时（无论在物理上还是逻辑上，或者一方不存在），然后自动级联一个删除操作（物理删除）到“prod”、“child”表中的记录，其“sp\u id”与“parent”中的值匹配。
例如，假设我有

"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”。“parent”set delete\u flg=1，其中sp\u id=1，逻辑上删除“parent”表中sp\u id为1的最后一条“现有”记录，那么“prod”。“child”中的记录也会被mysql自动物理删除。
问题
我一直在考虑将子表中的sp\u id作为外键引用父表中的sp\u 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=''

在使用触发器的提示下，我的解决方案是这样的：向prod和stg父表添加2个触发器（一个是update之后，on是delete之后）。


# 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

使用多个模式在mysql中更新时软删除

1条答案

相关问题

热门标签

最新问答