MySQL 多个字段的唯一索引

Question

我们的数据库中有一种特殊的表，它本身存储其更改的历史记录。所谓的“自存档”表：

CREAT TABLE coverages (
   id INT, # primary key, auto-increment
   subscriber_id INT,
   current CHAR,  # - could be "C" or "H".
   record_version INT,
   # etc.
);

它存储我们订阅者的“覆盖范围”。字段“当前”指示这是当前/原始记录（“C”）还是历史记录（“H”）。

对于给定订阅者，我们只能有一个当前的“C”覆盖范围，但我们无法创建具有 2 个字段（*subscriber_id 和 current*）的唯一索引，因为对于任何给定的“C”记录，可能有任意数量的“H” “记录——变化的历史。

因此，索引仅对于 current == 'C' 和任何subscriber_id 来说应该是唯一的。

这可以在 Oracle DB 中使用“物化视图”之类的东西来完成：我们可以创建一个物化视图，该视图仅包含 current = 'C' 的记录，并使用这两个字段创建一个唯一索引： *订阅者 ID，当前*。

问题是：如何在 MySQL 中完成此操作？

Answer 1

您可以使用 NULL 值来执行此操作。如果使用NULL而不是“H”，MySQL 在评估 UNIQUE 约束时会忽略该行：

A UNIQUE index creates a constraint such that all values in the index must be
distinct. An error occurs if you try to add a new row with a key value that
matches an existing row. This constraint does not apply to NULL values except
for the BDB storage engine. For other engines, a UNIQUE index permits multiple
NULL values for columns that can contain NULL.

现在，这有点作弊，这意味着你无法拥有你的数据完全如您所愿。所以这个解决方案可能不适合您的需求。但是，如果您可以以这种方式重新处理数据，那么它应该可以工作。