mysql常见字段对应数据类型,常见的MySQL字段及其相应的数据类型

I am setting up a very small MySQL database that stores, first name, last name, email and phone number and am struggling to find the 'perfect' datatype for each field. I know there is no such thing as a perfect answer, but there must be some sort of common convention for commonly used fields such as these. For instance, I have determined that an unformatted US phone number is too big to be stored as an unsigned int, it must be at least a bigint.

Because I am sure other people would probably find this useful, I dont want to restrict my question to just the fields I mentioned above.

What datatypes are appropriate for common database fields? Fields like phone number, email and address?

解决方案

Someone's going to post a much better answer than this, but just wanted to make the point that personally I would never store a phone number in any kind of integer field, mainly because:

You don't need to do any kind of arithmetic with it, and

Sooner or later someone's going to try to (do something like) put brackets around their area code.

In general though, I seem to almost exclusively use:

INT(11) for anything that is either an ID or references another ID

DATETIME for time stamps

VARCHAR(255) for anything guaranteed to be under 255 characters (page titles, names, etc)

TEXT for pretty much everything else.

Of course there are exceptions, but I find that covers most eventualities.