mysql - 表的特定字段值的唯一约束

Question

我有一张发票表。它有很多领域，但问题是围绕2个主要领域

• InvoiceNo - 字母数字值
• Deleted - boolean 1 或 0 ，代表一条记录是否被删除。

我们的业务要求 InvoiceNo 是唯一的。但是，如果一行被删除，我们可以重新使用 InvoiceNo

InvoiceNo    Deleted
123Er        1
123Er        0

以上是一个有效的用例。但我不想再有 123Er & 0 的记录。

是否可以为某些值 Unique (InvoiceNo, Deleted=0) 在 2 个字段的组合上创建唯一键，还是我们应该使用存储过程或触发器？

Answer 1

借助 function based index 在其他 RDBMS 系统中很容易实现

目前 MySql 没有这样的功能，但是从 5.7 版本开始可以使用虚拟(或生成)列来模拟它。

简单的工作示例:http://rextester.com/HGY68688

CREATE TABLE IF NOT EXISTS mytable1234(
  InvoiceNo varchar(10),
  Deleted int,
  xxx varchar(10) generated always as (case when deleted = 0 then InvoiceNo end) virtual
);

create unique index myindex12 on mytable1234( xxx );

INSERT INTO mytable1234( InvoiceNo, Deleted)  VALUES ('aaa1', 0 );
INSERT INTO mytable1234( InvoiceNo, Deleted)  VALUES ('aaa1', 1 );
INSERT INTO mytable1234( InvoiceNo, Deleted)  VALUES ('aaa1', 1 );

-- INSERT INTO mytable1234( InvoiceNo, Deleted)  VALUES ('aaa1', 0 );

如果您取消注释此代码段中的最后一个 INSERT，然后尝试运行此代码段，您将得到:Duplicate entry 'aaa1' for key 'myindex12' 错误。
这样一来，表中可能会有多条InvoiceNo值为deleted = 1的记录，而deleted = 0只有一条记录>，因为 MySql 不允许这样做。