如何使用mysql开发投票系统_mysql投票数据库设计方法

CREATE TABLE vote_subjects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  type ENUM('single', 'multiple') DEFAULT 'single', -- 单选/多选
  start_time DATETIME,
  end_time DATETIME,
  is_active TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE vote_options (
  id INT AUTO_INCREMENT PRIMARY KEY,
  subject_id INT NOT NULL,
  option_text VARCHAR(100) NOT NULL,
  sort_order INT DEFAULT 0,
  FOREIGN KEY (subject_id) REFERENCES vote_subjects(id) ON DELETE CASCADE
);

CREATE TABLE vote_records (
  id INT AUTO_INCREMENT PRIMARY KEY,
  subject_id INT NOT NULL,
  option_id INT NOT NULL,  -- 可为空，如果是多选且允许不选具体项
  user_id INT NOT NULL,    -- 用户ID，可用账号系统关联
  ip_address VARCHAR(45),  -- 备用验证（非登录用户）
  vote_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_user_vote (subject_id, user_id), -- 限制同一用户对同一主题只投一次
  FOREIGN KEY (subject_id) REFERENCES vote_subjects(id),
  FOREIGN KEY (option_id) REFERENCES vote_options(id)
);

SELECT COUNT(*) FROM vote_records 
WHERE subject_id = ? AND user_id = ?;

INSERT INTO vote_records (subject_id, option_id, user_id, ip_address) 
VALUES (?, ?, ?, ?);

SELECT o.option_text, COUNT(r.id) as vote_count
FROM vote_options o
LEFT JOIN vote_records r ON o.id = r.option_id
WHERE o.subject_id = ?
GROUP BY o.id, o.option_text
ORDER BY vote_count DESC;

SELECT * FROM vote_subjects 
WHERE id = ? AND is_active = 1 
AND NOW() BETWEEN start_time AND end_time;