What's the best datatype for an email field?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

What's the best datatype for an email field?

Post by JellyFish »

What is the best MySQL datatype for a email field; a field that holds an email address? Should I use TEXT, BLOB, CHAR, or VARCHAR.

I appreciate your input.

[EDIT]Also what would be a good datatype for randomly generated key like: sD3k2J5lsKd2l3kj0tARe0jt894HikN2 (A string of 32 numbers, upper-case letters, and lower-case letters)?
Last edited by JellyFish on Sat Mar 07, 2009 2:07 pm, edited 1 time in total.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: What's the best datatype for an email field?

Post by jayshields »

TEXT and BLOB are for large amounts of data. CHAR is for when you know exactly the amount of characters that the field will hold. VARCHAR is for variable character length data.

So use VARCHAR for email (maybe a VARCHAR with a 100 limit?), and CHAR for the generated key if the character amount is a set length.

http://dev.mysql.com/doc/refman/5.0/en/ ... rview.html
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: What's the best datatype for an email field?

Post by JellyFish »

Thanks for your reply Jay.

About the email, I don't know if email addresses usually have a character limit so I don't know if it would be correct to have on in my database. Should I go with VARCHAR with a character limit or should I stick with TEXT?

I think CHAR is best for the key sense I know every key will have 32 characters exact. But for some odd reason when I try to change the datatype of my key field to CHAR(32) it ends up with VARCHAR(32). I'm changing the datatype in godaddy's MySQL manager (PHPMyAdmin, by Starfield Technologies), should I just write out a ALTER statement or something?

[EDIT] This is the query I'm using to change the Key field's datatype:

Code: Select all

ALTER TABLE `users` CHANGE `Key` `Key` CHAR( 32 ) NOT NULL;
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: What's the best datatype for an email field?

Post by Bill H »

MySQL sometimes makes changes to column types on its own. From MySQL docs:
If any column in a table has a variable length, the entire row becomes variable-length as a result. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR columns longer than three characters are changed to VARCHAR columns. This does not affect how you use the columns in any way; in MySQL, VARCHAR is just a different way to store characters. MySQL performs this conversion because it saves space and makes table operations faster. See Chapter 13, Storage Engines.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: What's the best datatype for an email field?

Post by JellyFish »

So what you're saying is, Bill, a CHAR field and a VARCHAR field cannot exist within the same table?

Sense the amount of bytes used for varchar values varies, I guess the email field should be VARCHAR (255), right? In that case, why ever have a lower limit for VARCHAR types?
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: What's the best datatype for an email field?

Post by Bill H »

Email addresses that long would be rare indeed, but since the space taken up is only that used by the entry and the (255) is only the upper limit, there is nothing to lose by using it.

The lower limit of the size for VARCHAR has to do with efficiency. Using an extra byte to store the length of a field smaller than four is not efficient.

A CHAR(1), CHAR(2), OR A CHAR(3) can exist with VARCHARS, but any CHAR columns longer than that will get converted. But so what? There is no difference in they way you use a CHAR and a VARCHAR, so why would you care which it is?
Sense the amount of bytes used for varchar values varies,
I'm not certain you quite understand what that means. The "amount of bytes used" means that all of the data entered by the user is stored, up to the limit specified. The amount of storage used is determined by the number of characters entered. Longer entries use more storage, shorter entries use less, but the amount is determined by the person making the entry. That is not really a bad thing, and actually makes the VARCHAR the better choice. (Which is why MySQL does the silent conversion thing.)
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: What's the best datatype for an email field?

Post by JellyFish »

Bill H wrote:The lower limit of the size for VARCHAR has to do with efficiency. Using an extra byte to store the length of a field smaller than four is not efficient.
But if four is all that will be stored in a VARCHAR(255) field then 5 bytes will be all that is used, correct? But I guess if you set the field to VARCHAR(4) then it would ensure that only four characters will be entered thus also ensuring only 5 bytes or less to be used per row.
Bill H wrote:A CHAR(1), CHAR(2), OR A CHAR(3) can exist with VARCHARS, but any CHAR columns longer than that will get converted. But so what? There is no difference in they way you use a CHAR and a VARCHAR, so why would you care which it is?
I truncated my table and tried changing the field to CHAR(32), but it keeps in forcing VARCHAR(32). I even tried adding a new field and then renaming it, but still the same thing happens. I don't know what you mean by "...any CHAR columns longer then that will get converted.". Do you mean to say that if I already have data stored in the column I'm changing and the data exceeds the limits I'm specifying in the change, the data will automatically get converted to something more suitable?
Bill H wrote:Which is why MySQL does the silent conversion thing.
Which silent conversionation thing?
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: What's the best datatype for an email field?

