MySQL ON DUPLICATE KEY UPDATE，唯一键中的列可为空

Question

我们的 MySQL Web 分析数据库包含一个汇总表，该表会随着新活动的导入而全天更新。我们使用 ON DUPLICATE KEY UPDATE 以便汇总覆盖之前的计算，但遇到困难，因为汇总表的 UNIQUE KEY 中的列之一是可选的 FK，并且包含 NULL 值。

这些 NULL 旨在表示“不存在，并且所有此类情况都是等效的”。当然，MySQL 通常将 NULL 视为“未知，并且所有此类情况都不等效”。

基本结构如下：

“活动”表，其中包含每个会话的一个条目，每个会话都属于一个活动，并且某些条目具有可选的过滤器和事务 ID。

CREATE TABLE `Activity` (
    `session_id` INTEGER AUTO_INCREMENT
    , `campaign_id` INTEGER NOT NULL
    , `filter_id` INTEGER DEFAULT NULL
    , `transaction_id` INTEGER DEFAULT NULL
    , PRIMARY KEY (`session_id`)
);

“摘要”表，其中包含活动表中会话总数的每日汇总以及包含事务 ID 的会话总数。这些摘要是分开的，每一个活动和（可选）过滤器的组合都有一个摘要。这是一个使用 MyISAM 的非事务表。

CREATE TABLE `Summary` (
    `day` DATE NOT NULL
    , `campaign_id` INTEGER NOT NULL
    , `filter_id` INTEGER DEFAULT NULL
    , `sessions` INTEGER UNSIGNED DEFAULT NULL
    , `transactions` INTEGER UNSIGNED DEFAULT NULL
    , UNIQUE KEY (`day`, `campaign_id`, `filter_id`)
) ENGINE=MyISAM;

实际的汇总查询如下所示，计算会话和交易的数量，然后按活动和（可选）过滤器进行分组。

