代码之家 › 专栏 › 技术社区 › zaozaoer

多模式mysql更新时的软删除

cascading-deletes cascade foreign-keys mysql

zaozaoer · 技术社区 · 7 年前

我现在的处境是:

背景

我有两个数据库模式称为“prod”和“stg”。
“prod”包含两个名为“parent”和“child”的表
“stg”只有“parent”表
“parent”表定义在“prod”和“stg”模式中是相同的。
在删除记录的情况下,“父”表定义为软删除(逻辑删除,即设置delete_flg为“1”),而“子”表定义为真删除(物理删除记录)

目标

我正在努力实现以下目标: 当且仅当“prod”、“parent”和“stg”、“parent”都被删除时(无论是物理上还是逻辑上,或在一侧不存在),然后自动将删除操作(物理上删除)级联到“prod”、“child”表中“sp_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”将delete_flg=1设置为sp_id=1,这将在逻辑上删除“parent”表中sp_id为1的最后一个“existing”记录,那么“prod”中的记录“.child”也将被mysql自动物理删除。

问题

我一直在考虑让子表中的sp_id作为外键引用父表中的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=''

1 回复 | 直到 7 年前

zaozaoer 7 年前

有了使用触发器的提示,这是我的解决方案,它可以工作: 向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