MySQL插入后触发器 - MyISAM与InnoDB比较

Question

我正在尝试编写一个after insert触发器，让它不会回滚对innodb表的插入操作。但MyISAM似乎没有这个问题。

让我举个例子:

CREATE TABLE `testTable` (
  `id` int(10) AUTO_INCREMENT,
  `data` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB; #Engine supports transactions

CREATE TABLE `dummyTable` (
  `id` int(10) AUTO_INCREMENT,
  `data` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


DELIMITER $$
CREATE TRIGGER triggerTest AFTER INSERT ON `testTable`
FOR EACH ROW
BEGIN
  INSERT INTO dummyTable VALUES(1, 2, 3, 4); #This will throw a column count error
END;$$
DELIMITER ;


INSERT INTO testTable(data) VALUES('This insert will be rolled back');
SELECT COUNT(1) FROM testTable; # 0

如果您将testTable的引擎更改为MyISAM，原始插入操作将不会被回滚，因为（我猜测）MyISAM不支持事务。

CREATE TABLE `testTable` (
  `id` int(10) AUTO_INCREMENT,
  `data` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM; #Engine does NOT support transactions

CREATE TABLE `dummyTable` (
  `id` int(10) AUTO_INCREMENT,
  `data` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

DELIMITER $$
CREATE TRIGGER triggerTest AFTER INSERT ON `testTable`
FOR EACH ROW
BEGIN
  INSERT INTO dummyTable VALUES(1, 2, 3, 4); #This will throw a column count error
END;$$
DELIMITER ;

INSERT INTO testTable(data) VALUES('This insert will not be rolled back');
SELECT COUNT(1) FROM testTable; # 1

问题：是否有一种方法可以使InnoDB表的插入触发器在触发器中出现错误时保留原始插入？

- Mike B

---

当然，MyISAM不会回滚原始插入，因为它无法回滚任何操作。 - user330315

Answer 1

是的，不同的行为与引擎是否支持事务有关：

对于事务表，语句失败应导致语句执行的所有更改回滚。触发器失败会导致语句失败，因此触发器失败也会导致回滚。对于非事务表，无法进行回滚，因此尽管语句失败，但在错误点之前执行的任何更改仍然有效。

您可以声明一个CONTINUE处理程序来处理这个特定的错误：

DELIMITER $$
CREATE TRIGGER triggerTest AFTER INSERT ON `testTable`
FOR EACH ROW
BEGIN
  DECLARE CONTINUE HANDLER
      FOR SQLSTATE '21S01' -- "Column count doesn't match value count"
      BEGIN END; -- do nothing (but continue)
  INSERT INTO dummyTable VALUES(1, 2, 3, 4);
END $$
DELIMITER ;

- RandomSeed

Answer 2

我完全同意到目前为止所说的一切。显然，非事务性存储引擎不会回滚。但是，您可以将触发器更改为BEFORE触发器，从而实现与InnoDB示例相同的结果，即更改未提交，如下所示：

CREATE TABLE `__testTable` (
  `id` int(10) AUTO_INCREMENT,
  `data` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM; #Engine does NOT support transactions

CREATE TABLE `dummyTable` (
  `id` int(10) AUTO_INCREMENT,
  `data` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

DELIMITER $$
CREATE TRIGGER triggerTest BEFORE INSERT ON `__testTable`
FOR EACH ROW
BEGIN
  INSERT INTO dummyTable VALUES(1, 2, 3, 4); #This will throw a column count error
END;$$
DELIMITER ;

INSERT INTO __testTable(data) VALUES('This insert will not occur!');
SELECT COUNT(1) FROM __testTable; # 1

- Dom