想要将MySQL字段的值限制为特定范围（十进制值）

I want to restrict the value of a field in a row of a table to a specific range. Is it possible to restrict my relationship_level field to [0.00 to 1.00]?

我想将表格行中的字段值限制为特定范围。是否可以将我的relationship_level字段限制为[0.00到1.00]？

At the moment I am using DECIMAL(2,2), it wouldn't allow DECIMAL(1,2) as M must be >= D. I assume a data type of DECIMAL(2,2) will actually allow values from 00.00 up to 99.99?

目前我正在使用DECIMAL（2,2），它不允许DECIMAL（1,2），因为M必须> = D.我假设DECIMAL（2,2）的数据类型实际上允许来自00.00的值高达99.99？

CREATE TABLE relationships (
    from_user_id MEDIUMINT UNSIGNED NOT NULL,
    to_user_id MEDIUMINT UNSIGNED NOT NULL,
    relationship_level DECIMAL(2,2) UNSIGNED NOT NULL,
    PRIMARY KEY (from_user_id, to_user_id), 
    FOREIGN KEY (from_user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE NO ACTION,
    FOREIGN KEY (to_user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE NO ACTION,
    INDEX relationship_from_to (to_user_id, from_user_id, relationship_level)
) ENGINE = INNODB;

Is there a better way to do this, can anyone foresee any limitations?

有没有更好的方法来做到这一点，谁能预见到任何限制？

Many thanks!

非常感谢！

2 个解决方案

#1

You can simulate a check constraint in MySQL using triggers.

您可以使用触发器在MySQL中模拟检查约束。

For example, if you want to force all values larger than 1.00 to be stored as 1.00, you could do so with 2 triggers like this:

例如，如果要强制将大于1.00的所有值存储为1.00，则可以使用以下两个触发器执行此操作：

DELIMITER $$

DROP TRIGGER IF EXISTS tr_b_ins_relationships $$

CREATE TRIGGER tr_b_ins_relationships BEFORE INSERT ON relationships FOR EACH ROW BEGIN
  IF new.relationship_level > 1
  THEN
    SET new.relationship_level = 1;
  END IF;
END $$

DELIMITER ;


DELIMITER $$

DROP TRIGGER IF EXISTS tr_b_upd_relationships $$

CREATE TRIGGER tr_b_upd_relationships BEFORE UPDATE ON relationships FOR EACH ROW BEGIN
  IF new.relationship_level > 1
  THEN
    SET new.relationship_level = 1;
  END IF;
END $$

DELIMITER ;

#2

Actually, DECIMAL(2,2) will allow a decimal of up to 2 places, BOTH of which are allocated to decimal places. The maximum value for that field would be 0.99, and the minimum would be 0.00.

实际上，DECIMAL（2,2）将允许最多2位的小数，其中两位被分配到小数位。该字段的最大值为0.99，最小值为0.00。

To restrict values to 00.00 to 99.99, use DECIMAL(4,2) UNSIGNED.

要将值限制为00.00到99.99，请使用DECIMAL（4,2）UNSIGNED。

#1