多对多关联场景下批量插入metrics后如何获取所有插入ID并写入关联表？

问题核心 #

批量插入metrics表时，仅能获取最后一条记录的ID，导致无法为所有predictions记录创建关联关系。以下是几种可行的解决办法：

方法1：修正批量插入SQL，获取所有插入的ID #

如果使用的数据库支持批量插入返回所有自增ID（比如PostgreSQL、MySQL 8.0.22+、SQL Server），只需调整插入语句的写法，确保RETURNING（或OUTPUT）能返回所有插入记录的ID：

PostgreSQL/MySQL 8.0.22+示例：

INSERT into metrics (whatever)
VALUES 
  ('metric_data_1'),
  ('metric_data_2'),
  ...
  ('metric_data_10')
RETURNING id;

执行这条语句后，会返回10条插入记录的id列表，顺序与VALUES中的数据顺序完全对应。拿到这些ID后，直接和IDs_pred按顺序配对，批量插入predictionmetrics即可：

INSERT into predictionmetrics (prediction_id, metric_id)
VALUES
  (id_pred_1, id_met_1),
  (id_pred_2, id_met_2),
  ...
  (id_pred_10, id_met_10);

SQL Server示例：

INSERT into metrics (whatever)
OUTPUT inserted.id
VALUES 
  ('metric_data_1'),
  ('metric_data_2'),
  ...
  ('metric_data_10');

方法2：提前生成UUID作为Metrics的ID（同事建议的方案） #

跳过数据库自增ID，在应用层提前为每条指标生成UUID，直接指定metrics表的id字段，同时提前完成IDs_pred与UUID的配对：

1. 为10条指标生成对应的UUID，形成配对数据：[(id_pred_1, uuid_1, metric_data_1), (id_pred_2, uuid_2, metric_data_2), ...]
2. 批量插入metrics表：

INSERT into metrics (id, whatever)
VALUES
  (uuid_1, 'metric_data_1'),
  (uuid_2, 'metric_data_2'),
  ...
  (uuid_10, 'metric_data_10');

3. 直接用提前配对好的数据批量插入关联表：

INSERT into predictionmetrics (prediction_id, metric_id)
VALUES
  (id_pred_1, uuid_1),
  (id_pred_2, uuid_2),
  ...
  (id_pred_10, uuid_10);

这种方法无需依赖数据库返回ID，跨数据库兼容性更强，还能避免因数据库自增机制导致的顺序问题。

方法3：用CTE一次性完成插入与关联（适合PostgreSQL等支持CTE的数据库） #

通过公共表表达式（CTE）将metrics插入、ID获取、关联表插入合并为一条SQL，全程在数据库层面完成，避免应用层处理ID的繁琐：

WITH inserted_metrics AS (
    INSERT INTO metrics (whatever)
    VALUES 
      ('metric_data_1'),
      ('metric_data_2'),
      ...
      ('metric_data_10')
    RETURNING id, row_number() OVER () AS rn
),
prediction_list AS (
    SELECT unnest(ARRAY[id_pred_1, id_pred_2, ..., id_pred_10]) AS prediction_id, row_number() OVER () AS rn
)
INSERT INTO predictionmetrics (prediction_id, metric_id)
SELECT pl.prediction_id, im.id
FROM inserted_metrics im
JOIN prediction_list pl ON im.rn = pl.rn;

这里通过row_number()为插入的metrics和传入的IDs_pred生成对应行号，确保关联关系准确无误。

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