MySQL で本物のブール値を格納する方法

CREATE TABLE accounts_tinyint (id INT PRIMARY KEY, active BOOL);

INSERT INTO accounts_tinyint VALUES (1, TRUE), (2, FALSE), (3, 5), (4, -1);

SELECT id, active FROM accounts_tinyint;

+----+--------+
| id | active |
+----+--------+
|  1 |      1 |
|  2 |      0 |
|  3 |      5 |
|  4 |     -1 |
+----+--------+

SELECT COUNT(*) AS rows_that_are_truthy FROM accounts_tinyint WHERE active;
SELECT COUNT(*) AS matches_equals_true  FROM accounts_tinyint WHERE active = TRUE;

+----------------------+
| rows_that_are_truthy |
+----------------------+
|                    3 |
+----------------------+
+---------------------+
| matches_equals_true |
+---------------------+
|                   1 |
+---------------------+

INSTALL EXTENSION vsql_boolean;

CREATE TABLE accounts (
    id     INT PRIMARY KEY,
    email  VARCHAR(64),
    active STRICTBOOL NOT NULL DEFAULT 'false'
);

INSERT INTO accounts (id, email, active) VALUES
    (1, 'ana@example.com',  'true'),
    (2, 'ben@example.com',  'no'),
    (3, 'cleo@example.com', 'on'),
    (4, 'dev@example.com',  '0');

INSERT INTO accounts (id, email) VALUES (5, 'eve@example.com');

SELECT id, email, active FROM accounts;

+----+------------------+--------+
| id | email            | active |
+----+------------------+--------+
|  1 | ana@example.com  | true   |
|  2 | ben@example.com  | false  |
|  3 | cleo@example.com | true   |
|  4 | dev@example.com  | false  |
|  5 | eve@example.com  | false  |
+----+------------------+--------+

INSERT INTO accounts (id, email, active) VALUES (6, 'f@example.com', 'maybe');

ERROR 1366 (HY000): Incorrect STRICTBOOL value: 'maybe' for column 'active' at row 1

SELECT email FROM accounts WHERE active = 'true';

+------------------+
| email            |
+------------------+
| ana@example.com  |
| cleo@example.com |
+------------------+

SELECT boolean_sum(active) AS active_accounts,
       ROUND(boolean_avg(active), 2) AS active_share,
       COUNT(*) AS total
FROM accounts;

+-----------------+--------------+-------+
| active_accounts | active_share | total |
+-----------------+--------------+-------+
|               2 |          0.4 |     5 |
+-----------------+--------------+-------+

CREATE TABLE survey (id INT PRIMARY KEY, agreed STRICTBOOL);
INSERT INTO survey VALUES (1, 'true'), (2, 'false'), (3, NULL);

SELECT boolean_sum(agreed) AS yes,
       ROUND(boolean_avg(agreed), 2) AS share,
       COUNT(*) AS rows_total
FROM survey;

+------+-------+------------+
| yes  | share | rows_total |
+------+-------+------------+
|    1 |   0.5 |          3 |
+------+-------+------------+

CREATE INDEX idx_active ON accounts (active);

ALTER TABLE accounts_tinyint MODIFY active STRICTBOOL;

ERROR 3219 (HY000): Cannot convert column 'active' to custom type 'STRICTBOOL'

ALTER TABLE accounts_tinyint ADD COLUMN active_bool STRICTBOOL NOT NULL DEFAULT 'false';

UPDATE accounts_tinyint SET active_bool = 'true'  WHERE active <> 0;
UPDATE accounts_tinyint SET active_bool = 'false' WHERE active = 0;

SELECT id, active, active_bool FROM accounts_tinyint;

+----+--------+-------------+
| id | active | active_bool |
+----+--------+-------------+
|  1 |      1 | true        |
|  2 |      0 | false       |
|  3 |      5 | true        |
|  4 |     -1 | true        |
+----+--------+-------------+

UPDATE accounts_tinyint SET active_bool = IF(active <> 0, 'true', 'false');

ERROR 3219 (HY000): Incorrect STRICTBOOL value: cannot implicitly cast string expression. Use explicit conversion for column 'active_bool' at row 1

UPDATE accounts_tinyint SET active_bool = STRICTBOOL::from_string(IF(active <> 0, 'true', 'false'));

ALTER TABLE accounts_tinyint DROP COLUMN active;
ALTER TABLE accounts_tinyint RENAME COLUMN active_bool TO active;