如何将空字符串设置为空值以允许在可选字段中重复空字符串？

I have an email field that's optional. The regex works fine accepting the empty string (^$) but now the issue is that the empty string is considered a unique entry. It will only allow one user to register without entering an email address. I know I have to set it to null, but not sure how.

我有一个可选的电子邮件字段。正则表达式很好地接受空字符串（^ $），但现在的问题是空字符串被认为是唯一的条目。它只允许一个用户在不输入电子邮件地址的情况下进行注册。我知道我必须将它设置为null，但不知道如何。

Something like this:

像这样的东西：

Duplicate entry '' for key 'users_email_unique' empty field

密钥'users_email_unique'空字段的重复条目''

Error: Duplicate entry '' for key 'email'

错误：密钥'电子邮件'重复输入''

2 个解决方案

#1

Create a trigger that converts blanks to nulls on insert or update:

创建一个触发器，在插入或更新时将空白转换为空值：

create trigger null_email
before insert or update on users
for each row
new.email = nullif(old.email, '')

Or convert blanks to nulls on insert:

或者在插入时将空格转换为空值：

insert into users (..., email, ...)
values (..., nullif(?, ''), ...)

The trigger is the better way, because it handles data from any source, whereas method 2 would require the insert/update SQL of every application to conform to the "no blanks" rule.

触发器是更好的方法，因为它处理来自任何源的数据，而方法2则要求每个应用程序的插入/更新SQL符合“无空白”规则。

#2

You could use partial index:

你可以使用部分索引：

CREATE UNIQUE INDEX idx_unq_tab_email ON tab(email) WHERE TRIM(email) <> '';

DBFiddle Demo

DBFiddle演示

That way you still have UNIQUE constraint plus original value.

这样你仍然有UNIQUE约束加上原始值。

#1