MYSQL boolean types

Question

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

There is NO boolean type within tables. Instead, mysql use numeric types.
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?

I almost never see IS being used in that manner in any of the RDBMSes I have worked with; most often I see someFieldUsedForAFlagValue <> 0. — Uueerdo
– Uueerdo, Commented Mar 23, 2017 at 21:07
Interesting. This is the first time I've seen any distinction between TRUE and 1. Like @Uueerdo said, it's not very common anyway. — shmosel
– shmosel, Commented Mar 23, 2017 at 21:11

Pang · Accepted Answer · 2018-03-26 04:32:17Z

2

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.

edited Mar 26, 2018 at 4:32

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Mar 24, 2017 at 2:42

Solarflare

11.2k2 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

J.L. Yokata Over a year ago

so this is all about syntax. there is no boolean type in mysql, instead they use number 1 0. is that right?

Solarflare Over a year ago

I added some details about this. Yes, there is no dedicated boolean type in MySQL tables, and yes, the result of a boolean expression, which is by definition a boolean, is implemented as numbers. It is interpretation if that means "there is no boolean type" in MySQL, as you just cannot distinguish it from numbers. For practical purposes you can probably say it that way, but in an exam or a theoretical discussion you should be aware of that distinction though.

J.L. Yokata Over a year ago

Oh! I understand now! Thanks for the clear explanation! =D

Collectives™ on Stack Overflow

MYSQL boolean types

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related