PHP小码农

Appearance

布尔类型（BOOLEAN） ​

本节讲解 PostgreSQL 的布尔类型 BOOLEAN。你将了解它的 1 字节存储、可接受的多种字面量写法、三值逻辑（TRUE / FALSE / NULL）的运算规则，以及在表设计中正确使用布尔列的实践。

前置知识

阅读本节前，建议先了解：日期时间类型选型指南

基础概念 ​

布尔类型（Boolean Type）是 SQL 标准中的三值逻辑类型，只有两个真值加上一个 NULL。PostgreSQL 的实现里它在行内固定占 1 字节。

类型名别名存储空间取值范围默认值说明
BOOLEANBOOL1 字节TRUE / FALSE / NULL无SQL 标准类型，推荐写法

与其他数据库相比有两点差异需要注意：

数据库布尔类型存储与 PostgreSQL 的差异
PostgreSQLBOOLEAN1 字节原生三值逻辑，支持 IS UNKNOWN
MySQLBOOLEAN / BOOL1 字节实为 TINYINT(1) 别名，接受 2 等任意整数
SQL ServerBIT1 字节只能存 0/1，不接受 'yes' 之类字面量
Oracle无（23c 前）1 字节（CHAR(1)+CHECK）需 NUMBER(1) 或 CHAR(1) 模拟
SQLite无整数全靠 0 / 1 约定

关键点：PostgreSQL 的 BOOLEAN 是真正的类型，不是整数别名。它不接受 2、-1 这类整数，也不会把它们静默转成 TRUE。

sql
-- 数据库驱动返回的类型名就是 bool
SELECT
  pg_typeof(TRUE)                       AS type_name,
  pg_column_size(TRUE)                  AS bytes_on_wire,
  pg_column_size(FALSE)                 AS false_bytes;
text
 type_name | bytes_on_wire | false_bytes
-----------+---------------+-------------
 boolean   |             1 |           1
(1 row)

关键术语：

  • 三值逻辑（Three-Valued Logic）：SQL 的 TRUE / FALSE / UNKNOWN 三态，NULL 参与运算时结果通常为 UNKNOWN。
  • 未知（Unknown）：NULL 在比较运算中的取值，可用 IS UNKNOWN 字面表达。
  • 短路求值（Short-Circuit Evaluation）：AND / OR 未必按书写顺序求值，优化器可重排子表达式。
  • 真值表（Truth Table）：列出操作数与运算结果对应关系的表。

语法与用法 ​

text
-- 类型声明
列名 BOOLEAN
列名 BOOL

-- 字面量（TRUE 一侧）
TRUE  | 't' | 'true'  | 'y' | 'yes' | 'on' | '1'
-- 字面量（FALSE 一侧）
FALSE | 'f' | 'false' | 'n' | 'no'  | 'off' | '0'

-- 比较与判断
flag IS TRUE          -- 结果为 TRUE 或 FALSE，永不为 NULL
flag IS NOT TRUE      -- NULL 与 FALSE 都返回 TRUE
flag IS FALSE
flag IS NOT FALSE
flag IS UNKNOWN       -- 等价于 flag IS NULL
flag = TRUE           -- NULL 输入时返回 NULL

-- 逻辑运算
a AND b
a OR b
NOT a

-- WHERE 简写
WHERE flag            -- 等价于 WHERE flag = TRUE，但 NULL 不通过
WHERE NOT flag
sql
-- 最简可运行示例
CREATE TABLE users (
  id        BIGINT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name      TEXT    NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE   -- 布尔列务必给默认值
);

INSERT INTO users (name, is_active) VALUES
  ('alice',  TRUE),
  ('bob',   'yes'),     -- 字面量写法多样
  ('carol', 'f'),
  ('dave',  NULL);      -- 未设置

SELECT id, name, is_active FROM users ORDER BY id;
text
 id | name  | is_active
----+-------+-----------
  1 | alice | t
  2 | bob   | t
  3 | carol | f
  4 | dave  |
