将MySQL数据类型的值限制在特定范围（最好不是ENUM） [英] Limit the value of a MySQL datatype to a specific range (preferably not ENUM)

问题描述

我想将可以存储在字段中的数据类型值限制在整数值的特定范围内：[0,10]。

I want to limit the datatype value that can be stored within a field to a specific range of integer values: [0,10].

在用户输入内一个PHP脚本，我验证和清理数据，以确保它在0到10之间。但是，有没有办法确保这在投票表本身通过某种数据类型或约束保持为真？

On user input within a PHP script I validate and sanitise the data to make sure it is within the range 0 to 10. However, is there a way to ensure this remains true within the votes table itself via some sort of datatype or constraint?

此刻，我将int值存储在UNSIGNED TINYINT中，当然它的范围是0-255。我知道ENUM是一个选择。但是，我已经看到使用数字是不可取的： http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/

At the moment I store the int value within an UNSIGNED TINYINT which of course has the range of 0-255. I am aware of ENUM as an option. However, I have read that it is not advisable when using numbers: http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/

SQL：

CREATE TABLE votes (
vote_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
article_id MEDIUMINT UNSIGNED NOT NULL,
user_id MEDIUMINT UNSIGNED NOT NULL,
user_vote TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (vote_id),
FOREIGN KEY (article_id) REFERENCES articles (article_id) ON DELETE CASCADE ON UPDATE NO ACTION,
FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT article_uservoted UNIQUE KEY (user_id, article_id),
INDEX vote_value (article_id, user_id, user_vote)
) ENGINE=INNODB;

我希望有人可以协助。

推荐答案

您可以创建一个允许的投票值表，并在您的投票表中添加一个外键，所以当您尝试使用user_vote值插入不存在于allowed_votes表中的投票时，您将获得约束失败错误：

You can create a table of allowed vote values and add a foreign key in your votes table, so when you try to insert a vote with user_vote value other than existing in your allowed_votes table you get a constraint fail error:

CREATE TABLE allowed_votes (
  vote_rank TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (vote_rank)
) ENGINE = InnoDB;

INSERT INTO allowed_votes( vote_rank ) VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

ALTER TABLE votes
ADD FOREIGN KEY (user_vote) REFERENCES allowed_votes (vote_rank);

这篇关于将MySQL数据类型的值限制在特定范围（最好不是ENUM）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！