MySQL触发器-将SELECT存储在变量中 [英] MySQL Trigger - Storing a SELECT in a variable

问题描述

我有一个触发器，希望在其中拥有一个变量，该变量保存从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//

这篇关于MySQL触发器-将SELECT存储在变量中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！