What is difference between Boolean and tinyint(1) in MySQL?

The basic difference between Boolean and tinyint(1) is only in the naming convention. If we say that we need true or false values then Boolean comes to our mind, instead of tinyint(1).

These data types are synonyms. It is up to us which data type we want to use- values can be 1 and 0 or true and false.

The following is an example.

Creating a table with Boolean data type.

mysql> create table BooleanDemo
   -> (
   -> Light Boolean
   -> );
Query OK, 0 rows affected (0.52 sec)

Inserting records into table.

mysql> insert into BooleanDemo values(true);
Query OK, 1 row affected (0.12 sec)

To display all record.

mysql> select *from BooleanDemo;

The following is the output that displays Boolean TRUE.

+-------+
| Light |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

Creating a table with tinyint(1) data type.

mysql> create table tinyint1Demo
   -> (
   -> Light tinyint(1)
   -> );
Query OK, 0 rows affected (0.58 sec)

Inserting records into the table.

mysql> insert into tinyint1Demo values(1);
Query OK, 1 row affected (0.12 sec)

To display all the records.

mysql> select *from tinyint1Demo;

Here is the output.

+-------+
| Light |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

Note − The only basic difference is in semantics. If we are talking about true or false then it is clear that data type should be boolean. But if we are talking about 0 or 1 then data type should be tinyint(1).

Ankith Reddy

Published on 23-Nov-2018 10:33:39