在PostgreSQL中存储电子邮件地址的最佳方法是什么？

#1 楼

自定义DOMAIN s

我认为使用citext（不区分大小写）不够用[1]。使用PostgreSQL，我们可以创建一个自定义域，该域本质上是对类型的一些定义约束。我们可以创建一个域，例如通过citext类型或通过text类型。

使用HTML5 type=email规范

当前，对于什么是电子邮件地址这个问题，最正确的答案是在RFC5322中指定的。该规范非常复杂[2]，以至于一切都破坏了它。 HTML5包含针对电子邮件的不同规范，


此要求故意违反了RFC 5322，该规范定义了同时过于严格的电子邮件地址语法（在“ @”之前）字符），太模糊（在“ @”字符之后）和太松散（以大多数用户不熟悉的方式允许注释，空格字符和带引号的字符串）在此处无法实际使用。 [...]以下与JavaScript和Perl兼容的正则表达式是上述定义的实现。

/^[a-zA-Z0-9.!#$%&'*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$/

这可能是您想要的，如果对HTML5足够好，那么对您来说可能就足够了。我们可以在PostgreSQL中直接使用它。我还在这里使用citext（从技术上讲，这意味着您可以通过删除大写或小写字母在视觉上简单地进行正则表达式）。

CREATE EXTENSION citext;
CREATE DOMAIN email AS citext
  CHECK ( value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$' );

现在您可以请...

SELECT 'asdf@foobar.com'::email;

但不是

SELECT 'asdf@foob,,ar.com'::email;
SELECT 'asd@f@foobar.com'::email;

因为这两个返回

ERROR:  value for domain email violates check constraint "email_check"

因为这也是基于citext

SELECT 'asdf@foobar.com'::email = 'ASdf@fooBAR.com';

默认情况下返回true。

使用plperlu / Email::Valid


需要注意的是，有一种更正确的方法，使用plperlu会更加复杂。如果您需要这种正确性，则不要citext。 Email::Valid甚至可以检查域是否具有MX记录（例如Email :: Valid的文档中的示例）！首先，添加plperlu（需要超级用户）。

CREATE EXTENSION plperlu;

然后创建函数，注意我们将其标记为IMMUTABLE：

CREATE FUNCTION valid_email(text)
  RETURNS boolean
  LANGUAGE plperlu
  IMMUTABLE LEAKPROOF STRICT AS
$$
  use Email::Valid;
  my $email = shift;
  Email::Valid->address($email) or die "Invalid email address: $email\n";
  return 'true';
$$;

然后创建域，

CREATE DOMAIN validemail AS text NOT NULL
  CONSTRAINT validemail_check CHECK (valid_email(VALUE));

使用citext在技术上是错误的。 SMTP将local-part定义为区分大小写。但是，再次，这是规范愚蠢的情况。它包含自己的身份危机。规范说local-part（在@之前的部分）“必须区分大小写”……“必须被视为区分大小写”……但“利用邮箱本地部分的区分大小写会阻碍互操作性，因此不建议使用。 “
电子邮件地址的规范非常复杂，甚至都不是独立的。 Complex确实是一种轻描淡写，制定规范的人甚至都不理解。.来自regular-expression.info的文档


这两个正则表达式均未对整个电子邮件实施长度限制地址或本地部分或域名。 RFC 5322没有指定任何长度限制。这些源于其他协议（例如用于实际发送电子邮件的SMTP协议）的限制。 RFC 1035确实规定域必须少于或等于63个字符，但在其语法规范中不包括该域。原因是，真正的常规语言不能强制执行长度限制，并且不能同时禁止使用连字符。

评论

---

W3.org链接已损坏；这是另一种来源：html.spec.whatwg.org/multipage/…

– MaxGabriel
18年2月17日在2:02

---

@MaxGabriel感谢您的支持，您会尽快得到编辑权限，直到我将其固定在那里。

–埃文·卡洛尔（Evan Carroll）
18年2月17日在2:22

---

是否有理由在字符类中同时包含a-z和A-Z？

– xehpuk
18年5月31日在9:00

---

@xehpuk很好，因为〜是区分大小写的，因此您必须（a）使用〜*不区分大小写，或者（b）在char类中使用大小写字母。

–埃文·卡洛尔（Evan Carroll）
18年5月31日在14:40

---

citext的〜对我似乎不区分大小写，这就是我要问的原因。

– xehpuk
18年5月31日在14:51

---

#2 楼

我总是使用CITEXT来发送电子邮件，因为（实际上）电子邮件地址不区分大小写，即John@Example.com与john@example.com相同。

与文本相比，设置唯一索引以防止重复的操作也更加容易：

-- citext
CREATE TABLE address (
   id serial primary key,
   email citext UNIQUE,
   other_stuff json
);

-- text
CREATE TABLE address (
   id serial primary key,
   email text,
   other_stuff json
);
CREATE UNIQUE INDEX ON address ((lower(email)));

比较电子邮件也变得更加轻松和省力容易出错：

SELECT * FROM address WHERE email = 'JOHN@example.com';

与之相比：

SELECT * FROM address WHERE lower(email) = lower('JOHN@example.com');

CITEXT是标准中定义的类型扩展模块名为“ citext”，可通过键入以下命令获得：

CREATE EXTENSION citext;

PS在Postgres中，text和varchar实际上是相同的，使用text不会产生任何惩罚。检查以下答案：文本和varchar之间的区别

#3 楼

我始终使用varchar(254)，因为电子邮件地址不能超过254个字符。

请参阅https://stackoverflow.com/questions/386294/what-is-the-the-maximum-length-of-a -valid-email-address

尽管我确实遇到了一些有用的数据类型，但Postgresql没有电子邮件地址的内置类型。

此外，您可能希望添加触发器或某种此类逻辑以标准化电子邮件地址，以防万一您希望在其上添加唯一键。区分大小写，而domain必须区分大小写。请参阅http://tools.ietf.org/html/rfc5321#section-2.4

另一个注意事项是是否要存储名称和电子邮件地址格式为local-part，在这种情况下，您需要一个长度超过254个字符的字符串，并且您将无法有意义地使用唯一约束，我不会这样做，建议您分别存储名称和电子邮件地址。在您的表示层中始终可以使用这种格式打印漂亮的地址。

#4 楼

您可能对使用检查CONSTRAINT感兴趣（可能更容易，但可能会拒绝使用超出您期望的值，或者使用FUNCTION，在此处和此处进行了讨论。基本上，这都是关于特异性和易于实现之间的折衷。） PostgreSQL甚至有一个本地IP地址类型，但是在pgfoundry上有一个用于电子邮件数据类型的项目，但是，我发现的最好的是电子邮件域，该域比检查约束要好，因为如果您更改它，您只需在域定义中执行一次即可，而无需遵循跟踪父子表的跟踪来更改所有检查约束。域确实很酷-有点像数据类型，但更易于实现。我在Firebird中使用了它们-Oracle甚至没有它们！