如何对比React本地state日期与MySQL数据库中存储的预约时段数据

一、后端查询逻辑修正 #

现有后端的查询条件有误，DateCreated是预约记录的创建时间，不是预约的实际时段，需要改为按预约的StartTime筛选，同时新增提交前二次校验接口避免前端篡改导致重复预约，修正后代码：

var db = require("../dbconnection");

var schedule = {
  // 根据选中日期拉取对应所有预约记录
  selectSchedule: function (data, callback) {
    db.query(
      "select Id, StartTime, EndTime, IsBlock from schedule where date(StartTime) = ?", 
      [data.date],
      callback
    );
  },
  // 新增：提交预约前二次校验接口
  checkSlotAvailable: function(data, callback) {
    db.query(
      "select Id from schedule where StartTime = ? limit 1",
      [data.actualDateStart],
      callback
    )
  }
};

module.exports = schedule;

二、前端逻辑调整 #

1. 新增状态管理 #

在组件内新增两个状态，分别存储已预约时段列表和查询加载状态：

const [bookedSlots, setBookedSlots] = useState([]);
const [slotLoading, setSlotLoading] = useState(false);

2. 选中日期后触发预约记录查询 #

修改handleDateClick函数，选中日期后立即调用后端接口拉取该日期的所有已预约数据：

const handleDateClick = async (arg) => {
  const newDate = arg.dateStr;
  setSlotLoading(true);
  // 调用后端查询接口
  const res = await Scheduleservice.selectSchedule({date: newDate});
  // 把已预约的StartTime提取成列表方便后续对比
  setBookedSlots(res.map(item => item.StartTime));
  setscheduleData({
    ...scheduleData,
    date: newDate,
  });
  setSlotLoading(false);
  // 跳转到选时段步骤
  setactiveStep(1);
};

3. 新增时段禁用判断函数 #

写一个公共函数判断当前时段是否需要禁用：

const isTimeDisabled = (rawTime, originIsBlock) => {
  // 拼接完整的预约开始时间
  const fullTime = scheduleData.date + rawTime;
  // 两个条件满足任意一个就禁用：1. 硬编码的IsBlock为true 2. 该时段已存在预约记录
  return bookedSlots.includes(fullTime) || originIsBlock;
}

4. 修改时段渲染逻辑 #

调整步骤1的时段按钮判断规则，同时增加加载态提示：

case 1:
  if(slotLoading) return <div className="col-lg-8 text-center">加载时段可用状态中...</div>
  return (
    <div
      className="col-lg-8 col-md-12 order-md-last text-center"
      id="time-blocks"
    >
      {timeSlots.map((data, i) => (
        <div className="timeslot-container mt-4" key={i}>
          <p className="pb-3 font-weight-bold">{data.dayType}</p>
          {data.times.map((times, j) => {
            const disabled = isTimeDisabled(times.Time, times.IsBlock);
            return (
              <Button
                variant={disabled ? "outline-dark" : "outline-primary"}
                disabled={disabled}
                className="d-block my-3 mx-auto"
                style={{ width: "150px" }}
                onClick={(e) => selectAppointment(e, times.Time)}
                key={j}
                value={Moment(scheduleData.date + times.Time).format(
                  "h:mm a"
                )}
              >
                {Moment(scheduleData.date + times.Time).format("h:mm a")}
              </Button>
            )
          })}
        </div>
      ))}
    </div>
  );

5. 提交预约增加二次校验（可选但推荐） #

在scheduleAppointment提交预约前，先调用后端校验接口确认时段未被占用，避免前端缓存或用户篡改导致重复预约：

const scheduleAppointment = async () => {
  // 原有表单校验逻辑保留
  if (
    scheduleData.fullName === "" ||
    scheduleData.resident === "" ||
    scheduleData.communicationMethod === "" ||
    scheduleData.email === "" ||
    scheduleData.phone === "" ||
    scheduleData.startTime === "" ||
    scheduleData.endTime === ""
  ) {
    console.log("submission false");
    return;
  }
  // 新增：二次校验时段可用性
  const checkRes = await Scheduleservice.checkSlotAvailable({
    actualDateStart: scheduleData.actualDateStart
  });
  if(checkRes.length > 0) {
    alert("该时段已被预约，请选择其他时段");
    // 重新刷新当前日期的预约状态
    const latestSlots = await Scheduleservice.selectSchedule({date: scheduleData.date});
    setBookedSlots(latestSlots.map(item => item.StartTime));
    return;
  }
  // 原有提交逻辑保留
  let insertData = {
    RoleId: "3",
    FullName: scheduleData.fullName,
    Resident: scheduleData.resident,
    CommunicationMethod: scheduleData.communicationMethod,
    Email: scheduleData.email,
    Phone: scheduleData.phone,
    StartTime: scheduleData.actualDateStart,
    EndTime: scheduleData.actualDateEnd,
    Description: scheduleData.description || null,
    DateCreated: Moment().format("YYYY-MM-DD hh:mm:ss"),
  };
  await Scheduleservice.insertSchedule(insertData);
  // 清空原有逻辑保留
  setscheduleData({
    date: "",
    startTime: "",
    endTime: "",
    fullName: "",
    resident: "",
    communicationMethod: "In Person",
    email: "",
    phone: "(1  )    -    ",
    description: "",
  });
  setscheduleDataError({
    fullNameError: "",
    residentError: "",
    communicationMethodError: "",
    emailError: "",
    phoneError: "",
    descriptionError: "",
  });
  setactiveStep(0);
  setShow(true);
};

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