INSERT INTO `Summary` 
    (`day`, `campaign_id`, `filter_id`, `sessions`, `transactions`)
    SELECT `day`, `campaign_id`, `filter_id
        , COUNT(`session_id`) AS `sessions`
        , COUNT(`transaction_id` IS NOT NULL) AS `transactions`
    FROM Activity
    GROUP BY `day`, `campaign_id`, `filter_id`
ON DUPLICATE KEY UPDATE
    `sessions` = VALUES(`sessions`)
    , `transactions` = VALUES(`transactions`)
;

除了 filter_id 为 NULL 的情况摘要之外，一切都运行良好。在这些情况下，ON DUPLICATE KEY UPDATE 子句与现有行不匹配，并且每次都会写入新行。这是因为“NULL != NULL”。然而，在比较唯一键时，我们需要的是“NULL = NULL”。

我正在寻找解决方法的想法或对我们迄今为止提出的想法的反馈。到目前为止我们想到的解决方法如下。

1. 在运行摘要之前删除所有包含 NULL 键值的摘要条目。 （这就是我们现在正在做的事情） 如果在汇总过程中执行查询，这会产生负面影响，即返回缺少数据的结果。

2. 将 DEFAULT NULL 列更改为 DEFAULT 0，这样可以使 UNIQUE KEY 一致匹配。 这具有负面影响，即使针对汇总表的查询的开发过于复杂。它迫使我们使用大量“CASE filter_id = 0 THEN NULL ELSE filter_id END”，并且由于所有其他表的 filter_id 都有实际的 NULL，因此会导致连接不便。

3. 创建一个返回“CASE filter_id = 0 THEN NULL ELSE filter_id END”的视图，并使用该视图而不是直接使用表。 摘要表包含几十万行，我被告知视图性能非常差。

4. 允许创建重复条目，并在汇总完成后删除旧条目。 与提前删除它们有类似的问题。

5. 添加一个包含 0 代表 NULL 的代理列，并在 UNIQUE KEY 中使用该代理（实际上，如果所有列都不为 NULL，我们可以使用 PRIMARY KEY）。
   这个解决方案看起来很合理，只不过上面的例子只是一个例子；实际数据库包含六个汇总表，其中一个在 UNIQUE KEY 中包含四个可为空的列。有些人担心开销太大。

您是否有更好的解决方法、表结构、更新过程或 MySQL 最佳实践可以提供帮助？

编辑：为了澄清“null 的含义”，

包含 NULL 列的汇总行中的数据仅在汇总报告中作为单个“包罗万象”行的意义上才被视为属于一起，汇总了该数据的那些项目点不存在或未知。因此，在汇总表本身的上下文中，含义是“未知值的那些条目的总和”。另一方面，在关系表中，这些确实是 NULL 结果。

将它们放入汇总表上的唯一键中的唯一原因是允许在重新计算汇总报告时自动更新（通过重复键更新）。

也许更好的描述方法是通过具体示例，其中一个汇总表按受访者给出的公司地址的邮政编码前缀按地理位置对结果进行分组。并非所有受访者都提供营业地址，因此交易和地址表之间的关系完全正确为 NULL。在此数据的汇总表中，为每个邮政编码前缀生成一行，包含该区域内数据的汇总。生成附加行以显示没有已知邮政编码前缀的数据摘要。

将其余数据表更改为具有显式“THERE_IS_NO_ZIP_CODE”0 值，并在 ZipCodePrefix 表中放置表示该值的特殊记录是不正确的 - 该关系确实为 NULL。

Answer 1

我认为类似（2）的东西确实是最好的选择——或者，至少，如果你从头开始的话，它会是最好的选择。在SQL中，NULL表示未知。如果你想要一些其他的含义，你真的应该使用一个特殊的值，而 0 肯定是一个不错的选择。

您应该在整个数据库中执行此操作，而不仅仅是这个表。那么你就不应该遇到奇怪的特殊情况。事实上，您应该能够删除当前的许多行（例如：当前，如果您想要没有过滤器的摘要行，则有特殊情况“过滤器为空”，而不是正常情况“filter = ?”。）

您还应该继续在引用的表中创建一个“不存在”条目，以保持 FK 约束有效（并避免特殊情况）。

PS：没有主键的表不是关系表，应该避免。

编辑 1

嗯，在这种情况下，您实际上需要重复密钥更新吗？如果您正在执行 INSERT ... SELECT，那么您可能会这样做。但是，如果您的应用程序提供数据，则只需手动执行即可 - 执行更新（将 zip = null 映射到 zip is null），检查更改了多少行（ MySQL 返回此），如果 0 则执行插入。

Answer 2

使用现代版本的 MariaDB（以前的 MySQL），如果您使用代理列路由#5，则可以通过重复键更新语句上的插入来简单地完成更新插入。添加 MySQL 生成的存储列或 MariaDB 持久虚拟列以对可为空字段应用唯一性约束，间接将无意义数据排除在数据库之外，以换取一些膨胀。

例如，

CREATE TABLE IF NOT EXISTS bar (
    id INT PRIMARY KEY AUTO_INCREMENT,
    datebin DATE NOT NULL,
    baz1_id INT DEFAULT NULL,
    vbaz1_id INT AS (COALESCE(baz1_id, -1)) STORED,
    baz2_id INT DEFAULT NULL,
    vbaz2_id INT AS (COALESCE(baz2_id, -1)) STORED,
    blam DOUBLE NOT NULL,
    UNIQUE(datebin, vbaz1_id, vbaz2_id)
);

INSERT INTO bar (datebin, baz1_id, baz2_id, blam)
    VALUES ('2016-06-01', null, null, 777)
ON DUPLICATE KEY UPDATE
    blam = VALUES(blam);

对于 MariaDB，将 STORED 替换为 PERSISTENT，索引需要持久性。

MySQL 生成的列
MariaDB 虚拟列

Answer 3

将 DEFAULT NULL 列更改为 DEFAULT 0，这样可以使 UNIQUE KEY 一致匹配。这具有负面影响，即使针对汇总表的查询的开发过于复杂。它迫使我们使用大量的“CASE filter_id = 0 THEN NULL ELSE filter_id END”，并且由于所有其他表的 filter_id 都具有实际的 NULL，因此导致了尴尬的连接。

创建一个返回“CASE filter_id = 0 THEN NULL ELSE filter_id END”的视图，并使用该视图而不是直接使用表。摘要表包含几十万行，并且我被告知视图性能非常差。

MySQL 5.x 中的视图性能会很好，因为视图除了用空值替换零之外什么也不做。除非您在视图中使用聚合/排序，否则针对视图的大多数查询都将由查询优化器重写以仅命中基础表。

当然，由于它是 FK，因此您必须在引用的表中创建一个 id 为零的条目。

Answer 4

我迟到了十多年，但我觉得我的解决方案应该是这里的答案，因为我遇到了完全相同的问题，并且这对我有用。如果您知道需要更新哪些内容，则可以在现有汇总查询之前手动更新它们，然后忽略现有查询中 filter_id 为 null 的所有情况，这样它就不会再次作为记录插入。

对于你的例子：

UPDATE `Summary` s
    LEFT JOIN `Activity` a
    ON s.`campaign_id` = a.`campaign_id`
SET s.`sessions`     = a.COUNT(`session_id`)                ,
SET s.`transactions` = a.COUNT(`transaction_id` IS NOT NULL)
WHERE s.`day`         = a.`day`
AND   s.`campaign_id` = a.`campaign_id`
AND   s.`filter_id` IS NULL
AND   a.`filter_id` IS NULL;

INSERT INTO `Summary` 
    (`day`, `campaign_id`, `filter_id`, `sessions`, `transactions`)
    SELECT `day`, `campaign_id`, `filter_id`
        , COUNT(`session_id`) AS `sessions`
        , COUNT(`transaction_id` IS NOT NULL) AS `transactions`
    FROM Activity
    WHERE `filter_id` IS NOT NULL
    GROUP BY `day`, `campaign_id`, `filter_id`
ON DUPLICATE KEY UPDATE
    `sessions` = VALUES(`sessions`)
    , `transactions` = VALUES(`transactions`);