Booleans In MariaDB and MySQL

There are two things I want a boolean data-type to do:

• Most importantly, I want the content of the column to be restricted to: true, false and unknown (null). If any other value is used, that will be a data quality error, because we don't know what it means. The database must prevent anything but the three legitimate states.
• It is nice to be able to express conditions such as "where policy.cancelled" rather than, say, "where policy.cancelled = 'true'". This is a minor convenience issue.

MariaDB and MySQL have various options for boolean-like data-types:

• bit
• bit(1)
• bool
• boolean
• tinyint(1)

All of these are half-baked hints at a boolean data-type. You can use the values TRUE (stored as 1) and FALSE (stored as 0). But the results aren't always what I would expect. Here are some tests:

The following tests were carried out in 11.8.2-MariaDB-ubu2404 and MySQL Community Edition 5.7.

Set-up

    create table test_boolean
    (
        id              varchar(1),
        bit_type        bit,
        bool_type       bool,
        boolean_type    boolean,
        tinyint_1_type  tinyint(1)
    );
    

Note that some inserts use true and false keywords to mean 1 and 0, as noted above.

    insert into test_boolean (id, bit_type, bool_type, boolean_type, tinyint_1_type) values ('A', 0, 0, 0, 0);
    insert into test_boolean (id, bit_type, bool_type, boolean_type, tinyint_1_type) values ('B', false, false, false, false);
    insert into test_boolean (id, bit_type, bool_type, boolean_type, tinyint_1_type) values ('C', 1, 1, 1, 1);
    insert into test_boolean (id, bit_type, bool_type, boolean_type, tinyint_1_type) values ('D', true, true, true, true);
    insert into test_boolean (id, bit_type, bool_type, boolean_type, tinyint_1_type) values ('E', null, 2, 2, 2);
    insert into test_boolean (id, bit_type, bool_type, boolean_type, tinyint_1_type) values ('F', null, null, null, null);
    

Checking What We Loaded

    select * from test_boolean;
    +----+-----------+-----------+--------------+----------------+
    | id | bit_type  | bool_type | boolean_type | tinyint_1_type |
    +----+-----------+-----------+--------------+----------------+
    | A  |           |         0 |            0 |              0 |
    | B  |           |         0 |            0 |              0 |
    | C  |          |         1 |            1 |              1 |
    | D  |          |         1 |            1 |              1 |
    | E  | null      |         2 |            2 |              2 |
    | F  | null      |      null |         null |           null |
    +----+-----------+-----------+--------------+----------------+
    6 rows in set (0.00 sec)
    

The column with data-type "bit" is mis-aligned. This appears to be because it is returning a byte-value of 1, which is ASCII SOH (Start of Header), which is an obsolete control character.

Using bit Data-type As A Boolean Condition In The Where Clause

    select * from test_boolean where bit_type;
    +----+-----------+-----------+--------------+----------------+
    | id | bit_type  | bool_type | boolean_type | tinyint_1_type |
    +----+-----------+-----------+--------------+----------------+
    | C  |          |         1 |            1 |              1 |
    | D  |          |         1 |            1 |              1 |
    +----+-----------+-----------+--------------+----------------+
    2 rows in set (0.00 sec)
    

This returns the correct rows, but is still says "undefined" for the bit value.

    select * from test_boolean where not bit_type;
    +----+-----------+-----------+--------------+----------------+
    | id | bit_type  | bool_type | boolean_type | tinyint_1_type |
    +----+-----------+-----------+--------------+----------------+
    | A  |           |         0 |            0 |              0 |
    | B  |           |         0 |            0 |              0 |
    +----+-----------+-----------+--------------+----------------+
    2 rows in set (0.00 sec)
    

This also returns the correct rows.

The blank bit-type is a disadvantage. Is it 0 or 1 or true or false or null. What is it?

Using boolean Data-type As A Boolean Condition In The Where Clause

    select * from test_boolean where boolean_type;
    +----+-----------+-----------+--------------+----------------+
    | id | bit_type  | bool_type | boolean_type | tinyint_1_type |
    +----+-----------+-----------+--------------+----------------+
    | C  |          |         1 |            1 |              1 |
    | D  |          |         1 |            1 |              1 |
    | E  | null      |         2 |            2 |              2 |
    +----+-----------+-----------+--------------+----------------+
    3 rows in set (0.00 sec)
    

    select * from test_boolean where not boolean_type;
    +----+-----------+-----------+--------------+----------------+
    | id | bit_type  | bool_type | boolean_type | tinyint_1_type |
    +----+-----------+-----------+--------------+----------------+
    | A  |           |         0 |            0 |              0 |
    | B  |           |         0 |            0 |              0 |
    +----+-----------+-----------+--------------+----------------+
    2 rows in set (0.00 sec)
    

