What is the optimal length for an email address in a database?

Here is an extracted portion of my query, reflecting the EMAIL_ADDRESS column data type and property:

EMAIL_ADDRESS CHARACTER VARYING(20) NOT NULL, 

However, John Saunders uses VARYING(256).

This suggests me that I have not necessarily understood the VARYING correctly.

I understand it such that the length of an email address is 20 characters in my case, while 256 for Jodn.

Context in John's code

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.

What is the optimal length for an email address in a database?

The maximum length of an email address is 254 characters.

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".

The local part must not exceed 64 characters and the domain part cannot be longer than 255 characters.

The combined length of the local + @ + domain parts of an email address must not exceed 254 characters. As described in RFC3696 Errata ID 1690.

I got the original part of this information from here

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

If you sign up for http://www.abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijk.com/ then your email address would surely be an outlier :)

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):

Just use varchar(50). Longer emails are crap, every time.

Just look how long 50 chars is:

peoplewithanemail@ddressthislongjustuseashorterone

If you allow 255 character emails:

• 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)

(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)

My work email address is more than 20 characters!

Read the appropriate RFC specification:

> "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"

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.

There is no limit as to the length of local-part and domain-name in RFC-2822. RFC-2181 limits the domain name to 255 octets/characters though.

Again, since a varchar only 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

Initially the maximum is 320 characters (64+1+255, as show in other answers) but as RFC 3696 Errata 1003 said:

> 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.

And from RFC 5321 section 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)

This is including the opening and closing brackets so it let us to only 254 octets of email address.

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.1 tell that there can be fields of more that the maximum and this is possible but not guarantied to servers to catch they correctly.

And then you can/must use a VARCHAR(254) to store an email address.

Note: In MySQL at least, a column declared as VARCHAR whit 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.

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 to 20 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.

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.

TLDR Answer

Use a VARCHAR(256) to store the 256 character maximum entailed in current, prevailing RFC Internet standards.

Source

SMTP originally defined what a path was in RFC821, published August 1982, which is an official Internet Standard (most RFC's are only proposals). To quote it...

>...a reverse-path, specifies who the mail is from. > >...a forward-path, which specifies who the mail is to.

RFC2821, published in April 2001, is the Obsoleted Standard that defined our present maximum values for local-parts, domains, and paths. A new Draft Standard, RFC5321, published in October 2008, keeps the same limits. To quote RFC2821...

>4.5.3.1.3. Path > > The maximum total length of a reverse-path or forward-path is 256 characters (including the punctuation and element separators).

Common Mistaken Answers

In February 2004, RFC3696 was published and it mistakenly cites the max limit of email addresses as 320 characters. But this was an "informational-only" document, which states...

>"This memo provides information for the Internet community. It does not specify an Internet standard of any kind."

We can disregard this limit, then, as it is published before RFC5321, which is a Draft Internet Standard that keeps the email address max limit unchanged at 256 characters.

As others have said, way bigger than 20. 256 + 64 sounds good to me, and is RFC compliant.

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.

Go big.