(4 rows)

输出里 t / f 是 psql 的紧凑显示形式，NULL 显示为空。注意 'yes' 与 'f' 都被正确解析，这是 PostgreSQL 布尔输入的宽泛之处。

详细说明 ​

合法输入字面量 ​

BOOLEAN 的输入函数接受的字符串有多个别名，大小写不敏感，前后空白被忽略：

输入写法解析结果备注
TRUE / true / 'true'tSQL 标准写法，推荐
't' / 'T't首字母，常见于 CSV 导出
'y' / 'Y' / 'yes't口语化写法
'on't配置项常见写法
'1't整数风格
FALSE / 'false'fSQL 标准写法，推荐
'f' / 'F'f首字母
'n' / 'no'f口语化写法
'off'f配置项常见写法
'0'f整数风格
' true 't前后空白被忽略
'2' / '-1' / 'abc'报错不会静默转成 TRUE

't' / 'f' 与 1 / 0 都能被识别，因此 CSV 导入的兼容性很好：

sql
SELECT
  't'::BOOLEAN       AS from_t,
  'yes'::BOOLEAN     AS from_yes,
  'off'::BOOLEAN     AS from_off,
  '1'::BOOLEAN       AS from_one,
  '  TrUe  '::BOOLEAN AS from_padded;
text
 from_t | from_yes | from_off | from_one | from_padded
--------+----------+----------+----------+-------------
 t      | t        | f        | t        | t
(1 row)

但要注意一个坑：'2' 会直接报错，而不是被当成 TRUE。这与 MySQL 的 TINYINT(1) 行为截然不同：

sql
-- 错误！PostgreSQL 不接受非 0/1 的整数
SELECT '2'::BOOLEAN;
text
ERROR:  invalid input syntax for type boolean: "2"
LINE 1: SELECT '2'::BOOLEAN;
               ^

三值逻辑真值表 ​

NULL 参与逻辑运算时遵循 Kleene 三值逻辑。这是 SQL 与普通编程语言最容易产生分歧的地方。

AND（与）真值表：

a AND bb = TRUEb = FALSEb = NULL
a = TRUETRUEFALSENULL
a = FALSEFALSEFALSEFALSE
a = NULLNULLFALSENULL

OR（或）真值表：

a OR bb = TRUEb = FALSEb = NULL
a = TRUETRUETRUETRUE
a = FALSETRUEFALSENULL
a = NULLTRUENULLNULL

NOT（非）真值表：

aNOT a
TRUEFALSE
FALSETRUE
NULLNULL

用实测验证表中的几个反直觉格子：

sql
SELECT
  (TRUE  AND NULL) AS t_and_null,   -- NULL
  (FALSE AND NULL) AS f_and_null,   -- FALSE：FALSE 已足以决定结果
  (TRUE  OR  NULL) AS t_or_null,    -- TRUE：TRUE 已足以决定结果
  (FALSE OR  NULL) AS f_or_null,    -- NULL
  (NOT NULL)       AS not_null;     -- NULL
text
 t_and_null | f_and_null | t_or_null | f_or_null | not_null
------------+------------+-----------+-----------+----------
            | f          | t         |           |
(1 row)

关键规律：AND 只要有一侧是 FALSE，结果就是 FALSE；OR 只要有一侧是 TRUE，结果就是 TRUE。即使另一侧是 NULL，结论也已被确定，这就是三值逻辑里"确定的不确定"。

比较运算里的 NULL ​

NULL 表示"未知"，因此任何与它做的相等比较都返回 NULL，而不是 FALSE：

sql
SELECT
  (NULL = NULL)       AS null_eq_null,      -- NULL，不是 TRUE
  (TRUE = NULL)       AS true_eq_null,      -- NULL
  (NULL <> NULL)      AS null_ne_null,      -- NULL
  (NULL IS NULL)      AS is_null,           -- TRUE
  (NULL IS NOT TRUE)  AS is_not_true,       -- TRUE
  (NULL IS UNKNOWN)   AS is_unknown,        -- TRUE
  (NULL IS DISTINCT FROM NULL) AS is_distinct;  -- FALSE，这是个安全比较
