MySQL database data type Bool/boolean and tinyint test summary

From:http://database.51cto.com/art/201203/323863.htm

Many people on the web are consulting MySQL for Boolean types? The MySQL database does provide a Boolean type that can be written as a Boolean type bool or Boolean type Boolean, but does the MySQL database data type have the same Boolean type as other database products? This article reveals the true face of the MySQL Boolean type.

Ad:51cto Net + 12th salon: The beauty of big data-how to drive user experience with data

Reading guidance

Many people on the web are consulting MySQL for Boolean types? The MySQL database does provide a Boolean type that can be written as a Boolean type bool or Boolean type Boolean, but does the MySQL database data type have the same Boolean type as other database products? This article reveals the true face of the MySQL Boolean type.

(i) Data type testing

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

a). Create a test table structure

[Email protected]: 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 written in bool or boolean,mysql is allowed, and we then look at the created table structure by the show command:

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 creating table boolean_test by manual input and the definition of the table structure in the database:

The MySQL database converts the field's data type Bool/boolean to tinyint (1) by default;

MySQL database automatic completion of the data type conversion process, no error or warning message prompts;

b). Write the test data

[Email protected]: Test 05:12:58> INSERT into boolean_test (online_flag,lock_flag) VALUES (TRUE,  FALSE);

Query OK, 1 row Affected (0.00 sec)

[Email protected]: Test 05:13:58> INSERT into boolean_test (online_flag,lock_flag) VALUES (1,0);

Query OK, 1 row Affected (0.00 sec)

[Email protected]: Test 05:14:04> INSERT into boolean_test (online_flag,lock_flag) VALUES (2,-1);

Query OK, 1 row Affected (0.00 sec)

[Email protected]: Test 05:14:11> INSERT into boolean_test (online_flag,lock_flag) VALUES (-128,127);

Query OK, 1 row Affected (0.00 sec)

[Email protected]: 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)

[Email protected]: 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, when the value written exceeds the signed integer tinyint data type storage range, there is a warning message that the field value is truncated;

A field that is boolean_test to a test data table can be written to express a Boolean value of TRUE or false without an error, or enclosed in single or double quotation marks;

Fields boolean_test to the test data table can be written to numeric values of non-expression Boolean types, and there will be no error or warning message from the MySQL database;

c). Display data written to table Boolean_test

[Email protected]: 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 in the test table boolean_test, you can see that there are some differences between the values stored in the MySQL database and the INSERT statements written by the data, as shown in:

Writes a Boolean value of True to convert 1,false to 0;

Values that exceed the upper and lower limits stored by the tinyint data type are automatically truncated;

The function of Boolean type Bool/boolean is equivalent to micro-integer tinytint;

Original link: http://www.mysqlops.com/2012/03/15/mysql-boolean-tinyint.html

"Editor's recommendation"

MySQL Source learning: MDL Dictionary Lock

MySQL Cluster development environment Concise deployment

Analysis of four different queries of MySQL

Analysis of four different queries of MySQL

Introduction to the features and usage of MySQL memory tables

MySQL database data type Bool/boolean and tinyint test summary