投票系统的Mysql唯一索引

Question

在用于记录帖子投票的表格中

CREATE TABLE votes
(
vote_id int(11) NOT NULL AUTO_INCREMENT,
post_id int(11) REFERENCES posts(post_id) ON DELETE CASCADE,
user_id int(11) REFERENCES users(user_id) ON DELETE SET NULL,
vote ENUM('Up', 'Down'),
ip varchar(255),
UNIQUE INDEX (on which???)
PRIMARY KEY(vote_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

如何添加UNIQUE INDEX来避免重复投票？如果投票者是user，UNIQUE INDEX应该适用于(post_id, user_id)；如果不是用户，则 UNIQUE INDEX 应适用于 (post_id, ip)。

事实上，我只需要为 (post_id, user_id) 或 (post_id, ip) 使用 UNIQUE INDEX；但不是两者。

Answer 1

修改了更多信息 为了按照您的要求进行具体操作，我会建议以下内容。添加源表以进一步规范化

CREATE TABLE vote_sources (
    source_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    // 0 will be treated as anonymous, as NULL can have issues on UNIQUE indexes
    user_id INT(11) UNSIGNED NOT NULL REFERENCES users(user_id) ON DELETE SET 0,
    ip VARCHAR(15) NOT NULL,
    dupeCheck VARCHAR(15) NOT NULL,
    PRIMARY KEY(source_id),
    UNIQUE INDEX (dupeCheck)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

CREATE TABLE votes
(
    vote_id int(11) NOT NULL AUTO_INCREMENT,
    post_id int(11) REFERENCES posts(post_id) ON DELETE CASCADE,
    source_id int(11) NOT NULL REFERENCES vote_sources(source_id) ON DELETE CASCADE,
    vote ENUM('Up', 'Down'),
    PRIMARY KEY(vote_id),
    UNIQUE INDEX(post_id,source_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

然后处理欺骗检查这个触发器

CREATE TRIGGER dupeSourceCheck
BEFORE INSERT ON vote_sources
FOR EACH ROW SET NEW.dupeCheck = IF(NEW.user_id>0,CAST(NEW.user_id AS CHAR),NEW.ip)

如果详细说明重复源，这又会导致重复键错误，同时仍然公开数字 user_id 以实现更高效的连接。