BOOLEAN or TINYINT to store values in MySQL?

MySQLMySQLi Database

The MySQL BOOLEAN and BOOL both are equivalent to TINYINT(1). Whenever you create a column using BOOLEAN and BOOL data type, MySQL implicitly convert the BOOLEAN and BOOL to TINYINT(1). The BOOLEAN and BOOL are equivalents of TINYINT(1), since they are synonyms.

Create a table using BOOLEAN data type. The query to create a table.

mysql> create table BooleanDemo
   -> (
   -> IsOn BOOLEAN
   -> );
Query OK, 0 rows affected (0.58 sec)

Now check internal structure of the above table. The query is as follows −

mysql> show create table BooleanDemo;

Output

+-------------+----------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                     |
+-------------+----------------------------------------------------------------------------------------------------------------------------------+
| BooleanDemo | CREATE TABLE `booleandemo` ( `IsOn` tinyint(1) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci    |
+-------------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Look at the above sample output, BOOLEAN is converted into tinyint(1). Same is case for BOOL data type also. The query to create a table is as follows −

mysql> create table BOOLDemo
   -> (
   -> validUser BOOL
   -> );
Query OK, 0 rows affected (0.61 sec)

Now check the internal structure of the table. The query is as follows −

mysql> show create table BOOLDemo;

Output

+----------+------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                       |
+----------+------------------------------------------------------------------------------------------------------------------------------------+
| BOOLDemo | CREATE TABLE `booldemo` (`validUser` tinyint(1) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci    |
+----------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
raja
Published on 10-Jan-2019 11:36:21
Advertisements