Node.js+MySQL中外键关联双表查询生成API数据问题

问题分析 #

你的代码存在两个核心问题：

1. 第一次查询doctor_schedule_collections返回的resp是对象数组，无法直接通过resp.doctor_id获取单个医生ID；
2. 嵌套查询的方式无法处理多条预约记录的情况，逻辑上根本跑不通，而且多次查询数据库会导致性能低下。

最优解决方式：使用SQL JOIN关联查询 #

直接通过SQL的INNER JOIN将两张表关联，一次性获取所有需要的字段，无需多次查询数据库，代码更简洁高效。

修正后的API代码 #

app.get("/all_schedule", (req, res) => {
  // 明确指定需要查询的字段，避免返回冗余数据
  const query = `
    SELECT 
      dc.name, dc.address, dc.gender, dc.specialist, dc.degree,
      dsc.date, dsc.duration, dsc.starting_time, dsc.ending_time, dsc.fees
    FROM doctor_schedule_collections dsc
    INNER JOIN doctor_collections dc ON dsc.doctor_id = dc.id
  `;

  con.query(query, (err, results) => {
    if (err) {
      // 处理查询错误
      return res.status(500).json({ error: err.message });
    }
    // 无数据返回空数组，有数据直接返回结构化结果
    res.json(results.length > 0 ? results : []);
  });
});

代码说明 #

• 使用INNER JOIN关联doctor_schedule_collections（别名dsc）和doctor_collections（别名dc），关联条件是预约记录的doctor_id等于医生表的id；
• 明确列出需要的字段，避免返回contact_no、email等不需要的数据，减少数据传输量；
• 增加了错误处理逻辑，当数据库查询出错时返回500状态码和错误信息；
• 无论是否有数据，都返回结构化的JSON响应，前端可以统一处理。

备选方案：如果必须分两次查询（不推荐） #

如果出于某些原因需要分开查询，需要遍历预约记录数组，逐个查询医生信息并合并结果（注意处理异步逻辑）：

app.get("/all_schedule", (req, res) => {
  con.query("SELECT * FROM doctor_schedule_collections", (err, schedules) => {
    if (err) {
      return res.status(500).json({ error: err.message });
    }
    if (schedules.length === 0) {
      return res.json([]);
    }

    // 遍历所有预约记录，查询对应医生信息
    const promiseList = schedules.map(schedule => {
      return new Promise((resolve, reject) => {
        con.query(
          "SELECT name, address, gender, specialist, degree FROM doctor_collections WHERE id=?",
          [schedule.doctor_id],
          (err, doctor) => {
            if (err) reject(err);
            // 合并医生信息和预约信息
            resolve({
              ...doctor[0],
              date: schedule.date,
              duration: schedule.duration,
              starting_time: schedule.starting_time,
              ending_time: schedule.ending_time,
              fees: schedule.fees
            });
          }
        );
      });
    });

    // 等待所有查询完成
    Promise.all(promiseList)
      .then(finalResults => res.json(finalResults))
      .catch(err => res.status(500).json({ error: err.message }));
  });
});

这个方案效率较低，因为每一条预约记录都要发起一次数据库查询，数据量大时性能会很差，优先推荐使用JOIN的方式。

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