如何在MySQL中创建触发器实现插入比赛记录时自动处理球队FK关联

需求目标 #

插入球队名称时避免重复记录，统一使用球队ID录入比赛及比分数据。 #

参考示例 #

原始输入数据 #

1, 'Team A', 'Team W', 0, 1, '12/02/2021'
2, 'Team B', 'Team x', 2, 1, '12/10/2021'
...
n, 'Team ??', 'Team ??', 3, 1, '14/12/2021'

已存在Team表数据 #

Team(1, 'Team A')
Team(2, 'Team B')
Team(3, 'Team W')
Team(4, 'Team X')

Matches表期望输出 #

Matches(1, 1, 2, 0, 1, '12/02/2021')
Matches(2, 3, 2, 2, 1, '12/10/2021')
Matches(3, 4, 3, 3, 1, '14/12/2021')

核心逻辑 #

插入比赛记录时先在Teams表中查找对应球队名称的ID，若不存在该球队则先写入Teams表，两支参赛队都需要做该校验；也可以提前录入所有球队数据，再写入Matches表记录。 #

问题描述 #

已有的参考示例都是操作当前表自带字段的场景，本场景需要处理的是关联外键（FK）而非直接存储的字段数据，尝试编写的触发器如下，但是无法实现需求：

CREATE TRIGGER `searchFkTeam` Befor Insert ON `matches` FOR EACH ROW
begin
 SET xteam = 'Team W'
 INSERT INTO team (name) VALUES (xteam)
 WHERE NOT EXISTS (
    SELECT id FROM teams WHERE name=xteam
 );
end;
DELIMITER ;

核心卡点：不知道该怎么声明变量，matches表中不存在new.team这个字段，存储的是球队对应的FK，触发器无法拿到球队名称参数。 #

现有表结构 #

Team表 #

Matches表期望结构 #

实现方案 #

首先需要先给Team表的name字段添加唯一约束，避免重复插入球队：

ALTER TABLE teams ADD UNIQUE INDEX idx_team_name (name);

方案1：批量导入用临时表处理（推荐） #

适合一次性导入大量带球队名称的原始比赛数据的场景：

1. 创建临时表存储原始输入数据

CREATE TEMPORARY TABLE tmp_match_input (
    input_id INT,
    team_a_name VARCHAR(100),
    team_b_name VARCHAR(100),
    goal_a INT,
    goal_b INT,
    match_date VARCHAR(20)
);

2. 将原始CSV格式的输入数据导入到临时表
3. 批量插入不存在的球队，已存在的自动跳过

INSERT IGNORE INTO teams (name)
SELECT team_a_name FROM tmp_match_input
UNION
SELECT team_b_name FROM tmp_match_input;

4. 关联Team表生成符合要求的比赛记录，插入正式Matches表

INSERT INTO matches (Team_A, Team_B, Goal_A, Goal_B, Date)
SELECT 
    t1.id AS team_a_id,
    t2.id AS team_b_id,
    tmp.goal_a,
    tmp.goal_b,
    tmp.match_date
FROM tmp_match_input tmp
LEFT JOIN teams t1 ON tmp.team_a_name = t1.name
LEFT JOIN teams t2 ON tmp.team_b_name = t2.name;

方案2：单条插入用存储过程封装 #

适合前端/接口单条提交比赛数据的场景：

DELIMITER //
CREATE PROCEDURE add_match(
    IN p_team_a_name VARCHAR(100),
    IN p_team_b_name VARCHAR(100),
    IN p_goal_a INT,
    IN p_goal_b INT,
    IN p_match_date VARCHAR(20)
)
BEGIN
    DECLARE v_team_a_id INT;
    DECLARE v_team_b_id INT;
    -- 处理主队，不存在则插入
    INSERT IGNORE INTO teams (name) VALUES (p_team_a_name);
    SELECT id INTO v_team_a_id FROM teams WHERE name = p_team_a_name;
    -- 处理客队，不存在则插入
    INSERT IGNORE INTO teams (name) VALUES (p_team_b_name);
    SELECT id INTO v_team_b_id FROM teams WHERE name = p_team_b_name;
    -- 插入比赛记录
    INSERT INTO matches (Team_A, Team_B, Goal_A, Goal_B, Date)
    VALUES (v_team_a_id, v_team_b_id, p_goal_a, p_goal_b, p_match_date);
END //
DELIMITER ;

调用方式：

CALL add_match('Team A', 'Team W', 0, 1, '12/02/2021');

为什么不用触发器实现 #

Matches表本身存储的是球队ID，没有球队名称字段，触发器触发时无法获取原始球队名称参数，不适合处理这类跨表参数转换的需求。

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