Test Summary of data types BOOL/BOOLEAN and TINYINT in MySQL database

Guide]

On the Internet, many people ask if mysql provides a boolean type? MySQL database does provide a BOOLEAN type. Keywords of this data type can be written as boolean or boolean. But is the BOOLEAN Type of MySQL database data the same as that of other database products? This article explores the true face of the mysql boolean type.

(1) Data Type Test

(1). BOOLEAN type BOOL/BOOLEAN and tiny integer TINYINT

A) create a test table structure

 
 

  
  
root@localhost : test 05:12:49> CREATE TABLE boolean_test(ID INT NOT NULL AUTO_INCREMENT,  
  
  
   
  
  
    ->                           Online_Flag BOOL,  
  
  
   
  
  
    ->                           Lock_Flag BOOLEAN,  
  
  
   
  
  
    ->                           PRIMARY KEY(ID)  
  
  
   
  
  
    ->                           )ENGINE=InnoDB CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';  
  
  
   
  
  
Query OK, 0 rows affected (0.01 sec) 
 
 

We can find that the SQL Syntax of MySQL allows the field type to be written as BOOL or BOOLEAN. In addition, we can use the SHOW command to check the created table structure:

 
 

  
  
*************************** 1. row ***************************  
  
  
   
  
  
       Table: boolean_test  
  
  
   
  
  
Create Table: CREATE TABLE `boolean_test` (  
  
  
   
  
  
  `ID` int(11) NOT NULL AUTO_INCREMENT,  
  
  
   
  
  
  `Online_Flag` tinyint(1) DEFAULT NULL,  
  
  
   
  
  
  `Lock_Flag` tinyint(1) DEFAULT NULL,  
  
  
   
  
  
  PRIMARY KEY (`ID`)  
  
  
   
  
  
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8  
  
  
   
  
  
1 row in set (0.00 sec) 
 
 

Summary:

We can see the difference between the structure definition of the boolean_test table created by manual input and the table structure definition in the database:

• The MySQL database converts the Data Type BOOL/BOOLEAN of the field to TINYINT (1) by default );
• No error or warning message is prompted during the data type conversion process automatically completed by the MySQL database;

B) Write Test Data

 
 

  
  
root@localhost : test 05:12:58> INSERT INTO boolean_test(Online_Flag,Lock_Flag) VALUES(TRUE,FALSE);  
  
  
   
  
  
Query OK, 1 row affected (0.00 sec)  
  
  
   
  
  
root@localhost : test 05:13:58> INSERT INTO boolean_test(Online_Flag,Lock_Flag) VALUES(1,0);  
  
  
   
  
  
Query OK, 1 row affected (0.00 sec)  
  
  
   
  
  
root@localhost : test 05:14:04> INSERT INTO boolean_test(Online_Flag,Lock_Flag) VALUES(2,-1);  
  
  
   
  
  
Query OK, 1 row affected (0.00 sec)  
  
  
   
  
  
root@localhost : test 05:14:11> INSERT INTO boolean_test(Online_Flag,Lock_Flag) VALUES(-128,127);  
  
  
   
  
  
Query OK, 1 row affected (0.00 sec) 
 
 

 
 

  
  
root@localhost : test 05:14:18> INSERT INTO boolean_test(Online_Flag,Lock_Flag) VALUES(-256,256);  
  
  
   
  
  
Query OK, 1 row affected, 2 warnings (0.00 sec)  
  
  
   
  
  
root@localhost : test 05:14:24> SHOW WARNINGS;  
  
  
   
  
  
+---------+------+------------------------------------------------------+  
  
  
   
  
  
| Level   | Code | Message                                              |  
  
  
   
  
  
+---------+------+------------------------------------------------------+  
  
  
   
  
  
| Warning | 1264 | Out of range value for column 'Online_Flag' at row 1 |  
  
  
   
  
  
| Warning | 1264 | Out of range value for column 'Lock_Flag' at row 1   |  
  
  
   
  
  
+---------+------+------------------------------------------------------+  
  
  
   
  
  
2 rows in set (0.00 sec) 
 
 

Summary:

• Two Boolean fields in boolean_test of the test data table. When the written value exceeds the storage range of the signed integer TINYINT data type, the warning message of field value truncation is displayed;
• The boolean_test field in the test data table can be written to the TRUE or FALSE field that expresses the Boolean value. No error is reported and it does not need to be enclosed by single quotation marks or double quotation marks;
• You can write a non-Boolean value to the boolean_test field of the test data table. MySQL does not receive any error or warning information;

C). display the data written into the boolean_test table.

 
 

  
  
root@localhost : test 06:31:33> SELECT * FROM boolean_test;  
  
  
   
  
  
+----+-------------+-----------+  
  
  
   
  
  
| ID | Online_Flag | Lock_Flag |  
  
  
   
  
  
+----+-------------+-----------+  
  
  
   
  
  
|  1 |           1 |         0 |  
  
  
   
  
  
|  2 |           1 |         0 |  
  
  
   
  
  
|  3 |           2 |        -1 |  
  
  
   
  
  
|  4 |        -128 |       127 |  
  
  
   
  
  
|  5 |        -128 |       127 |  
  
  
   
  
  
+----+-------------+-----------+  
  
  
   
  
  
5 rows in set (0.00 sec) 
 
 

Summary:

By checking the data in the boolean_test test table, we can find that the values stored in the MySQL database are different from the INSERT statements written into the data, as shown in:

• The written Boolean value TRUE is converted to 1, and FALSE is converted to 0;
• Values that exceed the upper and lower limits of TINYINT data type storage are automatically truncated;
• Boolean bool/BOOLEAN functions are equivalent to tiny integer TINYTINT;