MySQL触发器在插入多次之前检查值

Question

I have a table witch stores minimum and maximum air temperatures, sea water temperatures as "tinyint" and other params. Since I expect the new input to be between -50 and +50 degrees for air, I made a trigger on the table. But firstly I made this procedure:

    begin
    if temp<-50 or temp>50 then
    set sts = 0;
    else
    set sts = 1;
    end if;
    END

The trigger :

    BEGIN
    declare err boolean;
    declare msg varchar(255);
    call check_temp(NEW.Tmin,err);
    IF err!=1 THEN
    set msg = "Error: Tmin out of range.";
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
    END IF;

    call check_temp(NEW.Tmax,err);
    IF err!=1 THEN
    set msg = "Error: Tmax out of range.";
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
    END IF;
    END

As you can see, I call check_temp twice for the maximum and for the minimum values. As I have more columns with meteorological parameters, I need to add their "range check" to the above trigger.

My question is: is that the wisest thing to do? Using the procedure two times (and they are going to be three once I add the sea temperatures) like this, looks stupid to me. And checking all 8 parameters one by one in the trigger? I insert about 50 rows each time. Is it going to be much slower with the trigger? Up till now, I used PHP-side checking for this but decided to switch to the database capabilities instead. Can I use the trigger like that, or its purpose is completely different? Any advise on the topic will be appreciated. MySQL is 5.5.27

Answer 1

Functionality as the CHECK Constraints of other products would be appropriate in this particular case, but MySQL does not support this feature. Usually triggers are often used to achieve what you need.

In the following example, very basic, all columns are validated on the trigger and when you try to insert 50 records, it takes a few milliseconds. This is just a proof of concept and it would be convenient for you to perform a test on a production-like environment.

/*Table structure for table `table_variables` */

DROP TABLE IF EXISTS `table_variables`;

CREATE TABLE `table_variables` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `variable0` TINYINT(4) DEFAULT NULL,
  `variable1` TINYINT(4) DEFAULT NULL,
  `variable2` TINYINT(4) DEFAULT NULL,
  `variable3` TINYINT(4) DEFAULT NULL,
  `variable4` TINYINT(4) DEFAULT NULL,
  `variable5` TINYINT(4) DEFAULT NULL,
  `variable6` TINYINT(4) DEFAULT NULL,
  `variable7` TINYINT(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

/* Trigger structure for table `table_variables` */

DELIMITER $$

/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `trg_check_bi` */$$

CREATE TRIGGER `trg_check_bi` BEFORE INSERT ON `table_variables`
FOR EACH ROW
BEGIN
    DECLARE msg VARCHAR(255);
    DECLARE _min, _max TINYINT DEFAULT -49;
    SET _max := _max * (-1);
    SET @max = _max;
    IF (new.`variable0` NOT BETWEEN _min AND _max) THEN
        SET msg := 'Error: variable0 out of range.';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
    END IF;
    IF (new.`variable1` NOT BETWEEN _min AND _max) THEN
        SET msg := 'Error: variable1 out of range.';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
    END IF;
    IF (new.`variable2` NOT BETWEEN _min AND _max) THEN
        SET msg := 'Error: variable2 out of range.';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
    END IF;
    IF (new.`variable3` NOT BETWEEN _min AND _max) THEN
        SET msg := 'Error: variable3 out of range.';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
    END IF;
    IF (new.`variable4` NOT BETWEEN _min AND _max) THEN
        SET msg := 'Error: variable4 out of range.';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
    END IF;
    IF (new.`variable5` NOT BETWEEN _min AND _max) THEN
        SET msg := 'Error: variable5 out of range.';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
    END IF;
    IF (new.`variable6` NOT BETWEEN _min AND _max) THEN
        SET msg := 'Error: variable6 out of range.';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
    END IF;
    IF (new.`variable7` NOT BETWEEN _min AND _max) THEN
        SET msg := 'Error: variable7 out of range.';
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
    END IF;
END $$

DELIMITER ;

INSERT `table_variables` (
    `variable0`,
    `variable1`,
    `variable2`,
    `variable3`,
    `variable4`,
    `variable5`,
    `variable6`,
    `variable7`)
VALUES
    (25, 46, 6, 42, 46, -42, -6, 47),
    (11, -37, 26, -3, -44, 37, -28, -4),
    (14, 33, -21, 40, 19, 23, 10, 29),
    (-32, 1, -47, 10, 42, 36, 5, -34),
    (-38, -40, -35, -6, 27, 7, 4, -49),
    (-14, 29, 41, -29, -23, 22, 31, 41),
    (-34, -49, 5, 27, -27, 30, -14, -11),
    (36, -30, -14, -27, -44, 10, 33, -12),
    (-10, 34, -42, 29, 29, 10, 11, -21),
    (6, 45, -36, 29, 7, -3, 13, 25),
    (37, -35, -40, -47, 32, -42, 38, -27),
    (-4, 12, 24, 36, -39, 41, -22, 12),
    (-19, 14, -18, 16, -15, 27, 31, 28),
    (-3, -49, 11, -44, -8, 42, -8, -21),
    (-31, -44, 21, -6, -42, -47, 38, -11),
    (-21, -23, -1, 17, 36, -16, -40, -3),
    (-43, 40, -16, 48, 43, 22, 29, 32),
    (25, -21, -32, -47, 6, 28, -28, 23),
    (-45, -48, 42, 11, -22, 4, 36, 24),
    (-39, -21, -34, 39, -47, -10, 46, 16),
    (-10, -48, 37, -15, -37, 8, 5, -47),
    (-4, -25, 32, -8, 11, 31, -25, 26),
    (-40, -30, 20, 44, 12, -22, -1, 16),
    (32, -33, -14, -22, -19, 20, 13, -43),
    (-10, 31, 39, -44, 8, 23, 44, 3),
    (32, 3, -31, -15, -32, 34, 20, 47),
    (30, -42, 44, 5, 41, 43, 44, 43),
    (32, -16, -31, 43, -34, 45, -14, 37),
    (-15, -38, 3, -17, -46, -31, 33, 12),
    (13, -22, 0, 18, 42, 9, -31, -33),
    (-23, 32, -16, -27, -38, 38, -40, 30),
    (26, -9, 23, -4, -38, -31, 9, -11),
    (-31, 25, -24, 48, -30, 48, -10, -47),
    (41, 1, 31, 4, -21, 30, -33, -9),
    (8, 16, 7, 39, 25, -38, -23, -47),
    (-18, 1, 13, 13, -25, -14, -43, -25),
    (-47, -11, 38, -23, 15, 0, 3, -31),
    (-20, 44, 37, 4, -40, 33, 39, 47),
    (-29, -40, 35, -46, 6, 21, 41, -4),
    (4, -16, -42, -16, -1, -6, 23, -13),
    (14, -40, 5, 0, 32, 16, 34, -24),
    (24, 45, 7, -49, -22, -12, -43, -29),
    (32, 1, -41, 38, -28, -11, -17, -5),
    (-21, -39, 13, 37, -1, 32, 17, 40),
    (-1, 25, -20, -26, -22, 18, 8, 37),
    (13, 4, 32, 1, -41, 36, -38, 47),
    (4, 26, -29, 25, -33, 5, -24, 15),
    (-1, -1, 47, -4, -15, -11, 36, -29),
    (-9, -7, 42, -14, -48, 47, 39, 3),
    (-31, -20, 91, 17, -89, 4, -14, 22);