mysql 字段限制_想要将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]?

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?

Many thanks!

解决方案

You can simulate a check constraint in MySQL using triggers.

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 ;