text
 null_eq_null | true_eq_null | null_ne_null | is_null | is_not_true | is_unknown | is_distinct
--------------+--------------+--------------+---------+-------------+------------+-------------
              |              |              | t       | t           | t          | f
(1 row)

这里展示了两类写法：

  • IS TRUE / IS NOT TRUE / IS FALSE / IS UNKNOWN：谓词形式，永远返回 TRUE 或 FALSE，不会返回 NULL。
  • = / <>：比较运算符，遇到 NULL 就返回 NULL。

IS DISTINCT FROM 是"NULL 安全"的不等判断：它把 NULL 当成一个普通值来比，因此 NULL IS DISTINCT FROM NULL 为 FALSE。

NOT IN 与 NULL 的经典陷阱 ​

三值逻辑最著名的坑是 NOT IN 遇到 NULL：

sql
-- 期望：找出不在黑名单里的用户
SELECT name
FROM (VALUES ('alice'), ('bob'), ('carol')) AS u(name)
WHERE name NOT IN ('alice', NULL);
text
 name
------
(0 rows)

一行都没返回。原因是 name NOT IN ('alice', NULL) 展开为 name <> 'alice' AND name <> NULL，而后半部分的求值结果是 NULL，整个 AND 链因 FALSE AND NULL = FALSE、TRUE AND NULL = NULL 而无法为真。

正确做法是先把子查询里的 NULL 排除，或者改用 NOT EXISTS：

sql
-- 方案一：显式排除 NULL
SELECT name
FROM (VALUES ('alice'), ('bob'), ('carol')) AS u(name)
WHERE name NOT IN (SELECT v FROM (VALUES ('alice')) AS b(v) WHERE v IS NOT NULL);

-- 方案二：改用 NOT EXISTS（推荐，语义清晰且性能稳定）
SELECT u.name
FROM (VALUES ('alice'), ('bob'), ('carol')) AS u(name)
WHERE NOT EXISTS (
  SELECT 1 FROM (VALUES ('alice')) AS b(v) WHERE b.v = u.name
);
text
 name
-------
 bob
 carol
(2 rows)

短路求值不可依赖 ​

AND 与 OR 在 PostgreSQL 中允许短路，但优化器可以自由重排子表达式，因此不能靠书写顺序来防止除零之类的错误：

sql
-- 不要这样做：依赖 WHERE 的顺序来避免除零
SELECT * FROM (VALUES (1), (0)) AS t(d)
WHERE d <> 0 AND 10 / d > 5;
text
 d
---
 1
(1 row)

这次侥幸没报错，但换成 OR 或加上别的条件，优化器可能先算 10 / d：

sql
-- 错误！OR 分支可能被先求值，出现除零
SELECT * FROM (VALUES (1), (0)) AS t(d)
WHERE d = 0 OR 10 / d > 5;
text
ERROR:  division by zero

正确做法是用 CASE 显式控制求值顺序（CASE 保证按顺序求值，且不满足条件的分支不求值）：

sql
SELECT * FROM (VALUES (1), (0)) AS t(d)
WHERE CASE WHEN d = 0 THEN FALSE ELSE 10 / d > 5 END;
text
 d
---
 1
(1 row)

WHERE 简写与 IS TRUE 的区别 ​

WHERE flag 是 WHERE flag = TRUE 的语法糖，NULL 行不会通过；WHERE flag IS NOT TRUE 则会把 NULL 行也选出来：

sql
CREATE TABLE tasks_flow (
  id     INT,
  title  TEXT,
  done   BOOLEAN          -- 允许 NULL，表示"尚未确认"
);

INSERT INTO tasks_flow VALUES
  (1, '写文档', TRUE),
  (2, '写测试', FALSE),
  (3, '写注释', NULL);

