如何使用mysql实现商品评价系统

CREATE TABLE reviews (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  user_id INT NOT NULL,
  rating TINYINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment TEXT,
  parent_id INT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
  FOREIGN KEY (product_id) REFERENCES products(id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (parent_id) REFERENCES reviews(id)
);

INSERT INTO reviews (product_id, user_id, rating, comment, parent_id) 
VALUES (101, 201, 5, '商品很棒！', NULL);

INSERT INTO reviews (product_id, user_id, rating, comment, parent_id) 
VALUES (101, 202, 5, '谢谢你的认可！', 1);

SELECT 
  r1.id, 
  r1.user_id, 
  u.username, 
  r1.rating, 
  r1.comment, 
  r1.created_at,
  r2.id AS reply_id,
  r2.user_id AS reply_user_id,
  u2.username AS reply_username,
  r2.comment AS reply_comment
FROM reviews r1
LEFT JOIN reviews r2 ON r1.id = r2.parent_id
JOIN users u ON r1.user_id = u.id
LEFT JOIN users u2 ON r2.user_id = u2.id
WHERE r1.product_id = 101 
  AND r1.parent_id IS NULL 
  AND r1.status = 'approved'
ORDER BY r1.created_at DESC, r2.created_at ASC;

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