MySQL如何为两张独立表全量行生成pivot table关联记录

前提说明 #

当前库内三张表结构与存量数据如下：

• plans表：计划主表，含id、name、description字段，存量2条计划数据
• services表：服务主表，含id、name字段，存量2条服务数据
• service_plan表：服务-计划多对多关联pivot表，含id、service_id、plan_id、price字段，当前仅存service_id=1关联全部计划的2条记录，需补全所有服务与计划的组合关联，最终表内共2*2=4条笛卡尔积组合记录，新增记录填充统一默认测试价格。

实现语句 #

核心逻辑是通过CROSS JOIN生成两张主表的全量笛卡尔积组合，过滤已存在的关联后批量插入，不会造成重复数据。语句中0.00为示例默认测试价格，可根据实际需求替换为指定数值：

INSERT INTO service_plan (service_id, plan_id, price)
SELECT
  s.id,
  p.id,
  0.00 -- 此处替换为实际需要的默认测试定价
FROM services s
CROSS JOIN plans p
WHERE NOT EXISTS (
  SELECT 1
  FROM service_plan sp
  WHERE sp.service_id = s.id
    AND sp.plan_id = p.id
);

结果验证 #

执行插入后可运行以下查询校验结果，返回结果应为4条记录，包含所有服务与计划的两两组合：

SELECT service_id, plan_id, price FROM service_plan ORDER BY service_id, plan_id;

注意：如果service_plan表的id字段不是自增属性，需要在插入语句中补充id的生成逻辑，大部分场景下中间表主键设为自增即可直接运行上述语句。

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