SQL 电子邮件字段应该多长？

How long should SQL email fields be?

提问by Mala

I recognize that an email address can basically be indefinitely long so any size I impose on my varchar email address field is going to be arbitrary. However, I was wondering what the "standard" is? How long do you guys make it? (same question for Name field...)

我认识到电子邮件地址基本上可以无限长，因此我对 varchar 电子邮件地址字段施加的任何大小都将是任意的。但是，我想知道“标准”是什么？你们做多久？（名称字段的相同问题...）

update:Apparently the max length for an email address is 320 (<=64 name part, <= 255 domain). Do you use this?

更新：显然电子邮件地址的最大长度为 320（<=64 个名称部分，<= 255 个域）。你用这个吗？

回答by ZZ Coder

The theoretical limit is really long but do you really need worry about these long Email addresses? If someone can't login with a 100-char Email, do you really care? We actually prefer they can't.

理论上的限制真的很长，但你真的需要担心这些长的电子邮件地址吗？如果有人无法使用 100 个字符的电子邮件登录，您真的在意吗？我们实际上更喜欢他们不能。

Some statistical data may shed some light on the issue. We analyzed a database with over 10 million Email addresses. These addresses are not confirmed so there are invalid ones. Here are some interesting facts,

一些统计数据可能会说明这个问题。我们分析了一个包含超过 1000 万个电子邮件地址的数据库。这些地址未经确认，因此存在无效地址。这里有一些有趣的事实，

1. The longest valid one is 89.
2. There are hundreds longer ones up to the limit of our column (255) but they are apparently fake by visual inspection.
3. The peak of the length distribution is at 19.
4. There isn't long tail. Everything falls off sharply after 38.

1. 最长有效的一个是 89。
2. 在我们的专栏（255）的限制范围内有数百个更长的，但通过目视检查它们显然是假的。
3. 长度分布的峰值在 19。
4. 没有长尾巴。38岁以后一切都急剧下降。

We cleaned up the DB by throwing away anything longer than 40. The good news is that no one has complained but the bad news is not many records got cleaned out.

我们通过扔掉超过 40 条记录来清理数据库。好消息是没有人抱怨，但坏消息是没有多少记录被清理干净。

回答by Eric

I've in the past just done 255 because that's the so-ingrained standard of short but not too short input. That, and I'm a creature of habit.

我过去只做过 ​​255 次，因为这是短而不是太短输入的根深蒂固的标准。那，我是一个习惯的生物。

However, since the max is 319, I'd do nvarchar(320)on the column. Gotta remember the @!

然而，由于最大值是 319，我会nvarchar(320)在列上做。一定要记住@！

nvarcharwon't use the space that you don't need, so if you only have a 20 character email address, it will only take up 20 bytes. This is in contrast to a ncharwhich will alwaystake up its maximum (it right-pads the value with spaces).

nvarchar不会使用你不需要的空间，所以如果你只有一个 20 个字符的电子邮件地址，它只会占用 20 个字节。这是相对于一个nchar将始终占据其最大（它的权利垫用空格值）。

I'd also use nvarcharin lieu of varcharsince it's Unicode. Given the volatility of email addresses, this is definitely the way to go.

由于它是 Unicode，我也将使用nvarchar代替varchar。鉴于电子邮件地址的波动性，这绝对是要走的路。

回答by MGOwen

The following email address is only 94 characters:

以下电子邮件地址只有 94 个字符：

[e-mail]

[e-mail]

• Would an organisation actually giveyou an email that long?
• If they were stupid enough to, would you actually usean email address like that?
• Would anyone?Of course not. Too long to type and too hard to remember.

• 一个组织真的会给你一封那么长的电子邮件吗？
• 如果他们足够愚蠢，你真的会使用这样的电子邮件地址吗？
• 会的人？当然不是。打字太长，记不住。

Even a 92-year-old technophobe would figure out how to sign up for a nice short gmail address, and just use that, rather than type this into your registration page.