-- 简写：只取明确完成的
SELECT id, title FROM tasks_flow WHERE done ORDER BY id;
text
 id |  title
----+----------
  1 | 写文档
(1 row)
sql
-- 取"未完成 + 未确认"：用 IS NOT TRUE 才能包含 NULL
SELECT id, title, done FROM tasks_flow WHERE done IS NOT TRUE ORDER BY id;
text
 id |  title  | done
----+---------+------
  2 | 写测试  | f
  3 | 写注释  |
(2 rows)

IS NOT TRUE 与 NOT done 也不等价，后者对 NULL 求值仍得 NULL：

sql
SELECT id, title FROM tasks_flow WHERE NOT done ORDER BY id;
text
 id |  title
----+---------
  2 | 写测试
(1 row)

三者的选行范围汇总：

写法TRUE 行FALSE 行NULL 行
WHERE done选中不选不选
WHERE done = TRUE选中不选不选
WHERE done IS TRUE选中不选不选
WHERE NOT done不选选中不选
WHERE done IS NOT TRUE不选选中选中
WHERE done IS NOT FALSE选中不选选中
WHERE done IS NULL不选不选选中

排序与聚合 ​

布尔值在 ORDER BY 中的顺序是 FALSE < TRUE，NULL 默认排在最后（升序时）：

sql
SELECT id, done
FROM tasks_flow
ORDER BY done ASC;
text
 id | done
----+------
  2 | f
  1 | t
  3 |
(3 rows)

如果希望 TRUE 排前面（比如"已完成"优先展示），用 ORDER BY done DESC 或显式 ORDER BY done DESC NULLS LAST。

聚合布尔列时，通常不需要 sum，而是用 count(*) FILTER：

sql
SELECT
  count(*)                                   AS total,
  count(*) FILTER (WHERE done)                AS done_count,
  count(*) FILTER (WHERE done IS NOT TRUE)    AS pending_count,
  count(*) FILTER (WHERE done IS NULL)        AS unknown_count,
  round(100.0 * count(*) FILTER (WHERE done) / count(*), 1) AS done_pct
FROM tasks_flow;
text
 total | done_count | pending_count | unknown_count | done_pct
-------+------------+---------------+---------------+----------
     3 |          1 |             2 |             1 |     33.3
(1 row)

PostgreSQL 也提供了布尔聚合函数 bool_and / bool_or，以及 SQL 标准别名 every（等价于 bool_and）。它们把多行布尔压成一个布尔，用于"是否全部满足""是否存在满足"这类判断：

sql
-- bool_and / bool_or / every：把多行布尔压成一个布尔
SELECT
  bool_and(done) AS all_done,     -- 非 NULL 值全为 TRUE 才返回 TRUE
  bool_or(done)  AS any_done,     -- 非 NULL 值中有 TRUE 就返回 TRUE
  every(done)    AS every_alias   -- every 等价于 bool_and
FROM tasks_flow;
text
 all_done | any_done | every_alias
----------+----------+-------------
 f        | t        | f
(1 row)

注意 bool_and / bool_or 只跳过 NULL，不把 NULL 当 FALSE：tasks_flow 里 done 是 TRUE、FALSE、NULL，bool_and 因存在显式 FALSE 而返回 f，与那个 NULL 无关。但若输入行全是 NULL（或被 WHERE 过滤成空集），两者都返回 NULL 而不是 FALSE：

sql
-- 全是 NULL 时返回 NULL，不是 FALSE
SELECT bool_and(done) AS all_null_and, bool_or(done) AS all_null_or
FROM tasks_flow WHERE done IS NULL;
text
 all_null_and | all_null_or
--------------+-------------
              |
(1 row)

需要把"空集"当作 FALSE 时，显式套一层 coalesce：

sql
-- 空集也返回 FALSE，便于直接用于业务判断
SELECT coalesce(bool_or(done), FALSE) AS any_done_or_false
FROM tasks_flow WHERE done IS NULL;
text
 any_done_or_false
