主页 / dba / 问题 / 68266
Accepted
Adam Matan
Asked: 2014-06-16 02:32:48 +0800 CST

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

  • 772

在 PostgreSQL 中存储电子邮件地址的正确数据类型是什么？

我可以使用varchar（甚至text），但我想知道是否有更具体的电子邮件数据类型。

database-design postgresql

6 个回答

  1. Best Answer

    定制DOMAIN_

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

    使用 HTML5type=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 '[email protected]'::email;

    但不是

    SELECT 'asdf@foob,,ar.com'::email;
SELECT 'asd@[email protected]'::email;

    因为这两个都返回

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

    因为这也是基于 citext

    SELECT '[email protected]'::email = '[email protected]';

    默认返回真。

    使用plperlu/Email::Valid

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

    CREATE EXTENSION plperlu;

    然后创建函数，注意我们将 at 标记为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));

    脚注

    1. 使用citext在技术上是错误的。SMTP 定义local-part为区分大小写。但是，这又是一个规范愚蠢的例子。它包含自己的身份危机。规范说local-part（前面的部分@）“可能区分大小写”......“必须被视为区分大小写”......但是“利用邮箱本地部分的区分大小写会阻碍互操作性并且不鼓励。”
    2. 电子邮件地址的规范非常复杂，甚至都不是独立的。复杂确实是轻描淡写，那些制定规范的人甚至不理解它。. 来自regular-expression.info 上的文档

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

    • 63

  2. 我总是使用CITEXT电子邮件，因为电子邮件地址（实际上）不区分大小写，即 [email protected] 与 [email protected] 相同。

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

    -- 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 = '[email protected]';

    相比于：

    SELECT * FROM address WHERE lower(email) = lower('[email protected]');

    CITEXT是在名为 "citext"的标准扩展模块中定义的类型，可通过键入：

    CREATE EXTENSION citext;

    PStext并且varchar在 Postgres 中几乎是相同的，并且没有text像预期的那样使用任何惩罚。检查这个答案：文本和varchar之间的区别

    • 55

  3. 我一直使用varchar(254)的电子邮件地址不得超过 254 个字符。

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

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

    此外，您可能希望添加触发器或一些此类逻辑来标准化电子邮件地址，以防您希望在其上添加唯一键。

    特别是domain电子邮件地址的部分（其形式为local-part@domain不区分大小写，但local-part必须视为区分大小写。参见https://www.rfc-editor.org/rfc/rfc5321#section-2.4

    另一个考虑因素是，如果您希望在表单中存储姓名和电子邮件地址"Joe Bloggs" <[email protected]>，在这种情况下，您需要一个长度超过 254 个字符的字符串，并且您将无法有意义地使用唯一约束。我不会这样做，并建议分别存储姓名和电子邮件地址。在您的表示层中始终可以使用这种格式的漂亮打印地址。

    • 12

  4. 您可能对使用 a 感兴趣CHECK CONSTRAINT（可能更容易，但可能拒绝比您想要的更多，或者您使用 FUNCTION，在此处和此处讨论）。您可以使用带有约束的正则表达式。

    你可以这样做：

    CREATE TABLE person 
(
  name TEXT,
  address1 TEXT,
  ...
  ...  other fields...
  ...
  email TEXT
  ...    
  CONSTRAINT 
    proper_email CHECK (email ~* '^[A-Za-z0-9._+%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$')
);

    运算符不区分大小写~*匹配。正则表达式（1）可以随心所欲地复杂 - 请参阅这个权威答案，其中包含对 6,509 个字符长的确定正则表达式的引用！

    基本上，这都是关于特异性和易于实施之间的权衡。不过有趣的话题。PostgreSQL 甚至有一个本地 IP 地址类型，但在 pgfoundry 上有一个用于电子邮件数据类型的项目here。

    但是，我发现的最好的是电子邮件域。域比检查约束要好，因为如果您更改它，您只需在域定义中执行一次，而无需跟踪更改所有检查约束的父子表。

    域真的很酷——有点像数据类型，但实现起来更简单。我在 Firebird 中使用过它们——Oracle 甚至没有它们！

    1）从这里无耻地举起例子

    • 3

  5. 没有像“电子邮件”这样的数据类型，但我们仍然可以在 PostgreSQL 中使用正则表达式。所以，让我们利用它。

    你想做的第一件事是：

    1. CREATE EXTENSION citext;此数据类型用于不区分大小写的文本。因此，无论文本是大写还是小写，Postgres 都会将它们视为相同的文本。
    2. 在表中添加 CHECK 约束
    CREATE TABLE persons(
    person_id SERIAL PRIMARY KEY,
    person_first_name VARCHAR(50) NOT NULL,
    person_last_name VARCHAR(50),
    person_gender gender_t NOT NULL,
    person_birthdate DATE,
    person_email CITEXT NOT NULL UNIQUE,
    CHECK(
        person_email ~ '^[\w]+\@\w{0,6}\.\w{2,4}$'
    )
);

    现在在这里，您将正则表达式应用于您person_email的列。顺便说一下，正则表达式有点说明性，有更好的方法来验证电子邮件。

    为了记录，数据类型person_gender是自定义类型

    CREATE TYPE gender_t AS ENUM('M', 'F');
    • 2
    • 域email.sql- 基本的最小验证，但速度很快。因为每次查询都会触发验证，UPDATE即使字段的值没有改变！
    • 功能is_email.sql- 几乎按照规范进行验证，但速度较慢。
    • 0

相关问题