即使是 92 岁的技术恐惧症患者也会想出如何注册一个不错的简短 Gmail 地址，然后使用它，而不是将其输入到您的注册页面中。

Disk space probably isn't an issue, but there are at least two problems with allowing user input fields to be many times longer than they need to be:

磁盘空间可能不是问题，但允许用户输入字段比所需长度长很多倍至少存在两个问题：

• Displaying them could mess up your UI(at best they will be cut off, at worst they push your containers and margins around)
• 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 存储大量数据的情况）

I like 50 chars:

我喜欢 50 个字符：

[email protected]

[email protected]

If one user in a million has to use their other email address to use my app, so be it.

如果百万分之一的用户必须使用他们的其他电子邮件地址才能使用我的应用程序，那就这样吧。

(Statistics show that no-one actually enters more than about 40 chars for email address, see e.g.: ZZ Coder's answer https://stackoverflow.com/a/1297352/87861)

（统计数据显示，实际上没有人为电子邮件地址输入超过 40 个字符，参见例如：ZZ Coder 的回答https://stackoverflow.com/a/1297352/87861）

回答by SasQ

According to this text, based on the proper RFC documents, it's not 320 but 254: http://www.eph.co.uk/resources/email-address-length-faq/

根据本文，基于适当的 RFC 文档，它不是 320，而是 254：http: //www.eph.co.uk/resources/email-address-length-faq/

Edit: Using WayBack Machine: https://web.archive.org/web/20120222213813/http://www.eph.co.uk/resources/email-address-length-faq/

编辑：使用 WayBack 机器：https://web.archive.org/web/20120222213813/http://www.eph.co.uk/resources/email-address-length-faq/

What is the maximum length of an email address?

254 characters

There appears to be some confusion over the maximum valid email address size. Most people believe it to be 320 characters (64 characters for the username + 255 characters for the domain + 1 character for the @ symbol). Other sources suggest 129 (64 + 1 + 64) or 384 (128+1+255, assuming the username doubles in length in the future).

This confusion means you should heed the 'robustness principle' ("developers should carefully write software that adheres closely to extant RFCs but accept and parse input from peers that might not be consistent with those RFCs." - Wikipedia) when writing software that deals with email addresses. Furthermore, some software may be crippled by naive assumptions, e.g. thinking that 50 characters is adequate (examples). Your 200 character email address may be technically valid but that will not help you if most websites or applications reject it.

The actual maximum email length is currently 254 characters:

"The original version of RFC 3696 did indeed say 320 was the maximum length, but John Klensin (ICANN) subsequently accepted this was wrong."

"This arises from the simple arithmetic of maximum length of a domain (255 characters) + maximum length of a mailbox (64 characters) + the @ symbol = 320 characters. Wrong. This canard is actually documented in the original version of RFC3696. It was corrected in the errata. There's actually a restriction from RFC5321 on the path element of an SMTP transaction of 256 characters. But this includes angled brackets around the email address, so the maximum length of an email address is 254 characters." - Dominic Sayers

电子邮件地址的最大长度是多少？

254 个字符

对最大有效电子邮件地址大小似乎有些混淆。大多数人认为它是 320 个字符（用户名 64 个字符 + 域 255 个字符 + @ 符号 1 个字符）。其他来源建议 129 (64 + 1 + 64) 或 384 (128+1+255，假设用户名的长度在未来加倍)。

这种混淆意味着你应该注意“健壮性原则”（“开发人员应该仔细编写与现有 RFC 密切相关的软件，但接受和解析可能与这些 RFC 不一致的同行的输入。” - 维基百科）在编写处理以下问题的软件时电子邮件地址。此外，一些软件可能会因幼稚的假设而瘫痪，例如认为 50 个字符就足够了（示例）。您的 200 个字符的电子邮件地址在技术上可能是有效的，但如果大多数网站或应用程序拒绝它，这对您没有帮助。

实际的最大电子邮件长度目前为 254 个字符：

“RFC 3696 的原始版本确实说 320 是最大长度，但 John Klensin (ICANN) 随后承认这是错误的。”

“这是由域的最大长度（255 个字符）+邮箱的最大长度（64 个字符）+@ 符号 = 320 个字符的简单算术引起的。错误。这个鸭子实际上记录在 RFC3696 的原始版本中。它已在勘误表中更正。实际上 RFC5321 对 SMTP 事务的路径元素有 256 个字符的限制。但这包括电子邮件地址周围的尖括号，因此电子邮件地址的最大长度为 254 个字符。” - 多米尼克·塞耶斯

回答by Jeff Ferland

If you're really being pendantic about it, make a username varchar(60), domain varchar(255). Then you can do ridiculous statistics on domain usage that is slightly faster than doing it as a single field. If you're feeling really gun-ho about optimization, that will also make your SMTP server able to send out emails with fewer connections / better batching.

如果您真的很在意它，请创建一个用户名 varchar(60)，域 varchar(255)。然后，您可以对域使用情况进行荒谬的统计，这比将其作为单个字段进行略快。如果您对优化感到非常兴奋，这也将使您的 SMTP 服务器能够以更少的连接/更好的批处理发送电子邮件。

回答by Wez Furlong

RFC 5321 (the current SMTP spec, obsoletes RFC2821) states:

RFC 5321（当前的 SMTP 规范，过时的 RFC2821）指出：

4.5.3.1.1. Local-part

The maximum total length of a user name or other local-part is 64
octets.

4.5.3.1.2. Domain

The maximum total length of a domain name or number is 255 octets.

4.5.3.1.1. 局部

用户名或其他本地部分的最大总长度为 64
个八位字节。

4.5.3.1.2. 领域

域名或域名的最大总长度为 255 个八位字节。

This pertains to just localpart@domain, for a total of 320 ASCII (7-bit) characters.

这仅适用于 localpart@domain，总共 320 个 ASCII（7 位）字符。

If you plan to normalize your data, perhaps by splitting the localpart and domain into separate fields, additional things to keep in mind:

如果您打算规范化您的数据，也许是通过将 localpart 和域拆分为单独的字段，请记住以下附加事项：

• A technique known as VERP may result in full-length localparts for automatically generated mail (may not be relevant to your use case)
• domains are case insensitive; recommend lowercasing the domain portion
• localparts are case sensitive; [email protected] and [email protected] are technically different addresses per the specs, although the policy at the domain.com maybe to treat the two addresses as equivalent. It's best to restrict localpart case folding to domains that are known to do this.

• 一种称为 VERP 的技术可能会导致自动生成邮件的全长本地部分（可能与您的用例无关）
• 域不区分大小写；建议小写域部分
• localparts 区分大小写；[email protected] 和 [email protected] 根据规范在技术上是不同的地址，尽管 domain.com 的策略可能是将这两个地址视为等价的。最好将 localpart 大小写折叠限制为已知会执行此操作的域。

回答by GibboK

I use varchar(64) i do not think anyone could have longer email

我使用 varchar(64) 我认为没有人可以拥有更长的电子邮件

回答by Noon Silk

For email, regardless of the spec, I virtually always go with 512 (nvarchar). Names and surnames are similar.

对于电子邮件，无论规范如何，我几乎总是使用 512 (nvarchar)。名字和姓氏是相似的。

Really, you need to look at how much you care about having a little extra data. For me, mostly, it's not a worry, so I'll err on the conservative side. But if you've decided, through logically and accurate means, that you'll need to conserve space, then do so. But in general, be conservative with field sizes, and life shall be good.

真的，你需要看看你有多关心有一点额外的数据。对我来说，大多数情况下，这并不担心，所以我会在保守方面犯错。但是，如果您已经通过逻辑和准确的方式决定需要节省空间，那么就这样做。但总的来说，田野大小要保守，生活应该是美好的。

Note that probably not all email clients support the RFC, so regardless of what it says, you may encounter different things in the wild.

请注意，可能并非所有电子邮件客户端都支持 RFC，因此无论它说什么，您都可能会遇到不同的情况。