根据 文档、

对于可以包含 NULL 的列，UNIQUE 索引允许多个 NULL 值.

A UNIQUE index permits multiple NULL values for columns that can contain NULL.

因此从 Status 中删除 not null-约束并在 (ContactId,PhoneId,Status) 上添加唯一索引将像您一样工作想要它，如果您使用 null 而不是 0 来表示 inactive 记录.

So removing the not null-constraint from Status and adding a unique index on (ContactId,PhoneId,Status) will work as you want it to, if you use null instead of 0 for inactive records.

如果您不想或不能为您的 Status 列使用 null，请确保 Status=0 和 Status=null 行为相同，或例如也想将 Status=2 视为 active(并强制执行唯一性)，您可以添加一个将根据 Status 计算的虚拟列.

If you don't want to or cannot use null for your Status column, want to make sure both Status=0 and Status=null behave identically, or e.g. want to treat Status=2 as active (and enforcing uniqueness) too, you can add a dummy column that will be calculated from Status.

如果您使用的是 MySQL 5.7+，则可以使用生成的列执行此操作:

If you are using MySQL 5.7+, you can do this with a generated column:

CREATE TABLE IF NOT EXISTS `ContactPhone` (
  `ContactPhoneId` int(10) unsigned NOT NULL auto_increment primary key,
  `ContactId` int(11) NOT NULL,
  `PhoneId` smallint(5) unsigned NOT NULL,
  `Status` tinyint(1) NOT NULL DEFAULT '1',
  `StatusUnq` tinyint(1) as (if(Status <> 0, 1, null)) stored null,
  constraint unique (ContactId, PhoneId, StatusUnq)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

insert into ContactPhone (ContactPhoneId, ContactId, PhoneId, Status)
values (1, 1, 1, 1);
insert into ContactPhone (ContactPhoneId, ContactId, PhoneId, Status)
values (2, 1, 1, 1);
-- Duplicate key error 
insert into ContactPhone (ContactPhoneId, ContactId, PhoneId, Status)
values (3, 1, 1, 0);
insert into ContactPhone (ContactPhoneId, ContactId, PhoneId, Status)
values (4, 1, 1, 0);
update ContactPhone set Status = 1 where ContactPhoneId = 4;
-- Duplicate key error 

否则，您可以使用普通列并使用触发器来计算列的值，例如:

Otherwise, you can use a normal column and use triggers to calculate the value of the column, e.g.:

create trigger trbi_contactPhoneUnique before insert on ContactPhone 
for each row
  set new.StatusUnq = if(new.Status <> 0, 1, null);

create trigger trbu_contactPhoneUnique before update on ContactPhone 
for each row
  set new.StatusUnq = if(new.Status <> 0, 1, null);

您当然可以将公式切换为例如if(new.Status <> 0, new.Status, null); 如果您也想允许 Status 的不同值.

You can of course switch the formula to e.g. if(new.Status <> 0, new.Status, null); if you want to allow different values of Status too.