mysql - 为列设置范围

Question

我在我的数据库中使用了一定范围内的许多字段，例如:

CREATE TABLE figures (
    deg FLOAT,-- between 0 and pi
    prc FLOAT,-- between 0 and 1
    .......
);
CREATE TRIGGER filter1 BEFORE UPDATE ON figures FOR EACH ROW SET
    NEW.deg=IF(NEW.deg>3.1415926 OR NEW.deg<0, OLD.deg,NEW.deg),
    NEW.prc=IF(NEW.prc>1 OR NEW.prc<0, OLD.prc,NEW.prc),
    ..........;
CREATE TRIGGER filter2 BEFORE INSERT ON figures FOR EACH ROW SET
    NEW.deg=IF(NEW.deg>3.1415926 OR NEW.deg<0, NULL,NEW.deg),
    NEW.prc=IF(NEW.prc>1 OR NEW.prc<0, NULL,NEW.prc),
    .........;

有没有办法写的更清楚些？
像这样的东西:

--CREATE PROCEDURE/FUNCTION between()..................
CREATE TABLE figures (
    deg FLOAT between(0,3.1415),
    prc FLOAT between(0,1),
    .......

至少，我不想每个过滤器都写两次。 (在插入时，在更新时)

Answer 1

MySQL 8.0.16 之前 触发器是最好的解决方案 回复:检查约束...

'The CHECK clause is parsed but ignored by all storage engines.'.....

'The reason for accepting but ignoring syntax clauses is for compatibility, to 
make it easier to port code from other SQL servers, and to run applications 
that create tables with references. '

直接取自:http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

从 MySQL 8.0.16 开始，尽管它们现在可以像您期望的那样工作

  CREATE TABLE figures (
    deg FLOAT, 
    prc FLOAT,   
    CONSTRAINT `deg_min` CHECK ((`deg` > 0)),
    CONSTRAINT `deg_max` CHECK ((`deg` < 3.1415)),
    CONSTRAINT `prc_min` CHECK ((`prc` > 0)),
    CONSTRAINT `prc_max` CHECK ((`prc` < 1))
  )