在线汽车租赁系统MySQL表优化：新增quantity字段及批量预约实现咨询

针对你要管理同特征车辆库存（比如5辆同款丰田），且预约多辆时需插入不同car_id记录的需求，我整理了一套从数据库调整到业务逻辑的完整实现方案：

一、数据库结构优化建议 #

首先，我们需要确保能高效识别同特征车辆并管理其可用性：

• 方案1：基于现有表扩展（无需新增表）

  • 给car表新增car_status字段（VARCHAR类型，比如值为available/reserved/maintenance），用于快速标记车辆当前状态，避免频繁关联reservedCar表查询可用性。
  • 同特征车辆通过car_make+car_model字段来标识，比如所有丰田卡罗拉的car_make都是Toyota，car_model都是Corolla。

• 方案2：新增车型表（更规范的库存管理）
  如果需要更清晰的分组库存统计，可以新增car_type表：

  CREATE TABLE car_type (
      car_type_id INT PRIMARY KEY AUTO_INCREMENT,
      car_make VARCHAR(50) NOT NULL,
      car_model VARCHAR(50) NOT NULL,
      total_quantity INT NOT NULL COMMENT '该车型总库存',
      available_quantity INT NOT NULL COMMENT '该车型可用库存',
      UNIQUE KEY idx_make_model (car_make, car_model)
  );
  

  然后修改car表，新增外键关联car_type：

  ALTER TABLE car ADD COLUMN car_type_id INT;
  ALTER TABLE car ADD FOREIGN KEY (car_type_id) REFERENCES car_type(car_type_id);
  

  这种方式可以直接通过car_type表的available_quantity快速判断是否有足够库存，再去筛选具体可用车辆。

二、核心业务逻辑流程 #

当用户提交N辆同特征车辆的预约请求时，执行以下步骤：

1. 开启数据库事务：确保所有操作要么全部成功，要么全部回滚，避免数据不一致。
2. 筛选可用车辆：根据用户选择的车型（car_make+car_model）、取车/还车日期，筛选出未被预约且时间不冲突的车辆，取前N辆。
3. 创建主预约记录：在reservation表插入一条记录，获取生成的reservation_id。
4. 批量插入预约车辆关联记录：对每一辆选中的可用车辆，在reservedCar表插入一条包含reservation_id、car_id、取还日期的记录。
5. 更新库存/车辆状态：如果用了car_status字段，将选中车辆的状态改为reserved；如果用了car_type表，更新available_quantity减去预约数量。
6. 提交事务：所有操作完成后提交事务，若中间出现异常则回滚。

三、PHP代码实现示例 #

以下是基于PDO的核心代码示例（以方案1为例）：

数据库连接 #

// 初始化PDO连接
$pdo = new PDO('mysql:host=localhost;dbname=car_rental;charset=utf8mb4', 'your_username', 'your_password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

预约处理函数 #

function reserveMultipleCars($customerId, $carMake, $carModel, $pickupDate, $returnDate, $quantity) {
    global $pdo;

    try {
        // 开启事务
        $pdo->beginTransaction();

        // 1. 筛选可用车辆：未被预约且时间不冲突
        $availableCarsStmt = $pdo->prepare("
            SELECT c.car_id
            FROM car c
            LEFT JOIN reservedCar rc ON c.car_id = rc.car_id
            WHERE c.car_make = ?
              AND c.car_model = ?
              AND (
                  rc.car_id IS NULL
                  OR NOT (rc.pickup_date <= ? AND rc.return_date >= ?)
              )
            LIMIT ?
        ");
        // 注意日期参数的顺序：判断现有预约是否和新预约时间重叠
        $availableCarsStmt->execute([$carMake, $carModel, $returnDate, $pickupDate, $quantity]);
        $availableCarIds = $availableCarsStmt->fetchAll(PDO::FETCH_COLUMN, 0);

        // 检查可用车辆数量是否满足需求
        if (count($availableCarIds) < $quantity) {
            throw new Exception("可用车辆不足，当前仅能提供 " . count($availableCarIds) . " 辆");
        }

        // 2. 创建预约主记录（假设总价是按日单价*天数*数量计算，你可以根据实际定价逻辑调整）
        $totalPrice = calculateTotalPrice($carMake, $carModel, $pickupDate, $returnDate, $quantity);
        $reservationStmt = $pdo->prepare("
            INSERT INTO reservation (reservation_date, customer_id, total_price)
            VALUES (NOW(), ?, ?)
        ");
        $reservationStmt->execute([$customerId, $totalPrice]);
        $reservationId = $pdo->lastInsertId();

        // 3. 批量插入reservedCar记录，并更新车辆状态
        $reservedCarStmt = $pdo->prepare("
            INSERT INTO reservedCar (reservation_id, car_id, pickup_date, return_date)
            VALUES (?, ?, ?, ?)
        ");
        $updateCarStatusStmt = $pdo->prepare("
            UPDATE car SET car_status = 'reserved' WHERE car_id = ?
        ");

        foreach ($availableCarIds as $carId) {
            $reservedCarStmt->execute([$reservationId, $carId, $pickupDate, $returnDate]);
            $updateCarStatusStmt->execute([$carId]);
        }

        // 提交事务
        $pdo->commit();
        return [
            'success' => true,
            'reservation_id' => $reservationId,
            'message' => "成功预约 {$quantity} 辆车辆"
        ];
    } catch (Exception $e) {
        // 回滚事务
        $pdo->rollBack();
        return [
            'success' => false,
            'error' => $e->getMessage()
        ];
    }
}

// 辅助函数：计算预约总价（示例逻辑，需根据实际业务调整）
function calculateTotalPrice($carMake, $carModel, $pickupDate, $returnDate, $quantity) {
    // 这里可以根据车型设置不同的日单价，比如丰田卡罗拉每日50元
    $dailyRates = [
        'Toyota_Corolla' => 50,
        'Honda_Civic' => 55
        // 其他车型的定价...
    ];
    $rateKey = "{$carMake}_{$carModel}";
    $dailyRate = isset($dailyRates[$rateKey]) ? $dailyRates[$rateKey] : 45; // 默认单价

    // 计算天数
    $pickupTimestamp = strtotime($pickupDate);
    $returnTimestamp = strtotime($returnDate);
    $days = ceil(($returnTimestamp - $pickupTimestamp) / (60 * 60 * 24));

    return $dailyRate * $days * $quantity;
}

四、额外优化建议 #

• 索引优化：给reservedCar表的car_id、pickup_date、return_date建立联合索引，大幅提升可用车辆筛选的查询效率：

  CREATE INDEX idx_car_date ON reservedCar (car_id, pickup_date, return_date);
  

• 预约取消逻辑：当用户取消预约时，要删除reservedCar中对应的记录，同时将车辆状态改回available，如果用了car_type表则恢复available_quantity。
• 并发处理：如果系统有高并发预约需求，可以在查询可用车辆时加行锁，避免同一辆车被多个用户同时预约，比如在SELECT语句末尾加FOR UPDATE。

内容的提问来源于stack exchange，提问作者M. IG