-------------------
 f
(1 row)

为什么不要用 CHAR(1) 代替 BOOLEAN ​

历史项目里常见用 CHAR(1) 存 'Y' / 'N'，或用 SMALLINT 存 0 / 1。这两种做法都有明确代价：

方案存储可存非法值类型语义客户端映射索引体积
BOOLEAN1 字节否明确布尔类型最小
CHAR(1)1 字节是（可存 'X'）模糊字符串，需转换相同
SMALLINT2 字节是（可存 2）模糊整数，需转换更大
TEXT1+ 字节是（可存 'maybe'）模糊字符串，需转换更大

CHAR(1) 的核心问题不是空间，而是需要额外 CHECK 约束才能保证数据合法，而且应用层要写 flag == 'Y' 这类判断：

sql
-- 不推荐：字符串模拟布尔，需要额外的 CHECK 和转换逻辑
CREATE TABLE legacy_flags (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  is_valid CHAR(1) NOT NULL DEFAULT 'Y'
      CHECK (is_valid IN ('Y', 'N'))
);

-- 查询还得用字符串比较，容易写成 'y' 而匹配不到
SELECT * FROM legacy_flags WHERE is_valid = 'Y';
sql
-- 推荐：原生 BOOLEAN，类型本身就是约束
CREATE TABLE modern_flags (
  id       BIGINT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  is_valid BOOLEAN NOT NULL DEFAULT TRUE
);

SELECT * FROM modern_flags WHERE is_valid;

CHAR(1) 唯一的"优势"是与某些老旧系统的字符协议一致，但这属于集成层的转换职责，不该渗透到数据模型里。

布尔列上的索引与部分索引 ​

布尔列基数只有 2（加 NULL 也才 3），单独建 B-tree 索引通常没有意义——优化器多半会直接顺序扫描。真正有用的是部分索引（Partial Index）：

sql
-- 无意义：只有两种值，索引选择性极差
-- CREATE INDEX idx_users_active ON users (is_active);

-- 有意义：只为活跃用户建索引，索引体积小、命中率高
CREATE INDEX idx_users_active_email
    ON users (name)      -- 或 email 等真正有选择性的列
    WHERE is_active;

部分索引与上节的 WHERE flag 简写正好配套：查询条件要能"蕴含"索引的 WHERE 子句，优化器才会用上它。

sql
-- 能用上 idx_users_active_email
SELECT name FROM users WHERE is_active AND name LIKE 'a%';

-- 用不上：is_active IS NOT TRUE 与 WHERE is_active 不互相蕴含
SELECT name FROM users WHERE is_active IS NOT TRUE AND name LIKE 'a%';

如果要让"软删除"业务高效，常见做法是只给未删除行建唯一索引：

sql
-- 软删除场景：只对未删除行做唯一约束
CREATE UNIQUE INDEX uq_orders_no_alive
    ON orders_soft (order_no)
    WHERE NOT is_deleted;

这样被软删除的订单号可以被重复使用，而未删除的订单号保持唯一。

布尔列还是状态列 ​

一个字段只有"是/否"两态时用 BOOLEAN 最合适。但三态以上的业务不要硬塞成多个布尔列：

  • 两个互斥布尔列（is_draft + is_published）会产生 (FALSE, FALSE)、(TRUE, TRUE) 等非法组合，需要额外 CHECK 约束维持一致性。
  • 一个状态列（status TEXT CHECK (status IN ('draft','published','archived'))）天然保证互斥，扩展新状态也无需改表结构。

判断标准：

场景推荐设计
真正只有两态（is_deleted、is_verified）BOOLEAN NOT NULL DEFAULT FALSE
需要"未确认"第三态BOOLEAN（可空），查询用 IS NOT TRUE
三态以上互斥状态（草稿/已发布/已归档）TEXT + CHECK 枚举
多个可同时为真的开关多个 BOOLEAN 列（各自独立，不互斥）
数量很多的开关（权限位）BOOLEAN 列或 JSONB + GIN 索引

