MySQL BEFORE INSERT 触发器选择到多个变量？

Question

我正在尝试在 MySQL 中编写一个触发器，我需要设置几个变量的值。我需要放入这些变量的值来自现有表中的一行(不用担心多行，只会有一个):

DELIMITER |

CREATE TRIGGER ins_move_to_hist BEFORE INSERT ON EndOfDay
FOR EACH ROW
BEGIN
 DECLARE _RowsMatching int;
 DECLARE _EodId  int(10) unsigned;
  DECLARE _Open  decimal(6,4);
  DECLARE _High  decimal(6,4);
  DECLARE _Low   decimal(6,4);
  DECLARE _Close  decimal(6,4);
  DECLARE _Volume  int(10) unsigned;
  DECLARE _mtime  datetime;

 SELECT _RowsMatching = Count(*), 
  EodId as _EodId, 
  Open as _Open,
  High as _High,
  Low as _Low,
  Close as _Close,
  Volume as _Volume,
  mtime as _mtime
 from EndOfDay 
 where DateId = NEW.DateId
  and TickerId = NEW.TickerId;

但是，触发器不允许选择结果(我不需要)。所以，当我运行创建脚本时，它会给我这个错误:

Not allowed to return a result set from a trigger.

Answer 1

我相信您只需要将 as 语句更改为 into 语句即可。

更新:正如 Bill 在下面纠正我的那样，INTO 语句的格式更像插入语句，即；列列表 INTO 值列表。

SELECT Count(*), EodId, Open, High, Low, Close, Volume, mtime 
INTO _RowsMatching, _EodId, _Open, _High, _Low, _Close, _Volume, _mtime
...

谢谢，比尔。 ;)