BOOLEAN or TINYINT confusion

Question

I was designing a database for a site where I need to use a boolean datetype to store only 2 states, true or false. I am using MySQL.

While designing the database using phpMyAdmin, I found that I have both the BOOLEAN datatype and the TINYINT datatype. I went through different articles, some said TINYINT is the same as BOOLEAN, no difference. Some say BOOLEAN is converted into TINYINT in MySQL.

MY question is, if they both are same why do there exist two? There should be only one of them.

Here is the reference to the article(s) I read:

• MySQL - Explain the difference between BOOL, TINYINT and BIT.

Toon

Answer 1

MySQL does not have internal boolean data type. It uses the smallest integer data type - TINYINT.

The BOOLEAN and BOOL are equivalents of TINYINT(1), because they are synonyms.

Try to create this table -

CREATE TABLE table1 (
  column1 BOOLEAN DEFAULT NULL
);

Then run SHOW CREATE TABLE, you will get this output -

CREATE TABLE `table1` (
  `column1` tinyint(1) DEFAULT NULL
)

Shortstop

Yes, really. Haven't thought about the BIT. – Shortstop

But your answer is correct in all other aspects. What seems to be confusing the OP is the existence of synonyms. – Legitimize

It seems that it is done for backward compatibility. The BOOLEAN data type was before the MySQL 5 and the was not optimized BIT type, it was also TINYINT. From the documentation - New Features Planned for 5.1: Optimize the BIT type to take one bit. (BIT now takes one byte; it is treated as a synonym for TINYINT.). – Shortstop

Yes, you can know have BIT(1) or BIT(17) or even BIT(64) – Legitimize

@Shortstop -- Where your answer has the most votes and appears first (in my list anyway) and some time has past, is there any chance you would be willing add to your answer to include some discussion of the BIT type on MySQL 5.1 and later? – Thermochemistry

Having MySQL 5.7 we see the same behavior - dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html – Shortstop

@Thermochemistry Perhaps mentioning it is valuable, however BIT(1) doesn't actually use any less space than TINYINT(1) and does not display as most people would expect when using the standard mysql console. Because of that downside, and no storage benefit, just using TINYINT(1) or BOOLEAN seems to be the most common in my experience. – Brickle

Answer 2

Just a note for php developers (I lack the necessary stackoverflow points to post this as a comment) ... the automagic (and silent) conversion to TINYINT means that php retrieves a value from a "BOOLEAN" column as a "0" or "1", not the expected (by me) true/false.

A developer who is looking at the SQL used to create a table and sees something like: "some_boolean BOOLEAN NOT NULL DEFAULT FALSE," might reasonably expect to see true/false results when a row containing that column is retrieved. Instead (at least in my version of PHP), the result will be "0" or "1" (yes, a string "0" or string "1", not an int 0/1, thank you php).

It's a nit, but enough to cause unit tests to fail.

Struma

As an additional note, PHP's mysql drivers pull in all integer types as strings. – Friedlander

Answer 3

The Newest MySQL Versions have the new BIT data type in which you can specify the number of bits in the field, for example BIT(1) to use as Boolean type, because it can be only 0 or 1.

Axil

Answer 4

As of MySql 5.1 version reference

BIT(M) =  approximately (M+7)/8 bytes, 
BIT(1) =  (1+7)/8 = 1 bytes (8 bits)

=========================================================================

TINYINT(1) take 8 bits.

https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html#data-types-storage-reqs-numeric

Modular

Your reference says the storage required is actually "approximately (M+7)/8 bytes". i.e., it rounds up to the next full byte. Thus it does not take 1 bit. – Klatt

Answer 5

The numeric type overview for MySQL states: BOOL, BOOLEAN: These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true.

See here: https://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html

Cesarcesare