我有一个如下所示的 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 s)，我宁愿避免某种尴尬的解决方案，在这种情况下，我在 created_at 上有一个触发器，但在 DEFAULT CURRENT_TIMESTAMP 上有一个 updated_at 。出于某种原因，MySQL 只允许每个表有一个自动时间戳，所以我不能同时为 CURRENT_TIMESTAMP 和 created_at 做 updated_at 。

这是 SHOW CREATE TABLE 的 officer :

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

最佳答案

如果 officer_number 已经存在，您的 INSERT ... ON DUPLICATE KEY UPDATE 似乎是一种防止错误的方法。您是否需要进行更新(触发 TRIGGER)，或者您是否可以改用 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_INCREMENT 列设置的第一个自动生成的值，以影响此类列。

但是，如果您提供一个参数，它会返回该参数的值，并且下一次调用 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 吗？当您排除它时究竟会发生什么？

manual指出:

However, if you mix references to LAST_INSERT_ID() and LAST_INSERT_ID(expr), the effect is undefined

和:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own.

当您同时使用 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 也会很有帮助 - 接收零值的语句。

关于mysql - 当有更新触发器时，重复键更新不起作用，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/4106743/