mysql - MariaDB 10.0，innodb : Conditional Unique Constraint?

Question

我的模式，简化了:

人们:id (int), name (string), allow_duplicate_name (int)

allow_duplicate_name 为 1 表示真，0 表示假。

如果名称字段不唯一，我想阻止插入新记录，但前提是它们具有 allow_duplicate_name == 1。因此，如果我们有下表:

id  name  allow_duplicate_name
1   jack  1
2   jack  1
3   ryan  1
4   jack  1

那么下面的语句应该会成功:

INSERT INTO people (name, allow_duplicate_name)
VALUES ('jack', 1);

但是这个语句应该失败:

INSERT INTO people (name, allow_duplicate_name)
VALUES ('jack', 0);

我的根本问题是我有一个竞争条件，其中两个并发事务都检查是否存在重复项，如果不存在，然后插入一个具有相同名称且 allow_duplicate_name=0 的新记录。

我的另一个想法是在提交每个事务之前检查是否添加了重复项时使用 READ UNCOMMITTED，然后将其回滚，让应用程序采取不同的操作过程(例如，选择一个新名称)，因为我们可以容忍(这远不理想，但我们可以处理它......)每个事务看到另一个事务然后自己回滚的竞争条件，但我们绝对不能允许设置重复的插入/更新allow_duplicate_name=0 的名称。

我听说我们的设置使得存储过程使用起来非常痛苦，但也许存储过程是解决这个问题的唯一明智的方法？

Answer 1

你几乎可以做你想做的事。但您需要更改变量的名称。关键思想是唯一索引允许复制 NULL 值。因此，当“allow_duplicate_name”为 NULL 时，您可以获得重复项。

我建议将该字段重命名为类似 DuplicatesNotAllowed 的名称。然后创建索引:

 create unique index idx_people_name_duplicatesnotallowed on people(name, duplciatesnotallowed);

您可以将值插入为:

INSERT INTO people (name, duplciatesnotallowed)
    VALUES ('jack', NULL);

这会成功。

INSERT INTO people (name, allow_duplicate_name)
    VALUES ('jack', 1);

这将在第二次 运行时失败。但是，如果您更新现有行的字段，那么这将失败。我不确定这是否满足您的需求。