mysql不允许唯一索引为NULL - mysql not to allow NULL on unique index

我用mysql创建了一个表。表的列是sequence, email, date_created和date_canceled。primary是序列，我试图为email和date_canceled创建一个唯一的索引——所以当email处于活动状态时(从未被取消过——意味着date_cancelled为NULL)，就不会有另一个活动的email被插入或出现这种情况。

我知道它可以用oracle db完成，但mysql的唯一索引允许NULL。

有什么建议吗?谢谢!

我对你的问题的理解是，你希望每个用户在任何时候都有一个与他们相关联的活动电子邮件，同时保持用户以前邮件的历史记录。

解决方案1

从MySQL 5.7.5开始，你可以创建一个生成的列，然后在上面设置一个唯一的约束。http://mysqlserverteam.com/generated-columns-in-mysql-5-7-5/。例如

create table UniqueActiveEmailDemo 
(
    sequence bigint not null auto_increment primary key
  , userId bigint not null
  , email nvarchar(1024) not null
  , date_created timestamp default now()
  , date_cancelled datetime
  , single_active_mail_per_user bigint as (if(date_cancelled is null, userId, null))
);
alter table UniqueActiveEmailDemo 
add unique UK_UniqueActiveEmailDemo_SingleActiveMailPerUser 
(single_active_mail_per_user);

因为MySQL允许在一个唯一的约束中有多个空，当记录被取消时，生成列有一个空值;你想要多少记录就有多少记录。但是，如果记录没有被取消，生成的列返回用户的id;这受制于唯一约束，所以如果有另一个具有相同用户id的活动记录，唯一约束将抛出异常。

解决方案2

Sql Fiddle: http://sqlfiddle.com/#!9/b54dce/2

然而，一个更干净的方法，也适用于早期版本，将简单地防止date_cancelled为空;而不是将它设置为一个固定的值，在遥远的将来，任何项目尚未取消，然后有user_id和date_cancelled的组合为空;例如

create table UniqueActiveEmailDemo 
(
  sequence bigint not null auto_increment primary key
  , userId bigint not null
  , email nvarchar(1024) not null
  , date_created timestamp default now()
  , date_cancelled datetime not null default '9999-12-31 23:59:59'
);
ALTER TABLE UniqueActiveEmailDemo 
ADD UNIQUE UK_UniqueActiveEmailDemo_SingleActiveMailPerUser 
(userId, date_cancelled);

这里的一个区别是你不能在同一天取消同一用户的两个记录;但在现实中，我认为你永远不会得到它。
这也意味着你可以有记录，在未来得到。为了避免这个问题，你可以将9999-12-31 23:59:59视为空值;也就是说，任何具有该值的记录都是活动的。
您还可以通过在where now() between date_active and date_cancelled上添加active_from日期和过滤器来解决上述问题;然而，你需要添加更多的检查，以确保同一用户的活动Windows不会重叠;
添加检查约束将防止将来的值;但遗憾的是，它们目前没有在MySQL中使用(尽管是有效的语句)。http://dev.mysql.com/doc/refman/5.7/en/create-table.html

, date_cancelled datetime not null default '9999-12-31 23:59:59' check (date_cancelled = '9999-12-31 23:59:59' or date_cancelled <= now())

BDB存储引擎将NULL视为唯一值，只允许一个NULL值。

From the docs:

如果允许NULL值的列有唯一索引，则只有一个允许设置NULL的值。这与其他存储引擎不同，允许多个NULL值在唯一索引中。

如果你不想改变你的存储引擎来获得这种行为，你的其他选择是改变你的表结构，在不同的表中存储活动的电子邮件和取消的电子邮件…创建一个触发器来强制执行此行为…或者为active电子邮件指定一个神奇的日期值，并且不再允许NULL值在此列中。