CODEWITHSUNDEEP
mysqlboolean
J.L. Yokata
J.L. Yokata

Reputation: 115

MYSQL boolean types

Can someone confirm if what i think about boolean is right?

  1. There is NO boolean type within tables. Instead, mysql use numeric types.
  2. There IS boolean type within mysql program.

SO if you make a table with BOOL column, mysql will just use TINYINT(1) and you can insert TRUE or 1 that both will have the same result.

But if you use a operator or function that only accept boolean type as parameter like the operator "IS" ( IS boolean_value ) you HAVE TO use TRUE,FALSE (or even UNKNOWN)! you CAN NOT PASS 1 as an argument!

select 1 IS 1; #syntax error

select 1 IS TRUE; #return 1

is that right?

Upvotes: 0

Views: 1968

Answers (1)

Solarflare
Solarflare

Reputation: 11106

This comes down to some definitions in the sql standard.

The sql standard defines a truth value as TRUE | FALSE | UNKNOWN.

The IS-operator is defined for the following two cases:

  • A boolean test: <boolean primary> [ IS [ NOT ] <truth value> ] (a boolean primary is something that can be evaluated to a boolean)
  • A test for the null-value: <rowvalue> IS NULL

So the correct syntax for IS requires it to be followed by either a truth value (so the words TRUE, FALSE or UNKNOWN), or NULL. E.g. select 1 IS (1=1); is not allowed either, because the result of (1=1) is a boolean, not a truth value.

This will be true for any database system that supports IS (if it does not want to violate the sql standard). So the reason you cannot use anything but one of the 4 words after IS is simply that the syntax forbids it, and is not yet an indication of how booleans are implemented. Because IS is optional in the boolean test, it's remaining main purpose is the test for the null-value, so apart from IS NULL, you usually don't ever need to use IS.

Generally, if a function requires a specific datatype (e.g. a boolean), you have to provide that datatype. But depending on the database system, there might be automatic type casting. And while some database systems do have seperate boolean datatypes, MySQL implements 1 | 0 | null as the result of a boolean expression (which is by definition a boolean):

In SQL, all logical operators evaluate to TRUE, FALSE, or NULL (UNKNOWN). In MySQL, these are implemented as 1 (TRUE), 0 (FALSE), and NULL. [...] MySQL evaluates any nonzero, non-NULL value to TRUE.

and uses this wherever booleans are used, e.g. comparison operators, and even casts it correctly if needed:

Comparison operations result in a value of 1 (TRUE), 0 (FALSE), or NULL. These operations work for both numbers and strings. Strings are automatically converted to numbers and numbers to strings as necessary.

Consequential, for MySQL the boolean datatype is a synonym for TINYINT(1).

So if you can use 1 infront of IS (where a boolean expression is required) will depend on the database system. MySQL allows you to use 1, PostgreSQL doesn't (but would allow select (1=1) IS TRUE), and MSSQL doesn't even support IS.

Upvotes: 2

Related Questions