触发到同一表上的UPDATE和SELECT-错误1235(42000) [英] Trigger to UPDATE and SELECT on same table - ERROR 1235 (42000)

问题描述

我正在尝试实施表多个递增列，这些列不会重复使用已删除的列值.这篇文章被已回答表MYSQL 的子集内的增量，但是，引用的帖子不满足声明的要求，因为它允许子集的增量键重复.在第一篇文章中，发表了评论:

I am trying to implement Table with multiple incrementing columns which doesn't reuse deleted column values. This post was tagged as already being answered by How auto-increment within a subset of the table MYSQL, however, the referenced post did not meet the stated requirements as it allows the subset incremented keys to be duplicated. In the first post, a comment was given:

创建一个表，该表将存储每种类型的最后一个AI编号.使用触发器 在每次插入时将其递增并复制到原始表. –保罗 明镜

Create a table that will store last AI numbers per type. Use a trigger to increment it on every insert and copy to the original table. – Paul Spiegel

我认为这是个好主意，并付诸实施.

I thought this was a great idea, and implemented it.

-- MySQL Script generated by MySQL Workbench
-- 02/19/17 08:53:34
-- Model: New Model    Version: 1.0
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`t1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t1` (
  `pk1` INT NOT NULL,
  `pk2` INT NOT NULL,
  `id2` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`pk1`, `pk2`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`t2`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t2` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `id2` INT NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
USE `mydb`;

DELIMITER $$
USE `mydb`$$
CREATE TRIGGER `t2_BINS` BEFORE INSERT ON `t2` FOR EACH ROW
begin
UPDATE t1 SET id2=id2+1 WHERE pk1=NEW.pk1 AND pk2=NEW.pk2;
SET NEW.id2=(SELECT id2 FROM t1 WHERE pk1=NEW.pk1 AND pk2=NEW.pk2);
end$$


DELIMITER ;

但是，执行脚本时，出现以下错误:

When executing the script, however, I receive the following error:

ERROR 1235 (42000) at line 68: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

我正在使用MySQL 5.5.54.有新版本可以做到吗?

I am using MySQL 5.5.54. Are there new versions which can do so?

有什么解决方法可以做到这一点?

Are there any workarounds to accomplish this?

推荐答案

您可能已经在一个表上定义了两个BEFORE INSERT触发器.至少这就是错误消息所显示的内容.在此演示中取消注释第二个触发器，您将得到相同的错误消息

You have probably defined two BEFORE INSERT triggers on one table. At least that's what the error message says. Uncomment the second trigger in this demo and you will get the same error message

This version of MySQL doesn't yet support 'multiple triggers
with the same action time and event for one table'

第二个错误是您在错误的表上定义了触发器(或在触发器中混合了表).您的触发器在t2上，但是您试图从t1(NEW.pk1)访问列.因此，您将收到以下错误消息:

The second mistake is that you have defined the trigger on the wrong table (or you have mixed up the tables in the trigger). Your trigger is on t2, but you are trying to acces columns from t1 (NEW.pk1). So you would get the following error message:

Unknown column 'pk1' in 'NEW'

http://sqlfiddle.com/#!9/1515abb (取消注释在触发器中.)

http://sqlfiddle.com/#!9/1515abb (Uncomment the lines in the trigger.)

但是-即使解决了这个问题，逻辑看起来也不正确.您可能想要的工作版本可能是:

However - even fixing that, the logic doesn't look correct. A working version of what you might have intended could be:

CREATE TABLE IF NOT EXISTS `t1` (
  `pk1` INT NOT NULL,
  `pk2` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`pk1`, `pk2`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `t2` (
  `pk1` INT NOT NULL,
  `last_pk2` INT NOT NULL,
  PRIMARY KEY (`pk1`))
ENGINE = InnoDB;

DELIMITER //

CREATE TRIGGER `t1_BINS` BEFORE INSERT ON `t1` FOR EACH ROW
begin
  UPDATE t2 SET last_pk2=last_pk2 + 1 WHERE pk1=NEW.pk1;
  SET NEW.pk2=(SELECT last_pk2 FROM t2 WHERE pk1=NEW.pk1);
end//

DELIMITER ;

您可以在演示中看到，第二个插入未指定pk2，但结果会增加.

As you can see in the demo, the second insert doesn't specify pk2, but it is increased in the result.

但是，如果插入未在t2中注册的pk1值，则此操作将失败. (取消演示的最后一行的注释)，在这种情况下，必须在t2.由于pk1是t2中的主键，因此我们可以使用INSERT .. ON DUPLICATE KEY UPDATE ..语法:

This however will fail, if you insert a pk1 value that is not registered in t2. (Uncomment last line in the demo) In this case a new row has to be inserted into t2. Since pk1 is PRIMARY KEY in t2 we can use INSERT .. ON DUPLICATE KEY UPDATE .. syntax:

CREATE TRIGGER `t1_BINS` BEFORE INSERT ON `t1` FOR EACH ROW
begin
  INSERT INTO t2 (pk1, last_pk2) values (NEW.pk1, 1)
    ON DUPLICATE KEY UPDATE last_pk2 = last_pk2 + 1;
  SET NEW.pk2=(SELECT last_pk2 FROM t2 WHERE pk1=NEW.pk1);
end//

http://sqlfiddle.com/#!9/79eeea/1

现在最后一步是使其并发保存.我们必须防止两个并发线程/会话为t2.last_pk2读取相同的值.因此，写作和阅读应在一个陈述中完成.我们可以使用会话变量或LAST_INSERT_ID()来做到这一点:

Now last step is to make it concurrency save. We must prevent two concurrent threads/sessions to read the same value for t2.last_pk2. Thus writing and reading should be done in one statement. We can use a session variable or LAST_INSERT_ID() to do that:

CREATE TRIGGER `t1_BINS` BEFORE INSERT ON `t1` FOR EACH ROW
begin
  INSERT INTO t2 (pk1, last_pk2) values (NEW.pk1, @last_pk2 := 1)
    ON DUPLICATE KEY UPDATE last_pk2 = @last_pk2 := (last_pk2 + 1);
  SET NEW.pk2 = @last_pk2;
end//

http://sqlfiddle.com/#!9/dc235a/1

CREATE TRIGGER `t1_BINS` BEFORE INSERT ON `t1` FOR EACH ROW
begin
  INSERT INTO t2 (pk1, last_pk2) values (NEW.pk1, LAST_INSERT_ID(1))
    ON DUPLICATE KEY UPDATE last_pk2 = LAST_INSERT_ID(last_pk2 + 1);
  SET NEW.pk2 = LAST_INSERT_ID();
end//

http://sqlfiddle.com/#!9/86ed740/1

这篇关于触发到同一表上的UPDATE和SELECT-错误1235(42000)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！