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

jm81lzqq  于 2021-06-17  发布在  Mysql
关注(0)|答案(1)|浏览(318)

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

  1. "prod"."parent"
  2. +----+---------+--------+
  3. | SP_ID | SP_NAME | DELETE_FLG |
  4. +----+---------+--------+
  5. | 1 | 1 | 1 |
  6. +----+---------+--------+
  7. "prod"."parent"
  8. +----+---------+--------+
  9. | SP_ID | SP_NAME | DELETE_FLG |
  10. +----+---------+--------+
  11. | 1 | 1 | 1 |
  12. +----+---------+--------+
  13. "stg"."parent"
  14. +----+---------+--------+
  15. | SP_ID | SP_NAME | DELETE_FLG |
  16. +----+---------+--------+
  17. | 1 | 1 | 0 |
  18. +----+---------+--------+
  19. "prod"."child"
  20. +----+---------+
  21. | SP_ID | JOB_KEY |
  22. +----+---------+
  23. | 1 | key |
  24. +----+---------+

,如果我执行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中可以实现吗?或者我必须在应用层这么做?
表定义

  1. CREATE TABLE `prod`.`parent` (
  2. `SP_ID` varchar(20) NOT NULL COMMENT '',
  3. `SP_NAME` varchar(100) NOT NULL COMMENT '',
  4. `DELETE_FLG` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
  5. PRIMARY KEY (`SP_ID`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''
  7. CREATE TABLE `prod`.`child` (
  8. `SP_ID` varchar(20) NOT NULL COMMENT '',
  9. `JOB_KEY` varchar(11) NOT NULL,
  10. PRIMARY KEY (`SP_ID`,`JOB_KEY`)
  11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''
  12. CREATE TABLE `stg`.`parent` (
  13. `SP_ID` varchar(20) NOT NULL COMMENT '',
  14. `SP_NAME` varchar(100) NOT NULL COMMENT '',
  15. `DELETE_FLG` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
  16. PRIMARY KEY (`SP_ID`)
  17. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''
zy1mlcev

zy1mlcev1#

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

  1. # after update trigger
  2. CREATE DEFINER=`root`@`localhost` TRIGGER `stg`.`parent_AFTER_UPDATE` AFTER UPDATE ON `parent` FOR EACH ROW
  3. BEGIN
  4. IF (
  5. select
  6. count(*)
  7. from(
  8. select
  9. *
  10. from `prod`.`parent`
  11. where `prod`.`parent`.id = old.id and `prod`.`parent`.delete_flg = 0
  12. Union all
  13. select
  14. *
  15. from `stg`.`parent`
  16. where `stg`.`parent`.id = old.id and `stg`.`parent`.delete_flg = 0
  17. ) as a
  18. ) = 0 THEN
  19. DELETE FROM `prod`.`child` WHERE `prod`.`child`.id = old.id;
  20. END IF;
  21. END
  22. # after delete trigger
  23. CREATE DEFINER=`root`@`localhost` TRIGGER `stg`.`parent_AFTER_DELETE` AFTER DELETE ON `parent` FOR EACH ROW
  24. BEGIN
  25. IF (
  26. select
  27. count(*)
  28. from(
  29. select
  30. *
  31. from `prod`.`parent`
  32. where `prod`.`parent`.id = old.id and `prod`.`parent`.delete_flg = 0
  33. Union all
  34. select
  35. *
  36. from `stg`.`parent`
  37. where `stg`.`parent`.id = old.id and `stg`.`parent`.delete_flg = 0
  38. ) as a
  39. ) = 0 THEN
  40. DELETE FROM `prod`.`child` WHERE `prod`.`child`.id = old.id;
  41. END IF;
  42. END
展开查看全部

相关问题