mysql - 我可以控制在 UPDATE 中使用哪个 JOINed 行吗？

Question

曾几何时，我有一张这样的 table :

CREATE TABLE `Events` (
  `EvtId`   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `AlarmId` INT UNSIGNED,
  -- Other fields omitted for brevity

  PRIMARY KEY (`EvtId`)
);

AlarmId 被允许为 NULL。

现在，因为我想从每个事件的零个或一个警报扩展到每个事件的零个或多个警报，所以在软件更新中，我正在更改我的数据库实例以改为拥有这个:

CREATE TABLE `Events` (
  `EvtId`   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  -- Other fields omitted for brevity

  PRIMARY KEY (`EvtId`)
);

CREATE TABLE `EventAlarms` (
  `EvtId`   INT UNSIGNED NOT NULL,
  `AlarmId` INT UNSIGNED NOT NULL,

  PRIMARY KEY (`EvtId`, `AlarmId`),
  CONSTRAINT `fk_evt` FOREIGN KEY (`EvtId`) REFERENCES `Events` (`EvtId`)
     ON DELETE CASCADE ON UPDATE CASCADE 
);

到目前为止一切顺利。

数据也很容易迁移:

INSERT INTO `EventAlarms`
  SELECT `EvtId`, `AlarmId` FROM `Events` WHERE `AlarmId` IS NOT NULL;

ALTER TABLE `Events` DROP COLUMN `AlarmId`;

---

问题是，我的系统要求降级也是可能的。我承认降级有时会在数据方面造成损失，这没关系。但是，它们确实需要在可能的情况下工作，并导致旧的数据库结构，同时尽最大努力尽可能多地保留原始数据。

在这种情况下，这意味着从每个事件零个或多个警报变为每个事件零个或一个警报。我可以这样做:

ALTER TABLE `Events` ADD COLUMN `AlarmId` INT UNSIGNED;

UPDATE `Events`
  LEFT JOIN `EventAlarms` USING(`EvtId`)
  SET `Events`.`AlarmId` = `EventAlarms`.`AlarmId`;

DROP TABLE `EventAlarms`;

... 这很好，因为我真的不在乎保留哪一个(这是尽力而为，记住)。但是，如警告的那样，这不利于复制，因为结果可能无法预测:

> SHOW WARNINGS;
Unsafe statement written to the binary log using statement format since
BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-
increment column after selecting from another table are unsafe because the
order in which rows are retrieved determines what (if any) rows will be
written. This order cannot be predicted and may differ on master and the
slave.

有没有办法以某种方式“命令”或“限制”更新中的加入，或者我应该跳过整个企业并停止尝试变得聪明？如果是后者，我如何才能将降级后的 AlarmId 保留为 NULL iff 新表中有多行我们无法安全地区分？如果只有一个，我确实想迁移 AlarmId。

由于降级是“一次性”维护操作，因此不必完全实时，但速度会很好。两个表都可能有数千行。

(CentOS 7 上的 MariaDB 5.5.56，但也必须适用于 CentOS 6 附带的任何产品。)

Answer 1

首先，我们可以使用自连接执行一些分析:

SELECT `A`.`EvtId`, COUNT(`B`.`EvtId`) AS `N`
FROM `EventAlarms` AS `A`
LEFT JOIN `EventAlarms` AS `B` ON (`A`.`EvtId` = `B`.`EvtId`)
GROUP BY `B`.`EvtId`

结果看起来像这样:

EvtId       N
--------------
370         1
371         1
372         4
379         1
380         1
382        16
383         1
384         1

现在，如果您愿意，您可以删除所有表示映射到多个警报的事件的行(您建议将其作为后备解决方案；我认为这是有道理的，尽管您可以修改下面的内容以保留其中一个如果你真的想要，就到位)。

不过，与其实际DELETEing 任何东西，不如引入一个新表，使用上面显示的自连接查询填充:

CREATE TEMPORARY TABLE `_migrate` (
   `EvtId` INT UNSIGNED,
   `n` INT UNSIGNED,

   PRIMARY KEY (`EvtId`),
   KEY `idx_n` (`n`)
);

INSERT INTO `_migrate`
   SELECT `A`.`EvtId`, COUNT(`B`.`EvtId`) AS `n`
   FROM `EventAlarms` AS `A`
   LEFT JOIN `EventAlarms` AS `B` ON(`A`.`EvtId` = `B`.`EvtId`)
   GROUP BY `B`.`EvtId`;

然后你的更新变成:

UPDATE `Events`
   LEFT JOIN `_migrate` ON (`Events`.`EvtId` = `_migrate`.`EvtId` AND `_migrate`.`n` = 1)
   LEFT JOIN `EventAlarms` ON (`_migrate`.`EvtId` = `EventAlarms`.`EvtId`)
   SET `Events`.`AlarmId` = `EventAlarms`.`AlarmId`
   WHERE `EventAlarms`.`AlarmId` IS NOT NULL

最后，清理一下自己:

DROP TABLE `_migrate`;
DROP TABLE `EventAlarms`;

MySQL 仍然会发出与之前相同的警告，但由于知道最多会从源表中提取一个值，所以我们基本上可以忽略它。

它甚至应该相当高效，正如我们可以从等效的 EXPLAIN SELECT 中看出的那样:

EXPLAIN SELECT `Events`.`EvtId` FROM `Events`
   LEFT JOIN `_migrate` ON (`Events`.`EvtId` = `_migrate`.`EvtId` AND `_migrate`.`n` = 1)
   LEFT JOIN `EventAlarms` ON (`_migrate`.`EvtId` = `EventAlarms`.`EvtId`)
   WHERE `EventAlarms`.`AlarmId` IS NOT NULL

id  select_type table       type   possible_keys      key     key_len ref               rows Extra
---------------------------------------------------------------------------------------------------------------------
1   SIMPLE      _migrate    ref    PRIMARY,idx_n      idx_n   5       const             6    Using index
1   SIMPLE      EventAlarms ref    PRIMARY,fk_AlarmId PRIMARY 8       db._migrate.EvtId 1    Using where; Using index
1   SIMPLE      Events      eq_ref PRIMARY            PRIMARY 8       db._migrate.EvtId 1    Using where; Using index