如何用mysql实现商品评价系统_mysql商品评价数据库搭建方法

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE reviews (
    review_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    user_id INT NOT NULL,
    rating TINYINT NOT NULL CHECK (rating >= 1 AND rating <= 5), -- 评分1-5
    comment TEXT, -- 评价内容
    parent_id INT DEFAULT NULL, -- 若为回复，则指向父评论ID
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'approved', -- 审核状态
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES reviews(review_id) ON DELETE CASCADE
);

SELECT 
    r.review_id,
    u.username,
    r.rating,
    r.comment,
    r.created_at
FROM reviews r
JOIN users u ON r.user_id = u.user_id
WHERE r.product_id = 1 AND r.parent_id IS NULL AND r.status = 'approved'
ORDER BY r.created_at DESC;

SELECT 
    r.review_id,
    u.username,
    r.comment,
    r.created_at
FROM reviews r
JOIN users u ON r.user_id = u.user_id
WHERE r.parent_id = 100 -- 假设主评论ID为100
ORDER BY r.created_at ASC;

SELECT 
    AVG(rating) AS avg_rating,
    COUNT(*) AS total_reviews
FROM reviews 
WHERE product_id = 1 AND parent_id IS NULL AND status = 'approved';

INSERT INTO reviews (product_id, user_id, rating, comment)
VALUES (1, 101, 5, '商品很棒，物流很快！');

INSERT INTO reviews (product_id, user_id, rating, comment, parent_id)
VALUES (1, 102, 0, '感谢您的好评！', 100); -- 回复ID为100的评论