Is there a boolean literal in SQLite?

I know about the boolean column type, but is there a boolean literal in SQLite? In other languages, this might be true or false. Obviously, I can use 0 and 1, but I tend to avoid so-called "magic numbers" where possible.

From this list, it seems like it might exist in other SQL implementations, but not SQLite. (I'm using SQLite 3.6.10, for what it's worth.)

73068 次浏览
小开

1.1 Boolean Datatype

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

Docs

小开
最佳答案

From section 1.1 Boolean Datatype of the docs:

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

So it looks like you are stuck with 0 and 1.

小开

There is no boolean data type. There are only 5 types, listed here. Integers can be stored with various widths on disk, the smallest being 1 byte. However, this is an implementation detail:

"But as soon as INTEGER values are read off of disk and into memory for processing, they are converted to the most general datatype (8-byte signed integer)."

Given that, it is not surprising there are no boolean literals.

小开

There are only 5 datatypes supported in SQLite3.

From the Official SQLite3 doc. "Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:

NULL. The value is a NULL value.


INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value."

If you are going to store 1s and 0s, then SQLite wil use 1 byte if storage. Which is not bad. Official Doc link :- http://www.sqlite.org/datatype3.html

小开

BOOLEAN -> NUMERIC (Affinity)

Column Affinity

SQLite supports the concept of type affinity on columns. Any column can still store any type of data but the preferred storage class for a column is called its affinity. Each table column in an SQLite3 database is assigned one of the following type affinities: Affinity Description

  • TEXT This column stores all data using storage classes NULL, TEXT or BLOB.
  • NUMERIC This column may contain values using all five storage classes.
  • INTEGER Behaves the same as a column with NUMERIC affinity with an exception in a CAST expression.
  • REAL Behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation
  • NONE A column with affinity NONE does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another.

Boolean Datatype:

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

@moiing-duck "On the flip side, SQLite supports datetimes, despite not being one of those five types, so this answer is inconclusive"

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

小开

I noticed in sqlite for android, I can declare a Boolean column type with no error and its seems to work fine. I also tried defining the column as 'int' and storing java boolean values. I downloaded the db and confirmed I'm writing "true" in the column. I think it just works.

小开

The question is explicitly not about the column type (i.e storage-wise) but the use of TRUE and FALSE literals (i.e. parser-wise), which are SQL-compliant as per the PostgreSQL keywords documentation (which happens to also include SQL-92, SQL:2008 and SQL:2011 columns in the reference table).

The SQLite documentation lists all supported keywords, and this list contains neither TRUE nor FALSE, hence SQLite sadly is non-compliant in that regard.

You can also test it easily and see how the parser barfs as it wants the token to be a column name:

$ sqlite3 :memory:
SQLite version 3.14.0 2016-07-26 15:17:14
sqlite> CREATE TABLE foo (booleanish INT);
sqlite> INSERT INTO foo (booleanish) VALUES (TRUE);
Error: no such column: TRUE
小开

SQLite doesn't have Boolean type, you should use INTEGER with 0 is false and 1 is true

小开

Is there a boolean literal in SQLite?

As stated in Justin Ethier's answer, SQLite does not have specific data type for boolean. But starting from SQLite 3.23.0 it supports true/false literals:

  1. Recognize TRUE and FALSE as constants. (For compatibility, if there exist columns named "true" or "false", then the identifiers refer to the columns rather than Boolean constants.)

  2. Support operators IS TRUE, IS FALSE, IS NOT TRUE, and IS NOT FALSE. 

SELECT true AS t, false AS f;


SELECT 'condition is true'
WHERE 1 IS NOT FALSE;


CREATE TABLE a (id INT, b BOOLEAN DEFAULT(TRUE));
INSERT INTO a(id) VALUES(100);
SELECT * FROM a;
-- id  b
-- 100 1


SELECT * FROM a WHERE true;
-- id  b
-- 100 1

dbfiddle.com demo

小开

You can use BOOLEAN when creating a table:

sqlite3 :memory:
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> CREATE TABLE test (mybool BOOLEAN);

But this is not a real datatype and on the SQL description of the table we will have something like this:

"mybool" BOOLEAN NOT NULL CHECK(mybool IN(0,1))

Basically we create an SQL constrain constrain that the value should be 0 or 1. And therefore using TRUE/FALSE will pop an error:

sqlite> INSERT INTO test(mybool) VALUES (TRUE);
Error: no such column: TRUE

So, we can use the key word BOOLEAN but have to use 0/1 since it is not a "real" datatype.

When working with sqlalchemy we can use the datatype BOOLEAN without any problems

小开

SQLite has TRUE and FALSE literal as shown here :

https://www.sqlite.org/syntax/literal-value.html

(yeah, long time to respond :)

小开

As everyone else has pointed out, SQLite does not support a specific boolean storage type, and the OP specifically acknowledges this fact. However, this is completely independent of whether SQLite supports boolean literals and comprehensions.

For anyone wondering, the answer is YES, since SQLite 3.23 you can do boolean comprehensions with the boolean literals TRUE and FALSE, and it will work how you expect.

For example, you can do:

SELECT * FROM blah WHERE some_column IS FALSE;

SELECT * FROM blah WHERE some_column IS TRUE;

and it will work how you expect if you are using 0 for false and 1 for true.

From my testing, here is how SQLite matches various values:

  • Any non-zero NUMERIC, INTEGER, or REAL: IS TRUE
  • Any zero NUMERIC, INTEGER, or REAL: IS FALSE
  • A null value: IS NULL. Does not match IS TRUE or IS FALSE.
  • Any TEXT that does not parse to a numeric value: IS FALSE. Even values like "t", "TRUE", "true", "True" still match IS FALSE
  • TEXT that looks like a number (eg "0", "1", "5"): Behaves like NUMERIC, listed above.
  • BLOB: Untested.

提交答案