Boolean VS Tinyint(1) for Boolean Values in MySQL

Boolean vs tinyint(1) for boolean values in MySQL

These data types are synonyms.

BOOLEAN or TINYINT confusion

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
)

MySQL: From Boolean to Tinyint(1) and back

No, there is no way you can distinguish a column that was created as BOOLEAN from a column that was created as TINYINT(1).

The BOOLEAN type isn't stored anywhere. It's only like an alias, which is transformed into TINYINT(1) before it is stored in the database. You won't find any mention of BOOLEAN in the metadata for a column.

It's like if you store an integer value from an expression ABS(?). You don't know if the original value passed to ABS() was positive or negative, because only the result of the ABS() expression is stored.

By the way, TINYINT(1) doesn't mean it allows only a small number range. It's natural to think the (1) is a size limit, like it is for CHAR or DECIMAL. But for integer types in MySQL, it has no effect on the size of the data type or the range of values it accepts. TINYINT is always an 8-bit integer, and accepts values from -128 to 127, or for TINYINT UNSIGNED it always accepts values from 0 to 255.

See also my answer to https://stackoverflow.com/a/3135854/20860

Why does TINYINT(1) function as a boolean but INT(1) does not?

The (1) in parentheses for a MySQL integer type has nothing to do with the range of values accepted by the data type, or how it is stored. It's only for display.

See also my answer to Types in MySQL: BigInt(20) vs Int(20) etc.

TINYINT is no different from TINYINT(1) or TINYINT(2) or TINYINT(64). It's an 8-bit signed integer data type, and it accepts any 8-bit integer value from -128 to 127.

mysql> create table b (i tinyint(1));

mysql> insert into b values (42);

mysql> select * from b;
+------+
| i    |
+------+
|   42 |
+------+

For convenience, MySQL supports an alias for BOOL, which is replaced immediately by TINYINT(1).

mysql> create table b2 (i bool);

mysql> show create table b2;

CREATE TABLE `b2` (
  `i` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

As I said, the use of (1) means almost nothing, it's only a convention so that if you see TINYINT(1) it's reasonable to assume the column is intended to be used as a boolean. But nothing in MySQL prevents you from storing other integer values in it.

If you want a column to accept only 0 or 1, you can use BIT(1):

mysql> create table b3 (i bit(1));

mysql> insert into b3 values (0), (1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into b3 values (-1);
ERROR 1406 (22001): Data too long for column 'i' at row 1

mysql> insert into b3 values (2);
ERROR 1406 (22001): Data too long for column 'i' at row 1

This doesn't save any space compared to TINYINT though, because the storage for a given column rounds up to the nearest byte.

PS: Despite answer from @samdy1, TINYINT does not store strings '0' or '1' at all, it stores integers 0 or 1, as well as other integers from -128 to 127. There is no need to quote integers in SQL, and I am often puzzled why so many developers do.

True/False vs 0/1 in MySQL

Some "front ends", with the "Use Booleans" option enabled, will treat all TINYINT(1) columns as Boolean, and vice versa.

This allows you to, in the application, use TRUE and FALSE rather than 1 and 0.

This doesn't affect the database at all, since it's implemented in the application.

There is not really a BOOLEAN type in MySQL. BOOLEAN is just a synonym for TINYINT(1), and TRUE and FALSE are synonyms for 1 and 0.

If the conversion is done in the compiler, there will be no difference in performance in the application. Otherwise, the difference still won't be noticeable.

You should use whichever method allows you to code more efficiently, though not using the feature may reduce dependency on that particular "front end" vendor.

Why MySQL interprets Boolean as TINYINT(1) instead of BIT(1)?

It depends on version and database engine and driver

• BIT is supported properly in 5.05+ with MyISAM and InnoDB
• Some JDBC drivers need to be told this (eg Kettle's bundled driver)

But BIT is preferable to TINYINT of course.

It's just legacy and inertia that keeps TINYINT...

MySQL tinyint to boolean

To MySQL, because 2 is neither 1 nor 0, then 2 is neither TRUE nor FALSE. Consider:

SELECT 2 = TRUE, 2 = FALSE;

This returns:

| 2 = TRUE | 2 = FALSE |
| -------- | --------- |
| 0        | 0         |

You would need to express the condition differently, like:

SELECT data FROM table WHERE active > 0

This will also work (any non-zero value is considered true, see further):

SELECT data FROM table WHERE active;

---

This behavior is documented in the manual, which states:

BOOL, BOOLEAN

These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true.

mysql> SELECT IF(0, 'true', 'false');
+------------------------+
| IF(0, 'true', 'false') |
+------------------------+
| false                  |
+------------------------+

mysql> SELECT IF(1, 'true', 'false');
+------------------------+
| IF(1, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+

mysql> SELECT IF(2, 'true', 'false');
+------------------------+
| IF(2, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+

However, the values TRUE and FALSE are merely aliases for 1 and 0, respectively, as shown here:

mysql> SELECT IF(0 = FALSE, 'true', 'false');
+--------------------------------+
| IF(0 = FALSE, 'true', 'false') |
+--------------------------------+
| true                           |
+--------------------------------+

mysql> SELECT IF(1 = TRUE, 'true', 'false');
+-------------------------------+
| IF(1 = TRUE, 'true', 'false') |
+-------------------------------+
| true                          |
+-------------------------------+

mysql> SELECT IF(2 = TRUE, 'true', 'false');
+-------------------------------+
| IF(2 = TRUE, 'true', 'false') |
+-------------------------------+
| false                         |
+-------------------------------+

mysql> SELECT IF(2 = FALSE, 'true', 'false');
+--------------------------------+
| IF(2 = FALSE, 'true', 'false') |
+--------------------------------+
| false                          |
+--------------------------------+

Which MySQL data type to use for storing boolean values

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

---

Related Topics