The difference between Bool/boolean and TINYINT in MySQL-Test notes

(i) data type test

(1). Boolean type Bool/boolean and micro-integral tinyint

a). To 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 see that the SQL syntax for the field type bool or boolean,mysql is allowed to pass, and we'll look through the show command to create a good table structure:

1. Row ***************************

Table:boolean_test

Create table:create Table ' boolean_test ' (

' ID ' int (one) 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 find the difference between the structure definition of the table boolean_test and the table structure definition in the database, comparing with the manual input:

The MySQL database converts the field's data type Bool/boolean to tinyint (1) by default;
MySQL database automatically completed data type conversion process, did not give any error or warning information prompts;



B. Writing of 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:

Test data Table Boolean_test 2 field Boolean Type field, the write value is more than the signed integer tinyint data type storage scope, there is a warning message of field value truncation;
Fields that are boolean_test to a test datasheet can write true or False to an expression Boolean value without an error, and do not need to be enclosed in single or double quotes;
Fields that are boolean_test to the test datasheet can write values that are not expressed in Boolean types, and the MySQL database will not have any errors or warning messages;




c). Display data written to table Boolean_test

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 looking at the data from the test table boolean_test, you can find that there are some differences between the values stored in the MySQL database and the INSERT statements written by the data, which are reflected in the following:

The Write Boolean type value true is converted to a 1,false conversion of 0;
A value exceeding the upper and lower limits of the tinyint data type is automatically truncated;
The function of Boolean type Bool/boolean is equivalent to micro-integer tinytint;