What is the difference between BIT and TINYINT in MySQL?

Question

In which cases would you use which? Is there much of a difference? Which I typically used by persistence engines to store booleans?

Answer 1

Might be wrong but:

Tinyint is an integer between 0 and 255

bit is either 1 or 0

Therefore to me bit is the choice for booleans

Answer 2

A TINYINT is an 8-bit integer value, a BIT field can store between 1 bit, BIT(1), and 64 bits, BIT(64). For a boolean values, BIT(1) is pretty common.

Answer 3

BIT should only allow 0 and 1 (and NULL, if the field is not defined as NOT NULL). TINYINT(1) allows any value that can be stored in a single byte, -128..127 or 0..255 depending on whether or not it's unsigned (the 1 shows that you intend to only use a single digit, but it does not prevent you from storing a larger value).

For versions older than 5.0.3, BIT is interpreted as TINYINT(1), so there's no difference there.

BIT has a "this is a boolean" semantic, and some apps will consider TINYINT(1) the same way (due to the way MySQL used to treat it), so apps may format the column as a check box if they check the type and decide upon a format based on that.

Answer 4

At the risk of losing reputation, I'm sorry, I just have to say "RTM" (read the manual), it's all spelled out for you there

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html