如何使用mysql实现电商购物车_mysql电商购物车数据库设计

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL
);

CREATE TABLE cart (
    cart_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT DEFAULT NULL,
    session_id VARCHAR(100) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

CREATE TABLE cart_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    cart_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (cart_id) REFERENCES cart(cart_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    UNIQUE KEY unique_cart_product (cart_id, product_id)
);

SELECT 
    p.name,
    p.price,
    ci.quantity,
    (p.price * ci.quantity) AS subtotal
FROM cart_items ci
JOIN products p ON ci.product_id = p.product_id
JOIN cart c ON ci.cart_id = c.cart_id
WHERE c.user_id = 1;

-- 先检查是否已有该商品
SELECT item_id, quantity FROM cart_items ci
JOIN cart c ON ci.cart_id = c.cart_id
WHERE c.user_id = 1 AND ci.product_id = 101;
<p>-- 如果存在，执行更新
UPDATE cart_items ci
JOIN cart c ON ci.cart_id = c.cart_id
SET ci.quantity = ci.quantity + 1
WHERE c.user_id = 1 AND ci.product_id = 101;</p><p>-- 如果不存在，插入新记录（需先获取或创建cart_id）
INSERT INTO cart_items (cart_id, product_id, quantity)
VALUES (1, 101, 1);</p>

UPDATE cart_items ci
JOIN cart c ON ci.cart_id = c.cart_id
SET ci.quantity = 3
WHERE c.user_id = 1 AND ci.product_id = 101;

DELETE ci FROM cart_items ci
JOIN cart c ON ci.cart_id = c.cart_id
WHERE c.user_id = 1 AND ci.product_id = 101;

DELETE FROM cart_items WHERE cart_id IN (
    SELECT cart_id FROM cart WHERE user_id = 1
);