MySQL 8.4下如何重构无引用时自动删行的外键行为？

原脚本与核心逻辑 #

原SQL脚本如下：

drop table if exists foo;
drop table if exists bar;

create table bar (
  a character varying(10),
  c int auto_increment,
  primary key (c),
  index bar_index (a)
);

create table foo (
  a character varying(10),
  c character varying(10),
  primary key (a),
  foreign key (a) references bar (a) on delete cascade
);

insert into bar (a) values ("A");
insert into bar (a) values ("A");

insert into foo (a, c) values ("A", "xxx");

delete from bar where c=1;

select count(*) from bar;

--- 结果为1

delete from bar where c=2;

select count(*) from bar;

--- 结果为0

核心逻辑：

若foo表中不存在a = "A"的行，则删除bar表中所有a = "A"的行；当删除bar中某a的最后一行时，自动删除foo中对应a的行（级联删除）。

问题背景 #

该逻辑在MySQL < 8.4版本可正常运行，但MySQL 8.4引入了更严格的外键约束：外键必须引用父表的主键或唯一约束字段，而原脚本中bar.a仅为普通索引，因此无法创建foo表。临时开关SET restrict_fk_on_non_standard_key = OFF;已被标记为废弃，需寻找合规替代方案。

合规重构方案 #

由于原需求允许bar表存在多个相同a值的行，无法通过给bar.a添加唯一约束来适配外键规则，推荐使用触发器替代外键级联逻辑，具体实现如下：

1. 创建无外键的表结构 #

DROP TABLE IF EXISTS foo;
DROP TABLE IF EXISTS bar;

CREATE TABLE bar (
  a VARCHAR(10),
  c INT AUTO_INCREMENT,
  PRIMARY KEY (c),
  INDEX bar_index (a)
);

CREATE TABLE foo (
  a VARCHAR(10),
  c VARCHAR(10),
  PRIMARY KEY (a)
);

2. 创建触发器模拟级联逻辑 #

通过触发器实现原外键的级联删除效果：当删除bar中某a的最后一行时，自动删除foo中对应a的行。

DELIMITER //
CREATE TRIGGER after_bar_delete
AFTER DELETE ON bar
FOR EACH ROW
BEGIN
  DECLARE remaining_bar_count INT;
  -- 统计当前a值在bar表中的剩余行数
  SELECT COUNT(*) INTO remaining_bar_count FROM bar WHERE a = OLD.a;
  -- 若剩余行数为0，删除foo中对应a的行
  IF remaining_bar_count = 0 THEN
    DELETE FROM foo WHERE a = OLD.a;
  END IF;
END //
DELIMITER ;

3. 验证逻辑 #

执行原插入与删除操作，结果与原脚本一致：

insert into bar (a) values ("A");
insert into bar (a) values ("A");

insert into foo (a, c) values ("A", "xxx");

delete from bar where c=1;
select count(*) from bar; -- 结果为1

delete from bar where c=2;
select count(*) from bar; -- 结果为0
select count(*) from foo; -- 结果为0（已被触发器级联删除）

备选方案 #

若不依赖数据库层逻辑，也可在应用层实现原子操作：删除bar行后，检查该a在bar中的剩余数量，若为0则执行foo表的删除操作。需注意通过事务保证操作的原子性，避免数据不一致。

内容的提问来源于stack exchange，提问作者Paflow