MySQL 触发器 - 将 SELECT 存储在变量中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/324605/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Trigger - Storing a SELECT in a variable
提问by
I have a trigger in which I want to have a variable that holds an INT I get from a SELECT, so I can use it in two IF statements instead of calling the SELECTtwice. How do you declare/use variables in MySQL triggers?
我有一个触发器，其中我想要一个变量来保存我从 a 获得的 INT SELECT，因此我可以在两个 IF 语句中使用它，而不是调用SELECT两次。你如何在 MySQL 触发器中声明/使用变量？
回答by Bill Karwin
You can declare local variables in MySQL triggers, with the DECLAREsyntax.
您可以使用DECLARE语法在 MySQL 触发器中声明局部变量。
Here's an example:
下面是一个例子：
DROP TABLE IF EXISTS foo;
CREATE TABLE FOO (
i SERIAL PRIMARY KEY
);
DELIMITER //
DROP TRIGGER IF EXISTS bar //
CREATE TRIGGER bar AFTER INSERT ON foo
FOR EACH ROW BEGIN
DECLARE x INT;
SET x = NEW.i;
SET @a = x; -- set user variable outside trigger
END//
DELIMITER ;
SET @a = 0;
SELECT @a; -- returns 0
INSERT INTO foo () VALUES ();
SELECT @a; -- returns 1, the value it got during the trigger
When you assign a value to a variable, you must ensure that the query returns only a single value, not a set of rows or a set of columns. For instance, if your query returns a single value in practice, it's okay but as soon as it returns more than one row, you get "ERROR 1242: Subquery returns more than 1 row".
为变量赋值时，必须确保查询仅返回单个值，而不是一组行或一组列。例如，如果您的查询在实践中返回单个值，那没关系，但是一旦它返回多行，您就会得到“ ERROR 1242: Subquery returns more than 1 row”。
You can use LIMITor MAX()to make sure that the local variable is set to a single value.
您可以使用LIMIT或MAX()来确保将局部变量设置为单个值。
CREATE TRIGGER bar AFTER INSERT ON foo
FOR EACH ROW BEGIN
DECLARE x INT;
SET x = (SELECT age FROM users WHERE name = 'Bill');
-- ERROR 1242 if more than one row with 'Bill'
END//
CREATE TRIGGER bar AFTER INSERT ON foo
FOR EACH ROW BEGIN
DECLARE x INT;
SET x = (SELECT MAX(age) FROM users WHERE name = 'Bill');
-- OK even when more than one row with 'Bill'
END//
回答by Bill Karwin
CREATE TRIGGER clearcamcdr AFTER INSERT ON `asteriskcdrdb`.`cdr`
FOR EACH ROW
BEGIN
SET @INC = (SELECT sip_inc FROM trunks LIMIT 1);
IF NEW.billsec >1 AND NEW.channel LIKE @INC
AND NEW.dstchannel NOT LIKE ""
THEN
insert into `asteriskcdrdb`.`filtre` (id_appel,date_appel,source,destinataire,duree,sens,commentaire,suivi)
values (NEW.id,NEW.calldate,NEW.src,NEW.dstchannel,NEW.billsec,"entrant","","");
END IF;
END$$
Dont try this @ home
不要尝试这个@home
回答by IgorS
`CREATE TRIGGER `category_before_ins_tr` BEFORE INSERT ON `category`
FOR EACH ROW
BEGIN
**SET @tableId= (SELECT id FROM dummy LIMIT 1);**
END;`;
回答by WEBjuju
I'm posting this solution because I had a hard time finding what I needed. This post got me close enough (+1 for that thank you), and here is the final solution for rearranging column data before insert if the data matches a test.
我发布这个解决方案是因为我很难找到我需要的东西。这篇文章让我很接近（为此+1，谢谢），如果数据与 test 匹配，这里是在插入之前重新排列列数据的最终解决方案。
Note: this is from a legacy projectI inherited where:
注意：这是我继承的一个遗留项目，其中：
- The Unique Key is a composite of
rridprefix+rrid - Before I took over there was no constraint preventing duplicate unique keys
- We needed to combine two tables (one full of duplicates) into the main table which now has the constraint on the composite key (so merging fails because the gaining table won't allow the duplicates from the unclean table)
on duplicate keyis less than ideal because the columns are too numerous and may change
- 唯一键是
rridprefix+rrid - 在我接手之前，没有任何限制可以防止重复的唯一键
- 我们需要将两个表（一个完整的重复项）合并到现在对复合键具有约束的主表中（因此合并失败，因为获得表不允许来自不干净表的重复项）
on duplicate key不太理想，因为列太多并且可能会改变
Anyway, here is the trigger that puts any duplicate keys into a legacy column while allowing us to store the legacy, bad data (and not trigger the gaining tables composite, unique key).
无论如何，这是将任何重复键放入遗留列的触发器，同时允许我们存储遗留的、错误的数据（并且不会触发获取表复合的唯一键）。
BEGIN
-- prevent duplicate composite keys when merging in archive to main
SET @EXIST_COMPOSITE_KEY = (SELECT count(*) FROM patientrecords where rridprefix = NEW.rridprefix and rrid = NEW.rrid);
-- if the composite key to be introduced during merge exists, rearrange the data for insert
IF @EXIST_COMPOSITE_KEY > 0
THEN
-- set the incoming column data this way (if composite key exists)
-- the legacy duplicate rrid field will help us keep the bad data
SET NEW.legacyduperrid = NEW.rrid;
-- allow the following block to set the new rrid appropriately
SET NEW.rrid = null;
END IF;
-- legacy code tried set the rrid (race condition), now the db does it
SET NEW.rrid = (
SELECT if(NEW.rrid is null and NEW.legacyduperrid is null, IFNULL(MAX(rrid), 0) + 1, NEW.rrid)
FROM patientrecords
WHERE rridprefix = NEW.rridprefix
);
END
回答by dkretz
Or you can just include the SELECT statement in the SQL that's invoking the trigger, so its passed in as one of the columns in the trigger row(s). As long as you're certain it will infallibly return only one row (hence one value). (And, of course, it must not return a value that interacts with the logic in the trigger, but that's true in any case.)
或者您可以只在调用触发器的 SQL 中包含 SELECT 语句，因此它作为触发器行中的列之一传入。只要您确定它肯定只会返回一行（因此是一个值）。（当然，它不能返回与触发器中的逻辑交互的值，但在任何情况下都是如此。）
