如果 PRIMARY KEY 被 FOREIGN KEY 引用，则 MySQL REPLACE 的行为与 UPDATE 不同

Question

我有两个表:

CREATE TABLE `category` (
  `category_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` mediumint(8) unsigned NOT NULL,
  `name` varchar(20) CHARACTER SET ascii NOT NULL,
  `description` varchar(100) DEFAULT NULL,
  `repeat_interval` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `color` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`category_id`),
  KEY `id` (`user_id`),
  CONSTRAINT `category_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON  DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `event` (
  `event_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int(10) unsigned NOT NULL,
  `name` varchar(20) CHARACTER SET ascii NOT NULL,
  `description` varchar(100) DEFAULT NULL,
  `repeat_interval` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `color` mediumint(8) unsigned NOT NULL,
  `priority` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `start` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `end` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `done` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`event_id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `event_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `category`     (`category_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如果我在 category 表(一行)中创建一个 REPLACE，那么 event 表中的所有条目都引用修改后的行category 表将被丢弃。

但是如果我UPDATE category 表中的一行，那么event 表中的条目将保持不变。

为什么会出现这种行为，为什么当我 REPLACE something 时，所有引用该列的条目都被丢弃了？

我尝试了 ON UPDATE CASCADE 和默认的相同行为。

Google 帮不了我。

Answer 1

您有 ON DELETE CASCADE 外键，替换只是“删除然后插入新版本”- 似乎 ON DELETE 触发器被触发。

来自 Mysql 文档:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 12.2.5, “INSERT Syntax”.

参见 http://dev.mysql.com/doc/refman/5.0/en/replace.html

要解决此问题，您可能需要使用 ON DUPLICATE KEY UPDATE 插入语法:http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html