MySQL数据库限时秒杀

-- 创建商品表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    stock INT NOT NULL,
    version INT NOT NULL DEFAULT 0
);

-- 插入商品数据
INSERT INTO products (name, stock) VALUES ('秒杀商品', 100);

-- 秒杀逻辑（PHP示例）
<?php
$productId = 1;
$userId = 123;

// 开启事务
$conn->begin_transaction();

try {
    // 查询商品信息
    $stmt = $conn->prepare("SELECT stock, version FROM products WHERE id = ?");
    $stmt->bind_param("i", $productId);
    $stmt->execute();
    $result = $stmt->get_result();
    $product = $result->fetch_assoc();

    if ($product['stock'] > 0) {
        // 更新库存（乐观锁）
        $newVersion = $product['version'] + 1;
        $stmt = $conn->prepare("UPDATE products SET stock = stock - 1, version = ? WHERE id = ? AND version = ?");
        $stmt->bind_param("iii", $newVersion, $productId, $product['version']);
        $stmt->execute();

        if ($stmt->affected_rows > 0) {
            // 秒杀成功，记录订单
            $stmt = $conn->prepare("INSERT INTO orders (user_id, product_id) VALUES (?, ?)");
            $stmt->bind_param("ii", $userId, $productId);
            $stmt->execute();

            // 提交事务
            $conn->commit();
            echo "秒杀成功！";
        } else {
            // 版本不一致，秒杀失败
            $conn->rollback();
            echo "秒杀失败，商品已被抢光！";
        }
    } else {
        // 库存不足，秒杀失败
        $conn->rollback();
        echo "秒杀失败，商品已被抢光！";
    }
} catch (Exception $e) {
    // 异常处理
    $conn->rollback();
    echo "秒杀失败：" . $e->getMessage();
}
?>