mysql - 导入以前导出的 mysql 触发器时出错

Question

我有一个一直在工作的 mysql 触发器，我导出并删除了它并试图将它放回去，但我一直遇到以下错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 12

我的触发器是:

    CREATE TRIGGER `accounts_tracking` AFTER UPDATE ON `accounts`
 FOR EACH ROW BEGIN
IF( NEW.`check_level` != OLD.`check_level` ) THEN
INSERT INTO `accounts_tracking` ( `change_type`, `account_id`, `field`, `old_int`, `new_int`, `old_time`, `new_time` )
VALUES
( "1", 
 OLD.id, 
 "check_level",
 OLD.`check_level`, 
 NEW.`check_level`, 
 UNIX_TIMESTAMP(), 
 UNIX_TIMESTAMP());
END IF;
END

第 12 行是第二个 UNIX_TIMESTAMP()

我的表结构如下:

CREATE TABLE `accounts_tracking` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `change_type` smallint(5) unsigned NOT NULL,
  `account_id` int(10) unsigned NOT NULL,
  `field` varchar(255) NOT NULL,
  `old_int` int(11) NOT NULL,
  `new_int` int(11) NOT NULL,
  `new_time` int(10) unsigned NOT NULL,
  `old_time` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `account_id` (`account_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

服务器类型:MySQL服务器版本:5.1.73-log

谢谢。

Answer 1

正如 barranka 在评论部分建议的那样，您需要将此触发器括在分隔符中，如下所示:

DELIMITER $$
    CREATE TRIGGER `accounts_tracking` AFTER UPDATE ON `accounts`
 FOR EACH ROW BEGIN
IF( NEW.`check_level` != OLD.`check_level`) THEN
INSERT INTO `accounts_tracking` ( `change_type`, `account_id`, `field`, `old_int`, `new_int`, `old_time`, `new_time` )
VALUES
( "1", 
 OLD.id, 
 "check_level",
 OLD.`check_level`, 
 NEW.`check_level`, 
 UNIX_TIMESTAMP(), 
 UNIX_TIMESTAMP());
END IF;
END$$
DELIMITER ;

原因是通过添加一个Begin and End声明你实际上是在创建一个 stored routine/procedure与触发器本身。为了运行多个语句，例如在存储例程/过程中，您需要添加分隔符。

在触发器中没有 Begin 和 End 的其他情况下，您不需要分隔符。例如:

CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;