如何同时插入含外键关联的两张表并批量处理数组插入数据

1. 准确获取刚插入表a的自增ID #

用SELECT * FROM a ORDER BY id DESC LIMIT 1存在并发风险——如果同时有其他请求插入表a，拿到的可能不是当前请求生成的ID。更可靠的方案有两种：

方案一：利用数据库内置函数（以MySQL为例） #

在插入表a后，直接调用驱动返回结果中的insertId字段，这个值是会话隔离的，不会被其他请求干扰：

const insertAResult = await myDataSource.query(
  `INSERT INTO a (user_id, a_data) VALUES (?,?)`,
  [user_id, a_data]
);
// 从插入结果中直接获取当前会话生成的自增ID
const aId = insertAResult.insertId;

方案二：依赖驱动返回的插入结果 #

如果使用mysql2这类驱动，执行INSERT语句后返回的结果对象会直接包含insertId字段，这是最直接且安全的方式，无需额外查询。

2. 处理数组格式请求体并批量插入表b #

针对req.body中typeId和figure两个对应数组的情况，需先将两个数组的元素按索引配对，生成批量插入的参数列表，再构造批量INSERT语句：

步骤说明： #

• 先校验两个数组长度是否一致（避免数据错位）
• 遍历数组，将每一组[aId, typeId[i], figure[i]]整理成参数数组
• 构造包含多组VALUES的INSERT语句，用占位符对应参数

具体实现 #

// 从req.body中取出数据
const { typeId, figure } = req.body;

// 校验数组长度一致
if (typeId.length !== figure.length) {
  throw new Error("typeId与figure数组长度不匹配");
}

// 生成批量插入的参数列表
const batchParams = typeId.map((id, index) => [aId, id, figure[index]]);

构造批量插入SQL并执行：

const insertBResult = await myDataSource.query(
  `INSERT INTO b(a_id, type_id, count) VALUES ${batchParams.map(() => "(?,?,?)").join(",")}`,
  batchParams.flat() // 将二维数组拍平成一维，对应占位符顺序
);

完整修正后的DAO代码 #

const createData = async(user_id, a_data, reqBody) => {
  // 插入表a并获取自增ID
  const insertAResult = await myDataSource.query(
    `INSERT INTO a (user_id, a_data) VALUES (?,?)`,
    [user_id, a_data]
  );
  const aId = insertAResult.insertId;

  const { typeId, figure } = reqBody;
  // 校验数组长度
  if (typeId.length !== figure.length) {
    throw new Error("typeId与figure数组长度必须一致");
  }

  // 生成批量插入参数
  const batchParams = typeId.map((tid, idx) => [aId, tid, figure[idx]]);
  
  // 批量插入表b
  const insertBResult = await myDataSource.query(
    `INSERT INTO b(a_id, type_id, count) VALUES ${batchParams.map(() => "(?,?,?)").join(",")}`,
    batchParams.flat()
  );

  return { insertAResult, insertBResult };
};

额外优化：事务保证数据一致性 #

建议用事务包裹两次插入操作，避免表a插入成功但表b插入失败导致的数据不一致：

const createData = async(user_id, a_data, reqBody) => {
  const connection = await myDataSource.getConnection();
  await connection.beginTransaction();
  try {
    // 插入表a
    const insertAResult = await connection.query(
      `INSERT INTO a (user_id, a_data) VALUES (?,?)`,
      [user_id, a_data]
    );
    const aId = insertAResult.insertId;

    const { typeId, figure } = reqBody;
    if (typeId.length !== figure.length) {
      throw new Error("typeId与figure数组长度必须一致");
    }

    const batchParams = typeId.map((tid, idx) => [aId, tid, figure[idx]]);
    const insertBResult = await connection.query(
      `INSERT INTO b(a_id, type_id, count) VALUES ${batchParams.map(() => "(?,?,?)").join(",")}`,
      batchParams.flat()
    );

    await connection.commit();
    return { insertAResult, insertBResult };
  } catch (err) {
    await connection.rollback();
    throw err;
  } finally {
    await connection.release();
  }
};

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