The boolean data-type can clearly take values other than zero or 1 (it has the value 2 for id "E"). So this fails the data quality issue. We could say that zero means false and anything else means true. But how did a value of 2 get into this column? Did the user actually mean it to be true? I would check with the users. We can see that a boolean with value 1 is processed as "true" and so is a boolean with value 2. Since the boolean values are true, false (and possibly null), a boolean with the value "true" should be equal to another boolean with the value "true", no matter how "true" is represented in the database. Let's see if this is what happens:

    select
        a.id,
        b.id,
        a.boolean_type as a_boolean_type,
        b.boolean_type as b_boolean_type
    from
        test_boolean a
        join
        test_boolean b
        on
            a.boolean_type and
            b.boolean_type
    where
        a.id < b.id;

    +----+----+----------------+----------------+
    | id | id | a_boolean_type | b_boolean_type |
    +----+----+----------------+----------------+
    | C  | D  |              1 |              1 |
    | C  | E  |              1 |              2 |
    | D  | E  |              1 |              2 |
    +----+----+----------------+----------------+
    3 rows in set (0.00 sec)
    

This does support the minor convenience issue and confirms what we saw before: rows with id = "C", "D" and "E" all have boolean_type set to "true", but ...

    select
        a.id as a_id,
        b.id as b_id,
        a.boolean_type as a_boolean_type,
        b.boolean_type as b_boolean_type
    from
        test_boolean a
        join
        test_boolean b
        on
            a.boolean_type and
            b.boolean_type = a.boolean_type
    where
        a.id < b.id;

    +------+------+----------------+----------------+
    | a_id | b_id | a_boolean_type | b_boolean_type |
    +------+------+----------------+----------------+
    | C    | D    |              1 |              1 |
    +------+------+----------------+----------------+
    1 row in set (0.00 sec)
    

... the "true" value in the row with id "E" does not equal the "true" value in rows "C" and "D".

Using bool Data-type As A Boolean Condition In The Where Clause

    select * from test_boolean where bool_type;
    +----+-----------+-----------+--------------+----------------+
    | id | bit_type  | bool_type | boolean_type | tinyint_1_type |
    +----+-----------+-----------+--------------+----------------+
    | C  |          |         1 |            1 |              1 |
    | D  |          |         1 |            1 |              1 |
    | E  | null      |         2 |            2 |              2 |
    +----+-----------+-----------+--------------+----------------+
    3 rows in set (0.00 sec)
    

    select * from test_boolean where not bool_type;
    +----+-----------+-----------+--------------+----------------+
    | id | bit_type  | bool_type | boolean_type | tinyint_1_type |
    +----+-----------+-----------+--------------+----------------+
    | A  |           |         0 |            0 |              0 |
    | B  |           |         0 |            0 |              0 |
    +----+-----------+-----------+--------------+----------------+
    2 rows in set (0.00 sec)
    

"bool" is simply a synonym for "boolean". That's OK, but I prefer languages that allow only one way of doing one thing. If I have to type three extra characters, I don't care.

Using tinyint(1) Data-type As A Boolean Condition In The Where Clause

    select * from test_boolean where tinyint_1_type;
    +----+-----------+-----------+--------------+----------------+
    | id | bit_type  | bool_type | boolean_type | tinyint_1_type |
    +----+-----------+-----------+--------------+----------------+
    | C  |          |         1 |            1 |              1 |
    | D  |          |         1 |            1 |              1 |
    | E  | null      |         2 |            2 |              2 |
    +----+-----------+-----------+--------------+----------------+
    3 rows in set (0.00 sec)

    select * from test_boolean where not tinyint_1_type;
    +----+-----------+-----------+--------------+----------------+
    | id | bit_type  | bool_type | boolean_type | tinyint_1_type |
    +----+-----------+-----------+--------------+----------------+
    | A  |           |         0 |            0 |              0 |
    | B  |           |         0 |            0 |              0 |
    +----+-----------+-----------+--------------+----------------+
    2 rows in set (0.00 sec)
    

"boolean" is also a synonym for "tinyint(1)". That's OK, but I prefer languages that allow only one way of doing one thing. I really don't want three ways of doing the same thing.

MariaDB and MySQL are almost identical, and both are very widely used. MariaDB is a good database (as MySQL was before Oracle got hold of it). When I use MariaDB or MySQL, I avoid the bit/boolean/bool/tinyint(1) options in MySQL. I just use a suitably-named column with values that I define, usually "Y" and "N", and I write the conditions out in full. This seems to me to be the best choice for avoiding confusion.