MySQL多个字段的唯一索引 [英] MySQL unique index by multiple fields

问题描述

我们的数据库中有一种特殊的表，该表本身存储其更改历史记录.所谓的自存档"表:

We have a special kind of table in our DB that stores the history of its changes in itself. So called "self-archived" table:

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").

It stores "coverages" of our subscribers. Field "current" indicates if this is a current/original record ("C") or history record ("H").

对于给定的订户，我们只能拥有一个当前的"C"覆盖率，但是我们无法创建具有2个字段(* subscriber_id和current *)的唯一索引，因为对于任何给定的"C"记录，都可以有任何数字的"H"记录-更改历史记录.

We could only have one current "C" coverage for the given subscriber, but we can't create a unique index with 2 fields (*subscriber_id and current*) because for any given "C" record there could be any number of "H" records - history of changes.

因此，该索引应仅对 current =='C'和任何subscriber_id唯一.

So the index should only be unique for current == 'C' and any subscriber_id.

这可以在Oracle DB中使用物化视图"之类的方法来完成:在这里我们可以创建物化视图，该视图仅包含具有 current ='C'的记录，并使用这些创建唯一索引2个字段:* subscriber_id，当前*.

That could be done in Oracle DB using something like "materialized views": where we could create a materialized view that would only include records with current = 'C' and create a unique index with these 2 fields: *subscriber_id, current*.

问题是:如何在MySQL中完成此操作?

推荐答案

您可以使用NULL值执行此操作.如果使用NULL而不是"H"，则 MySQL在评估UNIQUE约束时将忽略该行:

You can do this using NULL values. If you use NULL instead of "H", MySQL will ignore the row when evaluating the UNIQUE constraint:

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.

现在，这有点作弊，这意味着您不能完全按照自己的意愿拥有数据.因此，此解决方案可能无法满足您的需求.但是，如果您可以以这种方式重做数据，那么它应该可以工作.

Now, this is cheating a bit, and it means that you can't have your data exactly as you want it. So this solution may not fit your needs. But if you can rework your data in this way, it should work.

这篇关于MySQL多个字段的唯一索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！