MySQL触发器用于在多次插入之前检查值 [英] MySQL trigger for checking values before insert multple times
问题描述
我有一个表女巫存储最低和最高气温，海水温度作为tinyint"和其他参数.由于我希望空气的新输入在 -50 到 +50 度之间，所以我在桌子上做了一个触发器.但首先我做了这个程序:
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
触发器:
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
如您所见，我为最大值和最小值调用了两次 check_temp.由于我有更多包含气象参数的列，因此我需要将它们的范围检查"添加到上述触发器中.
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.
我的问题是:这是最明智的做法吗?像这样使用该程序两次(一旦我添加了海水温度，它们将是三次)，对我来说看起来很愚蠢.并在触发器中一一检查所有8个参数?我每次插入大约 50 行.使用触发器会慢得多吗?到目前为止，我使用了 PHP 端检查，但决定改用数据库功能.我可以这样使用触发器吗，或者它的目的完全不同?任何有关该主题的建议将不胜感激.MySQL 是 5.5.27
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
推荐答案
作为其他产品的 CHECK Constraints 的功能将适用于这种特殊情况，但 MySQL 不支持此功能.通常触发器通常用于实现您的需要.
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.
在下面的示例中，非常基本，所有列都在触发器上验证，当您尝试插入 50 条记录时，需要几毫秒.这只是概念验证，方便您在类似生产的环境中执行测试.
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);
这篇关于MySQL触发器用于在多次插入之前检查值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
