MySQL 8 does not yet support the BOOLEAN type as specified in the SQL standard. There is a DDL "type" called BOOL, which is just an alias for TINYINT: create table t(b bool); select table_name, column_name, data_type, column_type from information_schema.columns where table_name = 't'; The above produces: TABLE_NAME|COLUMN_NAME|DATA_TYPE|COLUMN_TYPE| ----------|-----------|---------|-----------| t |b |tinyint |tinyint(1) | Notice … Continue reading How to Map MySQL’s TINYINT(1) to Boolean in jOOQ →

I think SQL is a very simple language, but ofcourse I'm biased.

But even a simple statement might have more complexity to it than you might think.

Do you know what the result is of this statement?

SELECT FALSE = FALSE = TRUE;

scroll down for the answer.



























The answer is: it depends.

You might expect it to return false because the 3 items in the comparison are not equal. But that's not the case.

In PostgreSQL this is the result:

postgres=# SELECT FALSE = FALSE = TRUE;
 ?column? 
----------
 t
(1 row)

So it compares FALSE against FALSE, which results in TRUE …

I always hear newer and newer exotic way to store different types of data in MySQL. People are trying to solve the problem of storing complex and not necessary strictly structured data in databases which is fine. But I never understood why to try to be tricky with the simplest datatypes. For example Booleans. You would believe it’s easy. Yes or no. But there are several different way to say in MySQL that this is a Boolean.

Possible solutions

1) The most common (consider as a best practice):

TINYINT unsigned NOT NULL

2) The trivial Boolean or Bool column type which is just a synonyms for TINYINT(1):

BOOLEAN NOT NULL DEFAULT 0

3) Declaring an enumaration with “true” and “false”:

ENUM('false', 'true') NOT NULL DEFAULT 'false'

4) The newest and weirdest I heard …