MySQL 触发器 - 将 SELECT 存储在变量中
我有一个触发器，其中我想要一个变量来保存从 SELECT 获得的 INT，因此我可以在两个 IF 语句中使用它，而不是调用 SELECT 两次.MySQL触发器中如何声明/使用变量?
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 SELECT twice. How do you declare/use variables in MySQL triggers?
推荐答案
您可以使用 DECLARE 语法在 MySQL 触发器中声明局部变量.
You can declare local variables in MySQL triggers, with the DECLARE syntax.
这是一个例子:
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
为变量赋值时，必须确保查询仅返回单个值，而不是一组行或一组列.例如，如果您的查询在实践中返回单个值，那没关系，但是一旦它返回多于一行，您就会得到ERROR 1242: Subquery returns more than 1 row".
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".
您可以使用 LIMIT 或 MAX() 来确保将局部变量设置为单个值.
You can use LIMIT or MAX() to make sure that the local variable is set to a single value.
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//
相关文章