当存在 UPDATE 触发器时，ON DUPLICATE KEY UPDATE 不起作用

Question

我有一个 INSERT 语句，如下所示：

INSERT INTO officer (officer_number,
                     name,
                     bank_id)    
VALUES ('',
        '',
        8)

ON DUPLICATE KEY UPDATE officer_number = '',
                        name = '',
                        bank_id = 8,
                        id = LAST_INSERT_ID(id)

这种方法运行得很好。当我添加以下触发器时，它停止工作：

CREATE TRIGGER officer_update BEFORE UPDATE ON `officer`
FOR EACH ROW SET NEW.updated_at = NOW(), NEW.created_at = OLD.created_at

这并不是没有插入 officer 记录。看起来触发器正在劫持 LAST_INSERT_ID() 之类的东西。我这样说是因为执行的下一个查询是这样的：

INSERT INTO account (import_id,
                     branch_id,
                     account_number,
                     officer_id,
                     customer_id,
                     open_date,
                     maturity_date,
                     interest_rate,
                     balance,
                     opening_balance)
VALUES ('123',
        '4567',
        '789',
        '0', # This is the officer id which is of course invalid
        '321',
        '1992-04-22',
        '2012-05-22',
        '0.0123',
        '0',
        '10000')

由于我已经使用同一个文件运行了数十次成功的导入，所以我没有更改我的代码，现在我的导入在添加此触发器后不起作用，我必须推断触发器是罪魁祸首。我在另一张表上遇到了类似的情况，删除触发器解决了问题。

所以我的问题是：

1. 有人可以解释一下吗， 具体来说，导致我的军官 id 设置为 0？
2. 什么是好的 这个问题的解决方案？

我在 officer.created_at （以及许多其他表的 created_at）上有另一个触发器，我宁愿避免某种排序一个尴尬的解决方案，我在 created_at 上有一个触发器，但在 updated_at 上有一个 DEFAULT CURRENT_TIMESTAMP 。由于某种原因，MySQL 只允许每个表使用一个自动时间戳，因此我无法同时对 created_at 和 updated_at 执行 CURRENT_TIMESTAMP。

这是officer的SHOW CREATE TABLE：

CREATE TABLE `officer` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `officer_number` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `bank_id` bigint(20) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `officer_number` (`officer_number`,`name`),
  UNIQUE KEY `officer_number_2` (`officer_number`,`bank_id`),
  KEY `bank_id` (`bank_id`),
  CONSTRAINT `officer_ibfk_1` FOREIGN KEY (`bank_id`) REFERENCES `bank` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=102735 DEFAULT CHARSET=latin1

Answer 1

如果 officer_number 已存在，您的 INSERT ... ON DUPLICATE KEY UPDATE 似乎是防止错误的一种方法。您是否需要进行更新（以触发触发器），还是可以使用 INSERT IGNORE？：

INSERT IGNORE INTO officer (officer_number,
                     name,
                     bank_id)    
VALUES ('',
        '',
        8);

如果 officer_id 已经存在，则不会执行任何操作，从而删除完全需要更新（因此LAST_INSERT_ID()）。

如果这是不可能的，那么也许可以调整您的INSERT ... ON DUPLICATE KEY UPDATE。我不清楚以下目的：

id = LAST_INSERT_ID(id)

LAST_INSERT_ID()（不带任何参数），返回由最近执行的 INSERT 语句为 AUTO_INCRMENT 列设置的第一个自动生成的值，以影响这样的柱子。

但是，如果您提供参数，它将返回该参数的值，并且下一次调用 LAST_INSERT_ID() （不带任何参数）将返回相同的值。例如：

SELECT LAST_INSERT_ID(100);
+---------------------+
| LAST_INSERT_ID(100) |
+---------------------+
|                 100 |
+---------------------+

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|              100 |
+------------------+

所以，如果我们假设 id == 100，那么这应该是真的：

SELECT LAST_INSERT_ID(id);
+--------------------+
| LAST_INSERT_ID(id) |
+--------------------+
|                100 |
+--------------------+

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|              100 |
+------------------+

接下来：

id = LAST_INSERT_ID(id)

应该是相同的：

id = id

或者，正如 Josh Davis 所建议的，它不应该完全有必要。您是否尝试过简单地id = id？当你排除它时到底会发生什么？

手册指出：

但是，如果您混合引用
LAST_INSERT_ID() 和
LAST_INSERT_ID(expr)，效果是
未定义

并且：

生成的ID是
维护在服务器上
每个连接基础。这意味着
函数返回的值
给定的客户是第一个
生成的 AUTO_INCREMENT 值
最近的声明影响
该客户端的 AUTO_INCRMENT 列。
该值不受其他值影响
客户，即使他们产生
自己的 AUTO_INCRMENT 值。

由于您同时使用 LAST_INSERT_ID() 和 LAST_INSERT_ID(expr)，因此行为未定义。此外，TRIGGER 可以被视为一个连接（它直接在服务器上运行），而 INSERT 和 CREATE 语句可能是从不同的连接调用的。鉴于此，以及已报告的与版本之间的 LAST_INSERT_ID 相关的各种更改和错误，您的方法可能会出现问题。

回到 Josh Davis 所说的，我倾向于解决 INSERT 语句中 id = LAST_INSERT_ID(id) 的使用问题。了解如何在 INSERT INTO account 语句中导出 officer_id（接收零值的语句）也会很有帮助。

Answer 2

我不明白您为什么尝试更新 id。您不能删除对 LAST_INSERT_ID() 的调用吗？

INSERT INTO officer (officer_number,
                     name,
                     bank_id)
VALUES ('',
        '',
        8)

ON DUPLICATE KEY UPDATE officer_number = '',
                        name = '',
                        bank_id = 8

另外，您应该发布您的 MySQL 版本（以及所使用的引擎），因为过去的 LAST_INSERT_ID() 行为发生了一些变化。