实战示例 ​

下面用一个完整的"用户通知偏好"系统演示布尔列的设计、约束、索引与统计。

第 1 步：建表 ​

sql
DROP TABLE IF EXISTS notify_prefs;

CREATE TABLE notify_prefs (
  id            BIGINT      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_name     TEXT        NOT NULL,
  -- 业务开关，必须给默认值，禁止 NULL
  email_enabled BOOLEAN     NOT NULL DEFAULT TRUE,
  sms_enabled   BOOLEAN     NOT NULL DEFAULT FALSE,
  push_enabled  BOOLEAN     NOT NULL DEFAULT TRUE,
  -- 允许 NULL：NULL 表示"用户未表态"，与 FALSE（明确关闭）区分
  digest_enabled BOOLEAN,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  -- 至少开启一个渠道，否则配置无意义
  CONSTRAINT chk_at_least_one
      CHECK (email_enabled OR sms_enabled OR push_enabled)
);

注意 CHECK (email_enabled OR sms_enabled OR push_enabled)——由于三列都是 NOT NULL，这个约束的求值不会产生 NULL，逻辑可靠。

第 2 步：插入数据 ​

sql
INSERT INTO notify_prefs (user_name, email_enabled, sms_enabled, push_enabled, digest_enabled)
VALUES
  ('alice', TRUE,  TRUE,  TRUE,  TRUE),
  ('bob',   TRUE,  FALSE, TRUE,  FALSE),
  ('carol', FALSE, TRUE,  FALSE, NULL),   -- 未表态
  ('dave',  TRUE,  FALSE, FALSE, NULL);

第 3 步：验证 CHECK 约束拦截非法数据 ​

sql
-- 错误！三个渠道全关，违反 chk_at_least_one
INSERT INTO notify_prefs (user_name, email_enabled, sms_enabled, push_enabled)
VALUES ('eve', FALSE, FALSE, FALSE);
text
ERROR:  new row for relation "notify_prefs" violates check constraint "chk_at_least_one"
DETAIL:  Failing row contains (5, eve, f, f, f, null, 2026-09-22 10:30:00.123456+08).

约束在数据库层挡住了逻辑错误，不依赖应用层记得校验。

第 4 步：按渠道统计开启率 ​

sql
SELECT
  count(*)                                          AS total_users,
  count(*) FILTER (WHERE email_enabled)              AS email_on,
  count(*) FILTER (WHERE sms_enabled)                AS sms_on,
  count(*) FILTER (WHERE push_enabled)               AS push_on,
  count(*) FILTER (WHERE digest_enabled)             AS digest_on,
  count(*) FILTER (WHERE digest_enabled IS NULL)     AS digest_unknown
FROM notify_prefs;
text
 total_users | email_on | sms_on | push_on | digest_on | digest_unknown
-------------+----------+--------+---------+-----------+----------------
           4 |        3 |      2 |      3 |         1 |              2
(1 row)

digest_on 是 1、digest_unknown 是 2，说明"明确开启"与"未表态"被正确区分开了。若把 digest_enabled 设计成 NOT NULL DEFAULT FALSE，这两类用户就无法区分。

第 5 步：验证三值逻辑在查询中的差异 ​

sql
-- 想找"需要发日报的用户"：简写会漏掉未表态的人
SELECT user_name, digest_enabled FROM notify_prefs WHERE digest_enabled ORDER BY id;
text
 user_name | digest_enabled
-----------+----------------
 alice     | t
(1 row)
sql
-- 想找"不需要发日报的用户"（含未表态）：IS NOT TRUE 才能覆盖
SELECT user_name, digest_enabled FROM notify_prefs
WHERE digest_enabled IS NOT TRUE
ORDER BY id;
text
 user_name | digest_enabled
-----------+----------------
 bob       | f
 carol     |
 dave      |
(3 rows)

两个查询覆盖了全部 4 个用户，没有重叠也没有遗漏。这正是三值逻辑的价值：NULL 不必被强行归类。