Post by Bill H »

The data doesn't get converted. The column type gets converted. The text "foobar" stored in a CHAR is exactly the same as the text "foobar" stored in a VARCHAR. The SQL statements used to store it are the same, the SQL statements used to retrieve it are exactly the same. From the standpoint of using data and using the database there is no difference whatever between the two column (field) types.

If you are utterly dedicated to having a CHAR(32) then create a table that has only the one column in it. Nothing other than that one column and maybe some INT types. Then you can define it as a CHAR(32) and you'll be happy. Your database will be more difficult to use, because all of your TEXT and VARCHAR columns will be in a different table, but you will have the CHAR(32) column that you seem so desperate to achieve. There is absolutely no user advantage to that column type, but...

The thing that is accomplished by setting the length of a VARCHAR column is that you set an upper limit on the number of charaters that you are allowing to be stored in it. Any characters over that limit are truncated. It reduces the size of the database by restricting the versatility that you give to persons entering data. You tell them, in effect "the longest name you can use is x characters," or "here's how wordy you can be in your answer."

A VARCHAR(4) might use 5 bytes, if 4 characters are entered. If only 2 were entered then it would use 3 bytes. It might use anywhere from 1 to 5 bytes. If you create a VARCHAR(3) then MySQL says, in effect, that it is more efficient to always allocate 3 bytes than it is to measure and determine whether 1, 2, 3 or 4 bytes is required.

The "silent conversion" is what you are experiencing when you create a CHAR(32) and ger a VARCHAR(32). MySQL didn't scream and yell at you about errors, it simply changed your specification and created the more efficient column type. Note, "more efficient" column type.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: What's the best datatype for an email field?

Post by JellyFish »

I only have three columns in my table: UserID, Email, and Key, each with the types INT(10), VARCHAR(255), and VARCHAR(32) respectively. I'd like Key to be CHAR(32) but I guess it doesn't really make a difference. I'm just curious to why it wouldn't be changing to CHAR(32). I had Email set to TEXT before, maybe that could be it.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: What's the best datatype for an email field?

Post by Bill H »

If any column in a table has a variable length, the entire row becomes variable-length as a result. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR columns longer than three characters are changed to VARCHAR columns. This does not affect how you use the columns in any way; in MySQL, VARCHAR is just a different way to store characters. MySQL performs this conversion because it saves space and makes table operations faster.
Your VARCHAR(255) is a variable length field, and so your CHAR(32) gets changed to a VARCHAR(32). This is an improvement to your table. It makes it faster and more efficient. It does not harm you in any way, shape or form. It does not make any difference in the way you use the database. Any trifling difference in space used on disc is more than countered by speed and efficiency in database access.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: What's the best datatype for an email field?

Post by JellyFish »

VARCHAR(255) is variable length, so doesn't that mean a CHAR column can't exist in the same table? Unless not every VARCHAR is variable length. :S
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: What's the best datatype for an email field?

Post by Bill H »

Unless not every VARCHAR is variable length.
VARCHAR is, by definition, a variable length column type. CHAR is fixed length.
The VAR in VARCHAR stands, in fact, for "variable." :wink:
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: What's the best datatype for an email field?

Post by JellyFish »

Sorry Bill, I miss-read some of your other posts, and now I understand. So why can't a CHAR(4) column exist within the same table as a VARCHAR? I'm going to let my key column be and keep it as VARCHAR(32); like you said, MySQL changing my column to a VARCHAR is probably for performance reasons.

Thanks for both your of your posts, and Bill especially for staying with me and my questions. :)
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: What's the best datatype for an email field?

Post by Bill H »

So why can't a CHAR(4) column exist within the same table as a VARCHAR?
If any column in a table has a variable length, the entire row becomes variable-length as a result. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR columns longer than three characters are changed to VARCHAR columns. This does not affect how you use the columns in any way; in MySQL, VARCHAR is just a different way to store characters. MySQL performs this conversion because it saves space and makes table operations faster. See Chapter 13, Storage Engines.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: What's the best datatype for an email field?

Post by JellyFish »

So I guess the simple answer is, because it saves space and makes table operations faster. I guess what I was asking was, why CHAR(4); why not all CHARs? But maybe there's no need for me to know this. :P
Post Reply