Mysql – When to use the different numeric data types – TINYINT / SMALLINT / MEDIUMINT / INT / BIGINT – MySQL

For MySQL 5.0.3 and higher, you can use BIT. The manual says:

As of MySQL 5.0.3, the BIT data type is used to store bit-field values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64.

Otherwise, according to the MySQL manual you can use BOOL or BOOLEAN, which are at the moment aliases of tinyint(1):

Bool, Boolean: These types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.

MySQL also states that:

We intend to implement full boolean type handling, in accordance with standard SQL, in a future MySQL release.

References: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

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:

1. You don't need to do any kind of arithmetic with it, and
2. 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.