第 6 步：建立部分索引并观察效果 ​

sql
-- 只为需要短信通知的用户建索引（约占少数）
CREATE INDEX idx_prefs_sms_users
    ON notify_prefs (user_name)
    WHERE sms_enabled;

ANALYZE notify_prefs;

-- 查询条件与索引 WHERE 子句一致，可走部分索引
EXPLAIN (COSTS OFF)
SELECT user_name FROM notify_prefs WHERE sms_enabled AND user_name LIKE 'c%';
text
                    QUERY PLAN
--------------------------------------------------
 Index Only Scan using idx_prefs_sms_users on notify_prefs
   Index Cond: ((user_name >= 'c'::text) AND (user_name < 'd'::text))
   Filter: (user_name ~~ 'c%'::text)
(3 rows)

计划里出现 Index Only Scan using idx_prefs_sms_users，说明部分索引被用上了。

sql
-- 换成 IS NOT TRUE，就无法匹配索引的 WHERE 子句
EXPLAIN (COSTS OFF)
SELECT user_name FROM notify_prefs WHERE sms_enabled IS NOT TRUE;
text
              QUERY PLAN
--------------------------------------
 Seq Scan on notify_prefs
   Filter: (sms_enabled IS NOT TRUE)
(2 rows)

第 7 步：布尔与 NULL 的聚合对照 ​

sql
SELECT
  user_name,
  digest_enabled,
  NOT digest_enabled                       AS not_expr,       -- NULL 仍为 NULL
  digest_enabled IS NOT TRUE               AS is_not_true,    -- NULL 变为 TRUE
  coalesce(digest_enabled, FALSE)          AS coalesced_false -- 补默认值
FROM notify_prefs
ORDER BY id;
text
 user_name | digest_enabled | not_expr | is_not_true | coalesced_false
-----------+----------------+----------+-------------+-----------------
 alice     | t              | f        | f           | t
 bob       | f              | t        | t           | f
 carol     |                |          | t           | f
 dave      |                |          | t           | f
(4 rows)

同一列在四种写法下得到完全不同的结果，写查询时必须明确业务意图：

写法TRUE 行FALSE 行NULL 行
digest_enabledtfNULL
NOT digest_enabledftNULL
digest_enabled IS NOT TRUEftt
coalesce(digest_enabled, FALSE)tff

第 8 步：清理环境 ​

sql
DROP TABLE IF EXISTS notify_prefs;
DROP TABLE IF EXISTS tasks_flow;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS legacy_flags;
DROP TABLE IF EXISTS modern_flags;
text
DROP TABLE

注意事项 ​

  • NULL 不是 FALSE。把布尔列设计成可空却在查询里用 WHERE NOT flag，会静默漏掉所有未表态的行。这类漏数据在生产里极难发现。

    sql
    -- 危险：NOT flag 漏掉 NULL 行
SELECT count(*) FROM notify_prefs WHERE NOT email_enabled;
    text
     count
-------
     1
(1 row)

    实际明确关闭 email 的只有 carol 一人，但若表里存在 NULL，这个数字仍会漏掉它们。

  • 布尔列尽量设为 NOT NULL 并给 DEFAULT。只有业务确实需要"未表态"这个第三态时才允许 NULL，此时要在注释里写明。

  • 不要依赖 AND / OR 的短路求值。优化器可以重排子表达式，WHERE a <> 0 AND 1 / a > 0 不保证不报除零错。

  • NOT IN 遇到 NULL 会返回空集。子查询里出现 NULL 时，NOT IN 整体失效，改用 NOT EXISTS 或先过滤 NULL。

  • 布尔列的普通 B-tree 索引几乎没用。只有 2~3 个不同值，选择性太差，优化器通常选择顺序扫描。用部分索引代替。

  • 部分索引的 WHERE 子句与查询条件必须互相蕴含。WHERE sms_enabled 的索引无法服务 WHERE sms_enabled IS NOT TRUE。

  • COUNT(flag) 统计的是非 NULL 行数，不是 TRUE 行数。统计 TRUE 必须用 count(*) FILTER (WHERE flag)。

    sql
    SELECT count(digest_enabled) AS non_null,
       count(*) FILTER (WHERE digest_enabled) AS true_count
