database 数据库中电子邮件地址的最佳长度是多少？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1199190/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the optimal length for an email address in a database?
提问by Léo Léopold Hertz ??
Here is an extracted portion of my query, reflecting the EMAIL_ADDRESScolumn data type and property:
这是我查询的提取部分，反映了EMAIL_ADDRESS列数据类型和属性：
EMAIL_ADDRESS CHARACTER VARYING(20) NOT NULL,
However, John Saundersuses VARYING(256).
但是，约翰桑德斯使用VARYING(256).
This suggests me that I have not necessarily understood the VARYING correctly.
这表明我不一定正确理解 VARYING。
I understand it such that the length of an email address is 20 characters in my case, while 256 for Jodn.
我的理解是，在我的情况下，电子邮件地址的长度为 20 个字符，而对于 Jodn，则为 256 个字符。
Context in John's code
John 代码中的上下文
CREATE TABLE so."User"
(
USER_ID SERIAL NOT NULL,
USER_NAME CHARACTER VARYING(50) NOT NULL,
EMAIL_ADDRESS CHARACTER VARYING(256) NOT NULL, // Here
HASHED_PASSWORD so.HashedPassword NOT NULL,
OPEN_ID CHARACTER VARYING(512),
A_MODERATOR BOOLEAN,
LOGGED_IN BOOLEAN,
HAS_BEEN_SENT_A_MODERATOR_MESSAGE BOOLEAN,
CONSTRAINT User_PK PRIMARY KEY(USER_ID)
);
I have never seen email addresses longer than 20 characters, used by ordinary people.
我从未见过普通人使用的超过 20 个字符的电子邮件地址。
What is the optimal length for an email address in a database?
数据库中电子邮件地址的最佳长度是多少？
回答by Iain Hoult
The maximum length of an email address is 254 characters.
电子邮件地址的最大长度为 254 个字符。
Every email address is composed of two parts. The local part that comes before the '@' sign, and the domain part that follows it. In "[email protected]", the local part is "user", and the domain part is "example.com".
每个电子邮件地址由两部分组成。'@' 符号之前的本地部分和它后面的域部分。在“[email protected]”中，本地部分是“user”，域部分是“example.com”。
The local part must not exceed 64 characters and the domain part cannot be longer than 255 characters.
本地部分不能超过 64 个字符，域部分不能超过 255 个字符。
The combined length of the local + @ + domain parts of an email address must not exceed 254 characters. As described in RFC3696 Errata ID 1690.
电子邮件地址的本地 + @ + 域部分的总长度不得超过 254 个字符。如RFC3696 Errata ID 1690 中所述。
回答by pageman
from Ask Metafilter:
来自询问元过滤器：
My data comes from a database of 323 addresses. The distribution has some upper-end outliers (positively-skewed). It is normally distributed without the outliers (I tested it.)
Min: 12 1st quartile: 19 Mean (w/ outliers): 23.04 Mean w/o outliers): 22.79 3rd quartile: 26 Max (w/ outliers): 47 Max (w/o outliers): 35
Median: 23 Mode: 24 Std. Dev (w/ outliers): 5.20 Std. Dev (w/o outliers): 4.70
Ranges based on data including outliers 68.2% of data 17.8 - 28.2 95.4% of data 12.6 - 33.4 99.7% of data 7.4 - 38.6
Ranges based on data outliers excluded 68.2% of data 18.1 - 27.5 95.4% of data 13.4 - 32.2 99.7% of data 8.7 - 36.9
我的数据来自一个包含 323 个地址的数据库。该分布有一些高端异常值（正偏态）。它是没有异常值的正态分布（我测试过。）
最小值：12 第 1 个四分位数：19 平均值（无异常值）：23.04 平均无异常值）：22.79 第三四分位数：26 最大值（无异常值）：47 最大值（无异常值）：35
中位数：23 模式：24 标准。开发（带异常值）：5.20 Std。开发（无异常值）：4.70
基于包含异常值的数据的范围 68.2% 的数据 17.8 - 28.2 95.4% 的数据 12.6 - 33.4 99.7% 的数据 7.4 - 38.6
基于数据异常值的范围排除了 68.2% 的数据 18.1 - 27.5 95.4% 的数据 13.4 - 32.2 99.7% 的数据 8.7 - 36.9
If you sign up for http://www.abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijk.com/then your email address would surely be an outlier :)
如果您注册http://www.abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijk.com/那么您的电子邮件地址肯定是一个异常值:)
Here's What is the maximum safe length of an email address to allow in a website form?on Raycon with a slightly different mean (N=50,496, mean=23):
这是网站表单中允许的电子邮件地址的最大安全长度是多少？在 Raycon 上，平均值略有不同（N=50,496，平均值=23）：
回答by Dan Diplo
My work email address is more than 20 characters!
我的工作电子邮件地址超过 20 个字符！
Read the appropriate RFC specification:
阅读适当的RFC 规范：
"The local-part of an e-mail address may be up to 64 characters long and the domain name may have a maximum of 255 characters"
“电子邮件地址的本地部分最长可达 64 个字符，域名最长可达 255 个字符”
回答by Nicolas Manzini
Just use varchar(50). Longer emails are crap, every time.
只需使用varchar(50). 较长的电子邮件每次都是垃圾。
Just look how long 50 chars is:
看看 50 个字符有多长：
peoplewithanemail@ddressthislongjustuseashorterone
peoplewithanemail@addressthislongjustuseashorterone
If you allow 255 character emails:
如果您允许 255 个字符的电子邮件：
- Displaying them can mess up your UI (at best they will be cut off, at worst they push your containers and margins around) and
- Malicious users can do things with them you can't anticipate (like those cases where hackers used a free online API to store a bunch of data)
- 显示它们可能会弄乱您的 UI（充其量它们会被切断，最坏的情况是它们会推动您的容器和边距）和
- 恶意用户可以用他们做你无法预料的事情（比如黑客使用免费的在线 API 存储大量数据的情况）
(Statistics show that no-one actually enters more than about 50 chars for a legit email address, see e.g.: pageman's answer https://stackoverflow.com/a/1199245/87861)
（统计数据表明，实际上没有人为合法的电子邮件地址输入超过 50 个字符，参见例如：寻呼机的回答https://stackoverflow.com/a/1199245/87861）
回答by VoidPointer
Variable character types in databases don't occupy unneeded space. Thus, there is no reason to constrain such fields as much as possible. Depending on the name of a person, the naming scheme used by their organization and their domain name, an address can easily exceed 20 characters.
数据库中的可变字符类型不会占用不需要的空间。因此，没有理由尽可能地限制这些字段。根据人名、组织使用的命名方案和域名，一个地址很容易超过 20 个字符。
There is no limit as to the length of local-part and domain-name in RFC-2822. RFC-2181limits the domain name to 255 octets/characters though.
RFC-2822 中对 local-part 和 domain-name 的长度没有限制。不过，RFC-2181将域名限制为 255 个八位字节/字符。
Again, since a varcharonly uses the space actually used by the string you store, there is no reason to have a small limit for email address length. Just go with 512 and stop worrying. Everything else is premature optimization
同样，由于varchar仅使用您存储的字符串实际使用的空间，因此没有理由对电子邮件地址长度进行小限制。只需使用 512 即可，无需担心。其他一切都是过早的优化
回答by PhoneixS
Initially the maximum is 320 characters (64+1+255, as show in other answers) but as RFC 3696 Errata 1003said:
最初最大为 320 个字符（64+1+255，如其他答案所示），但正如RFC 3696 Errata 1003所说：
However, there is a restriction in RFC 2821 on the length of an address in MAIL and RCPT commands of 256 characters. Since addresses that do not fit in those fields are not normally useful, the upper limit on address lengths should normally be considered to be 256.
但是，RFC 2821 中对 MAIL 和 RCPT 命令中的地址长度有 256 个字符的限制。由于不适合这些字段的地址通常没有用，因此地址长度的上限通常应视为 256。
And from RFC 5321section 4.5.3.1.3:
4.5.3.1.3. Path
The maximum total length of a reverse-path or forward-path is 256 octets (including the punctuation and element separators)
4.5.3.1.3. 小路
反向路径或前向路径的最大总长度为 256 个八位字节（包括标点符号和元素分隔符）
This is including the opening and closing brackets so it let us to only 254 octetsof email address.
这包括开始和结束括号，因此它让我们只有254 个八位字节的电子邮件地址。
But get in mind that the number of octets may not be equal to the numbers of characters (a char may have 2 or more octets). Also the RFC section 4.5.3.1tell that there can be fields of more that the maximum and this is possible but not guarantied to servers to catch they correctly.
但请记住，八位字节的数量可能不等于字符的数量（一个字符可能有 2 个或更多的八位字节）。此外，RFC 部分 4.5.3.1说明可以有更多的字段，这是可能的，但不能保证服务器正确捕获它们。
And then you can/must use a VARCHAR(254)to store an email address.
然后您可以/必须使用 aVARCHAR(254)来存储电子邮件地址。
Note: In MySQL at least, a column declared as VARCHARwhit less or equal than 255 octets will be all stored as 1 byte + length(the 1 is to store the length) so no space is gained if used a lower limit.
注意：至少在 MySQL 中，声明为VARCHAR小于或等于 255 个八位字节的列将全部存储为1 byte + length（1 是存储长度），因此如果使用下限，则不会获得空间。
回答by Stu Thompson
As others have said, way bigger than 20. 256 + 64 sounds good to me, and is RFC compliant.
正如其他人所说，比 20 大得多。256 + 64 对我来说听起来不错，并且符合 RFC。
The only reason to not have such a large value for your database is if you are worrying about performance or space, and if you are doing that then I'm 99.99999999999999% sure that is premature optimization.
对您的数据库没有如此大的价值的唯一原因是，如果您担心性能或空间，如果您正在这样做，那么我 99.999999999999999% 确定这是过早优化。
Go big.
去大。
回答by Stu Thompson
A CHAR(20) field will always take up 20 characters, whether you use it all or not. (Often padded with spaces at the end.) A VARCHAR(20) field will take up up to20 characters, but may take up less. One benefit of CHAR()s constant width is fast jumping to a row in a table, because you can just calculate the index it must be on. The drawback is wasting space.
CHAR(20) 字段将始终占用 20 个字符，无论您是否全部使用它。（通常在末尾用空格填充。）一个 VARCHAR(20) 字段最多占用20 个字符，但可能占用更少。CHAR() 的恒定宽度的一个好处是快速跳转到表中的一行，因为您只需计算它必须位于的索引。缺点是浪费空间。
The benefit of constant-sized CHAR(x)'s is lost if you have any VARCHAR(x) columns in your table. I seem to recall that MySQL silently converted any CHAR() fields into VARCHAR() behind the scenes if some columns were VARCHAR()s.
如果您的表中有任何 VARCHAR(x) 列，则常量大小的 CHAR(x) 的好处就会丢失。我似乎记得如果某些列是 VARCHAR()，MySQL 会在幕后默默地将任何 CHAR() 字段转换为 VARCHAR()。
