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

Source: Internet
Author: User
Tags character set

(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;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.