数据库更新异常：原材料库存扣除后villageMetal被设为0

核心问题出在UPDATE语句的语法错误：你在SET子句中用AND分隔多个字段的赋值操作，这是完全错误的写法。

错误原因解析 #

当你执行这条错误的UPDATE语句时：

UPDATE village SET villageMetal = ? AND villageWood = ? AND villageStone = ? AND villageCereals = ? WHERE villageID = ?

数据库会把整个表达式当作逻辑运算处理：

1. 先判断villageMetal = ?的布尔结果（1为真，0为假）
2. 再依次和后面的villageWood = ?等条件做AND逻辑运算
3. 最终把运算后的布尔值（通常是0）赋值给villageMetal字段，其他字段根本不会被更新

修正后的代码 #

把SET子句里的AND全部替换为逗号（,），这才是多字段更新的正确语法：

if($error != true){
    $akIronStock = $result["villageMetal"] - $iron;
    $akWoodStock = $result["villageWood"] - $wood;
    $akStoneStock = $result["villageStone"] - $stone;
    $akCerealsStock = $result["villageCereals"] - $cereals;

    var_dump($akIronStock);

    // 修正：用逗号分隔字段赋值
    $stmt = $this->pdo->prepare("UPDATE village SET villageMetal = ?, villageWood = ?, villageStone = ?, villageCereals = ? WHERE villageID = ?");
    $stmt->execute([$akIronStock, $akWoodStock, $akStoneStock, $akCerealsStock, $villageId]);

    $stmt = $this->pdo->prepare("INSERT INTO marketplace_insert (villageID, iron, wood, stone, cereals, silver, marketInsertEnd) VALUES (?,?,?,?,?,?,?)");
    $stmt->execute([$villageId, $iron, $wood, $stone, $cereals, $silver, $marketInsertEnd]);
}

额外优化建议 #

1. 完善错误提示：当前库存不足时仅设置error=true但无返回信息，建议返回明确提示，比如：

   if($result["villageMetal"] < $iron){
       return "金属库存不足，当前库存：{$result['villageMetal']}，需消耗：{$iron}";
   }
   

2. 移除冗余检查：函数参数已声明为int类型，is_numeric的判断完全多余，可以删除
3. 添加事务处理：库存更新和市场记录插入属于原子操作，建议用事务避免数据不一致：

   if($error != true){
       $this->pdo->beginTransaction();
       try {
           // 库存更新逻辑
           $stmt = $this->pdo->prepare("UPDATE village SET villageMetal = ?, villageWood = ?, villageStone = ?, villageCereals = ? WHERE villageID = ?");
           $stmt->execute([$akIronStock, $akWoodStock, $akStoneStock, $akCerealsStock, $villageId]);
   
           // 插入市场记录
           $stmt = $this->pdo->prepare("INSERT INTO marketplace_insert (villageID, iron, wood, stone, cereals, silver, marketInsertEnd) VALUES (?,?,?,?,?,?,?)");
           $stmt->execute([$villageId, $iron, $wood, $stone, $cereals, $silver, $marketInsertEnd]);
           
           $this->pdo->commit();
       } catch (Exception $e) {
           $this->pdo->rollBack();
           return "操作失败：".$e->getMessage();
       }
   }
   

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