代码之家 › 专栏 › 技术社区 › Jason Swett

当存在更新触发器时,重复密钥更新不起作用

lastinsertid triggers mysql

Jason Swett · 技术社区 · 14 年前

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')

所以我的问题是:

有人能解释一下吗, 具体来说,是因为要设置为0的id?
这个问题的解决办法?

我又开了一个扳机 officer.created_at created_at s) 我宁愿避免一种尴尬的解决方案但是 DEFAULT CURRENT_TIMESTAMP 在 updated_at . 由于某些原因,MySQL只允许每个表有一个自动时间戳,所以我不能这样做 CURRENT_TIMESTAMP 创建于 和 .

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

2 回复 | 直到 14 年前

Mike 14 年前

你的 INSERT ... ON DUPLICATE KEY UPDATE officer_number 已经存在。是否需要更新(触发触发器),或者可以使用 INSERT IGNORE

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

如果 officer_id 已经存在,因此不需要更新(因此) LAST_INSERT_ID()

如果那是不可能的,那么也许你的可以调整。我不清楚是为了:

id = LAST_INSERT_ID(id)

上次插入的ID() (不带任何参数),返回最近执行的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=上次插入的id(id)

应与:

id = id

或者,正如Josh Davis所建议的那样,这根本就没有必要。你试过简单的 id = id

这个 manual

但是,如果您混合引用上次插入的ID()和最后一个插入ID(expr),效果是未定义

以及:

生成的ID是 每个连接 基础。这意味着给定的客户是第一个为生成的自动增量值影响 由那个客户 . 客户,即使他们产生自动递增它们自己的值。

因为你两个都用和 LAST_INSERT_ID(expr) ,行为未定义。此外,触发器可以被看作是一个连接(它直接在服务器上运行),而INSERT和CREATE语句可能是从不同的连接调用的。考虑到这一点,以及已经报告的与版本之间的上次插入ID相关的各种更改和错误,很可能存在将对你的方法有问题。

id = LAST_INSERT_ID(id) 在你的插入声明中。了解您是如何获得 警官身份证 在你的 INSERT INTO account

Josh Davis 14 年前

我不明白你为什么要更新 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版本(以及使用的引擎),因为过去的行为。