set range for column

I'm using in my database, many fields of a certain range, like:

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),
    .........;

Is there any way to write it more clearly ?

Something like:

--CREATE PROCEDURE/FUNCTION betw开发者_JAVA技巧een()..................
CREATE TABLE figures (
    deg FLOAT between(0,3.1415),
    prc FLOAT between(0,1),
    .......

At least, I don't want to write every filter twice. (ON INSERT,ON UPDATE)

---

prior to MySQL 8.0.16 Triggers are the best solution Re:check constraints...

'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. '

lifted directly from: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

From MySQL 8.0.16 though they now work as you would expect

  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))
  )

---

At least, I don't want to write every filter twice. (ON INSERT,ON UPDATE)

You can write a stored function and call that in your trigger.

DELIMITER $$

CREATE FUNCTION check_deg (degree FLOAT, olddegree FLOAT) RETURNS FLOAT
BEGIN
  DECLARE result FLOAT;
  result = IF(degree>3.1415926 OR degree <0, olddegree,degree);
  RETURN result;
END$$

DELIMITER ;

That way you have one point where the limits are defined and if anything changes you only have to change the boundaries in one place.

---

The best solution is to use a CHECK() constraint, but MySQL doesn't support CHECK() constraints. (MySQL parses them, then ignores them.)

In some cases, you can replace a CHECK() constraint with a foreign key reference to a table that contains all the valid values. Floating-point numbers are not a good candidate for that kind of solution, though.

That leaves triggers. In your case, your best bet is to use a trigger that calls a stored function.

---

I have a unfinished idea :

CREATE PROCEDURE check_constraint (table VARCHAR,field VARCHAR,condition VARCHAR)
BEGIN
    SET @update_trigger = CONCAT ("
        IF EXIST TRIGGER check_constraint_",table,"_",field,"
        BEGIN /*I don't know yet what to do*/ END
        ELSE /*IF TRIGGER DONT EXIST*/
        BEGIN
            CREATE TRIGGER check_constraint_",table,"_",field,"
            BEFORE UPDATE ON ",table," FOR EACH ROW SET
            NEW.",field,"=IF("condition, ", OLD.",field,",NEW.",field,");
        END
    ");
    PREPARE update_trigger FROM @update_trigger;
    EXECUTE update_trigger;
    DEALLOCATE PREPARE update_trigger;
    SET @insert_trigger = ..............................
END

After we have a completed function, we can just call it during creation of the database:

CALL check_constraint("table","field","NEW.field<34567");