FROM notify_prefs;
    text
     non_null | true_count
----------+------------
        2 |          1
(1 row)

  • 不要用 CHAR(1) / SMALLINT 模拟布尔。1 字节换不来任何好处，却要额外 CHECK 约束与应用层字符串比较。

  • BOOLEAN 不接受 2 / -1 等整数。从 MySQL 迁移时，若历史数据里存在 TINYINT 的非 0/1 值，加载会直接报错，需要先清洗数据。

  • DROP COLUMN 后旧数据的布尔值不会消失。物理空间要等 VACUUM 后可能被后续写入复用，这与其他类型一致。

  • ORDER BY flag 的默认顺序是 FALSE 在前。想让"真"排前面必须显式 DESC，不要凭直觉假设。

  • 不要用布尔列做"软删除 + 唯一约束"而不建部分索引。删掉的行会一直占用唯一键，导致业务无法复用订单号。

最佳实践 ​

  • 业务开关列一律 BOOLEAN NOT NULL DEFAULT <明确的默认值>。默认值必须是业务上有意义的那个，不要留空让应用决定。

  • 需要"未表态"语义时才允许 NULL，且查询端统一用 IS NOT TRUE / IS NOT FALSE，避免 NOT flag 的隐式漏行。

  • 需要"真/假/未知"三态可分辨时，把语义写进列注释，并在查询层封装成视图：

    sql
    CREATE VIEW v_notify_decision AS
SELECT
  user_name,
  CASE
    WHEN digest_enabled IS TRUE  THEN 'send'
    WHEN digest_enabled IS FALSE THEN 'skip'
    ELSE 'ask-user'                       -- NULL：未表态
  END AS digest_action,
  coalesce(digest_enabled, FALSE) AS digest_effective  -- 默认不发送
FROM notify_prefs;

    应用层只需读 digest_effective，未表态的走 ask-user 引导流程，语义清晰且无歧义。

  • 高频过滤的布尔条件配部分索引，索引的 WHERE 子句与查询条件保持字面一致，并用 EXPLAIN 验证命中。

  • 统计布尔分布用 count(*) FILTER (WHERE ...)，不要用 sum(flag::int)，避免显式转型。

  • bool_and / bool_or 遇到空集会返回 NULL，若业务需要"空集视为 FALSE"，记得套 coalesce(..., FALSE)。

  • 多个互斥状态用 TEXT + CHECK 枚举，不要用多个布尔列。只有在各开关可以同时为真时才用多列。

  • 权限开关数量多时考虑 JSONB + GIN 索引，而不是给表加几十个布尔列（列数过多会影响行宽与计划稳定性）。

  • 迁移历史 CHAR(1) / TINYINT 数据时按三步走：新增 BOOLEAN 列 → 双写并回填（UPDATE ... SET flag = (old = 'Y')）→ 校验一致后切读删旧列。

    sql
    -- 回填示例：把 CHAR(1) 的 'Y'/'N' 转成布尔
-- UPDATE legacy_flags
-- SET is_valid_bool = (is_valid = 'Y')
-- WHERE is_valid_bool IS DISTINCT FROM (is_valid = 'Y');

    用 IS DISTINCT FROM 便于分批重跑而不重复写已正确的行。

  • BOOLEAN 与整数之间转换要显式：flag::int 得 0 / 1，NULL::int 仍是 NULL。对外协议（如 JSON）需要 0/1 时在应用层或视图中转换。

  • 不要为布尔列单独建普通索引。基数太低，索引维护成本高于收益；需要索引用部分索引或把布尔条件并入复合索引的前导列。

下一节

继续学习：二进制数据类型（BYTEA）

参考链接 ​