Small Test of the boolean Type of sqlite Database

There is no separate Boolean Storage Class in the sqlite database. The Booean value is stored as 0 (false) and 1 (true). After my short-term test, it is shown that there are three statuses of boolean, 0 (false) 1 (true) and null, as shown in,

The following insert statement can be successfully inserted.

Select * from stu where flag = "Database"

The row named a9 is queried.

Insert into stu (name, flag) values ('a1', 'true'); -- 0 insert into stu (name, flag) values ('a2 ', 'ture '); -- 0 insert into stu (name, flag) values ('a3 ', 1); -- 1 insert into stu (name, flag) values ('a4', 'null '); -- 0 insert into stu (name, flag) values ('a5 ', '1'); -- 1 insert into stu (name, flag) values ('a6', null ); -- nullinsert into stu (name, flag) values ('a7 ', '2'); -- 1 insert into stu (name, flag) values ('a8', 15 ); -- 1 insert into stu (name, flag) values ('a9 ', "Database"); -- 0

When exporting the database, you can find that the SQL statement executed is like this,

insert into [stu] values('a1', 0);insert into [stu] values('a2', 0);insert into [stu] values('a3', 1);insert into [stu] values('a4', 0);insert into [stu] values('a5', 1);insert into [stu] values('a6', null);insert into [stu] values('a7', 1);insert into [stu] values('a8', 1);insert into [stu] values('string', 0);insert into [stu] values('string2', 0);insert into stu (name,flag) values ('a9',0); --0

In this case, we assume that sqlite uses the boolean type data inserted by the stable type storage. However, during the extraction, the inserted string type data is converted to the int type for use.

Therefore, we can draw the following conclusion:

Ps: I have not yet had a deep understanding of it. Currently, it is the conclusion of practical testing. It is purely a guess. If you have any suggestion, please let us know.

--------- Updated on December 31, April 12, 2014 -------

Reference the definition of the ing data type in the T-SQL:

"Bit column is used to obtain the true and false data types, or the (yes) and no (no) data types. Stores 0 or 1 in a row. The integer that accepts values outside of 0 or 1, but is always interpreted as 1. Bit Data Columns cannot be NULL and cannot be indexed ."

For more information, refer to the article I reproduced: using UDF for easy migration