想要将MySQL字段的值限制为特定范围(十进制值) [英] Want to restrict the value of a MySQL field to specific range (Decimal values)

问题描述

我想将表格行中的字段值限制为特定范围.可以将我的Relationship_level字段限制为[0.00到1.00] 吗?

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]?

目前我正在使用DECIMAL(2,2)，它不允许DECIMAL(1,2)，因为M必须大于等于D.我假设DECIMAL(2,2)的数据类型实际上将允许值从00.00到99.99?

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?

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?

非常感谢！

推荐答案

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

You can simulate a check constraint in MySQL using triggers.

例如，如果要强制将所有大于1.00的值存储为1.00，则可以使用2个这样的触发器来做到这一点:

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:

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 ;

这篇关于想要将MySQL字段的值限制为特定范围(十进制值)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！