布尔与位串类型与约束体系（CHECK、外键、排他约束）

• 原生 boolean vs TINYINT(1)
• 三值逻辑
• JDBC tinyInt1isBit

PostgreSQL 有原生 boolean 类型，值为 true/false/NULL，比较语义是三值逻辑；MySQL 没有原生 boolean，用 TINYINT(1) 存 0/1 模拟，比较语义是整数。差异：PG 存 true/false、MySQL 存 0/1；驱动映射上，MySQL JDBC 的 tinyInt1isBit 参数决定 TINYINT(1) 是否映射为 Java boolean（默认 true 映射为 boolean，否则为 Integer）。迁移陷阱：PG 的 boolean 迁移到 MySQL 需转 0/1，WHERE 条件与布尔运算语义变化；MySQL 的 TINYINT(1) 迁移到 PG 需转 boolean，且 NULL 与三值逻辑需注意。

核心是"原生布尔 vs 整数模拟"的存储与语义差异，以及 JDBC 参数对映射的影响。

• 选择性过低
• B-Tree 失效
• 部分索引

布尔列只有 true/false 两个取值，选择性极低，B-Tree 索引对单值查询（WHERE flag = true）会命中大量行，优化器通常放弃索引改为全表扫描或位图扫描，索引无意义。替代方案：部分索引只索引需要的子集，如 CREATE INDEX ON t(id) WHERE flag = true，索引小且精准；或用位图扫描（MySQL 无位图，PostgreSQL 多列布尔可配合）。部分索引让"只查询 flag=true 的行"走小索引。

低选择性使普通索引失效，部分索引缩小索引范围、保留选择性，是布尔列的优化手段。

• 三值逻辑
• AND/OR 组合
• NULL 过滤

SQL 布尔是三值逻辑（true/false/unknown）。组合规则：true AND unknown = unknown，false AND unknown = false，true OR unknown = true，false OR unknown = unknown。在 WHERE 过滤中，只有结果为 true 的行保留，unknown 被过滤（视为不满足）。影响：WHERE col = 1 不匹配 col 为 NULL 的行（unknown）；CHECK 约束中，条件为 unknown（含 NULL）时视为通过（不违反），因此 CHECK (col > 0) 允许 col 为 NULL。需禁止空值则加 NOT NULL。

三值逻辑使 NULL 参与比较得到 unknown，被过滤；CHECK 对 unknown 视为通过，需配合 NOT NULL。

• bit/varbit 类型
• 位操作符
• 位掩码建模

PostgreSQL 的 bit(n) 是定长位串、varbit(n) 是变长位串，存储为二进制位。位操作符包括 &（与）、|（或）、#（异或）、~（取反）、<<（左移）、>>（右移）、bit_count（1 的个数，PG 14+）。权限位掩码/特性开关可用 bit 或整数位运算建模：每位代表一个权限/开关，用 & 判断是否开启、| 开启、# 切换。例如 flag & 1 = 1 判断第 1 位。位串紧凑且运算高效。

bit/varbit 与位运算符实现紧凑的位掩码，适合权限标记与特性开关建模。

• BIT(64) 位串
• TINYINT(1) 布尔模拟
• 读取转换

MySQL 的 BIT(n) 存储位串，BIT(64) 最多 64 位；TINYINT(1) 是整数（0/1），用于布尔模拟。BIT 列在 JDBC/客户端读取时默认返回二进制字节，可能显示为 b'...' 字面量或乱码，需用转换函数处理：BIN(col)、HEX(col) 转可读值，或 CAST(col AS UNSIGNED) 转整数，或 col + 0。TINYINT(1) 读取为整数 0/1，配合 tinyInt1isBit 可映射 boolean。区别：BIT 灵活位串、TINYINT 布尔。

BIT 读取返回二进制需显式转换，TINYINT(1) 读取为整数，drive 映射不同。

• MySQL 0/1 vs PG true/false
• Boolean 包装类 NULL
• Hibernate 方言

ORM 布尔映射常见坑：1) MySQL 存 0/1、PG 存 true/false，数据库迁移时布尔值表示不同，需转换；2) Java Boolean 包装类可为 null，与数据库 NULL 语义需一致，避免 NPE 或漏存；3) Hibernate 方言差异（MySQL 用 TINYINT(1)、PG 用 boolean），DDL 生成与查询不同；4) 读取类型不匹配（TINYINT(1) 映射为 boolean 依赖 tinyInt1isBit）。处理：统一用原生 boolean 或约定 0/1，明确 NULL 语义，配置方言。

布尔映射的坑集中在"存储表示、NULL 语义、方言差异"，跨库需特别注意。

• NULL 唯一性差异
• MySQL vs SQL Server
• 空值键处理

唯一约束中 NULL 语义因数据库而异：MySQL 中唯一索引允许列出现多个 NULL（NULL 视为互不相同），因此可插入多行 NULL；SQL Server 早期只允许一个 NULL 值（其中一列也可）。PostgreSQL 默认也允许多个 NULL。导致"业务键"含空值时唯一性行为不同。处理空值业务键：用 COALESCE 提供默认值、用唯一索引 + 触发器、或用 NOT NULL + 代理键。理解差异避免误判唯一性。

NULL 在唯一约束中的语义差异是跨库兼容陷阱，含空值业务键需结构化处理。

• 文本转布尔
• 整数转布尔
• 空字符串错误

PostgreSQL 中文本转布尔，'t'、'true'、'yes'、'on'、'1' 转为 true，'f'、'false'、'no'、'off'、'0' 转为 false；整数转布尔仅 0 为 false、1 为 true（其他报错）。空字符串 '' 不在可识别的布尔字面量中，转换报错（invalid input syntax for type boolean）。ALTER COLUMN ... USING col::boolean 做类型转换时，若含空字符串或非法值会失败，需先清洗数据。

布尔转换的合法字面量集合固定，空字符串/非法值会报错，迁移前需清洗。

• BOOL_AND/BOOL_OR
• 空集结果
• 全 NULL

BOOL_AND 返回组内所有布尔值的 AND（全部 true 才 true），BOOL_OR 返回 OR（任一 true 即 true）。空集输入时，BOOL_AND 返回 true（vacuous truth），BOOL_OR 返回 false。全 NULL 输入时，BOOL_AND 返回 NULL（由于三值逻辑，NULL 参与被忽略，但全是 NULL 时无真值，返回 NULL），BOOL_OR 同样返回 NULL。理解这些边界值能避免聚合判断错误。

空集与全 NULL 的聚合结果是三值逻辑的体现，BOOL_AND 空集为 true、全 NULL 为 NULL。

• 复合索引列序
• 低选择性处理
• 部分/覆盖索引

低选择性的布尔列一般不宜放复合索引最前面（最左前缀），否则会限制高选择性列的使用；应把高选择性列放前面，布尔列放后面或剔除。但若查询固定为 WHERE flag = true AND other = x，布尔列放前面可让索引直接命中 flag=true 的子集，有一定价值。更优做法是部分索引：CREATE INDEX ... ON t(other) WHERE flag = true，既精确又小。覆盖索引把查询列都放索引中避免回表。布尔列与部分/覆盖索引配合比放复合索引更有效。

低选择性列放复合索引位置取决于查询模式，部分索引是布尔过滤的更优方案。

• NO ACTION vs RESTRICT
• DEFERRABLE 推迟
• 各动作风险

NO ACTION 与 RESTRICT 在 PostgreSQL 中默认行为相同（被引用时立即阻止），但 NO ACTION 可在 DEFERRABLE 约束下推迟到事务末尾检查，RESTRICT 永不可推迟。CASCADE 级联删除/更新子行，方便但可能误删大量数据；SET NULL 把子行外键置 NULL，需子列可空；SET DEFAULT 置为默认值，需默认值合法。风险：CASCADE 数据丢失、SET NULL 数据失去引用、SET DEFAULT 需默认键存在。选择依业务语义。

NO ACTION 可推迟、RESTRICT 不可推迟是本质区别；其余动作按数据完整性语义选择。

• NOT VALID 阶段
• VALIDATE 阶段
• 锁与长锁避免

PostgreSQL 大表添加约束分两阶段：1) ALTER TABLE ... ADD CONSTRAINT ... NOT VALID 只定义约束但不校验已有数据，持 ACCESS EXCLUSIVE 锁但很快（只改元数据），新写入/更新会受约束；2) ALTER TABLE ... VALIDATE CONSTRAINT 扫描已有数据校验，只持 SHARE UPDATE EXCLUSIVE 锁，与大多数 DML 并发执行，避免长时间阻塞。两阶段把"改元数据"与"校验数据"分离，避免长锁。

NOT VALID 阶段短锁改元数据，VALIDATE 阶段共享锁扫描，分离使大表加约束不阻塞写入。

• 排他约束
• EXCLUDE USING gist
• btree_gist

排他约束 EXCLUDE USING gist (room_id WITH =, period WITH &&) 保证：任意两行的 room_id 相等（=）且 period 时间段重叠（&&）时，约束被违反并拒绝插入。它用 GiST 索引对多列做排除判定，实现"同一会议室时段不重叠"。因为 GiST 默认不直接支持普通类型的 = 等值操作，需 btree_gist 扩展把 = 等值纳入 GiST 操作符，使 room_id 的等值比较可在 GiST 中执行。因此 btree_gist 是混合等值与区间排他约束的前提。

排他约束把"等值 + 区间重叠"组合成不变量，GiST + btree_gist 支持混合操作符。

• CHECK 限制
• 跨行/跨表
• 替代手段

CHECK 约束在单行层面求值，只能引用本行其他列（表达式），不能引用其他表或子查询，因为 CHECK 需要在插入/更新时独立判定且不能依赖其他行/表（否则并发与一致性问题）。跨行/跨表完整性（如账户借贷合计为零）的替代：约束触发器（CONSTRAINT TRIGGER）在事务内校验多行、排他约束（EXCLUDE）校验区间重叠、应用层事务校验、或物化汇总表。选约束触发器或应用层按复杂与并发需求。

CHECK 是单行约束，跨行/跨表需约束触发器、排他约束或应用层，各有取舍。

• 外键不自动建索引
• 全表扫描
• 锁排队/死锁

PostgreSQL 不自动为外键列创建索引（设计取舍，避免额外开销），需手动建。若外键列无索引，父表 DELETE/UPDATE 时，数据库需检查子表是否有引用该父行，无索引则全表扫描子表，代价高；且 UPDATE/DELETE 会持锁等待扫描，并发下锁排队、甚至不同事务顺序冲突导致死锁。因此大表外键列应建索引（B-Tree），加速父表删除/更新的引用检查。

外键列缺索引使引用检查全表扫描，引发性能与锁问题，需手动建索引。

• CHECK 对 NULL
• 三值逻辑
• NOT NULL 配合

CHECK 约束基于三值逻辑：若条件为 unknown（含 NULL），约束视为通过（不违反）。因此 CHECK (col > 0) 在 col 为 NULL 时通过，允许 NULL。若业务要求禁止 NULL，需同时声明 NOT NULL：CHECK (col > 0) NOT NULL。这样既保证非空又保证值域。理解 CHECK 的 NULL 通过语义，避免误以为 CHECK 会拒绝空值。

CHECK 对 unknown 放行，禁止空值需显式 NOT NULL，二者配合达成完整约束。

• 约束触发器
• DEFERRABLE
• 与 CHECK 取舍

约束触发器（CONSTRAINT TRIGGER）支持 DEFERRABLE，可在事务末尾推迟执行，适合校验跨行/跨表一致性（此时所有行已更新）；普通触发器在语句/行级立即执行，无法推迟；CHECK 约束只能单行简单表达式。取舍：简单单行校验用 CHECK；需跨行/跨表且可推迟校验用约束触发器；简单行级副作用用普通触发器。约束触发器灵活性最高但复杂与开销大。

约束触发器可推迟到事务末，适合复杂跨行校验，普通触发器与 CHECK 各有局限。

• 并发排他
• 锁冲突
• NOWAIT

排他约束用 GiST 索引检测重叠，并发插入重叠区间时，后插入的事务会因与已有/并发的排他约束冲突而阻塞等待（等待释放锁），直到前事务提交或回滚。若前事务提交则后事务因违反约束报错，回滚则后事务可继续。NOWAIT 用于让冲突查询立即报错而非等待（如 SELECT ... FOR UPDATE NOWAIT），避免长时间阻塞。排他约束保证并发下区间不重叠，但可能造成锁等待。

排他约束并发下后到者阻塞等待，NOWAIT 使冲突立即失败，平衡一致性与等待。

• 分区表外键
• PG 版本限制
• MySQL 限制

PG 12+ 才支持分区表作为外键的被引用端（父表删除时引用检查），且分区表作为引用端支持外键；跨分区更新外键列在 PG 有行为限制（PG 14 之前不支持 UPDATE 跨分区移动）。MySQL 分区表不支持外键（分区表不能有外键，也不能被外键引用）。因此分区表 + 外键存在版本与功能限制，设计时需评估。

分区表外键支持取决于版本与数据库，PG12+ 改善、MySQL 分区表不支持外键。

• MySQL 8.0.16 CHECK
• 之前忽略
• PG 行为

MySQL 8.0.16 之前，CHECK 约束被解析但被静默忽略（不实际校验），因为在 MySQL 中 CHECK 仅作文档用途，实际靠枚举/应用层校验。8.0.16 起 CHECK 约束真正生效，违反会报错。PostgreSQL 自始就强制执行 CHECK，违反报错。差异：MySQL 旧版 CHECK 无效、新版才生效；PG 一直有效。升级 MySQL 或迁移时需了解 CHECK 是否真被校验。

MySQL 8.0.16 是 CHECK 强制执行的分水岭，PG 一直强制执行，行为差异显著。

• SET DEFAULT 语义
• 默认值缺失
• 报错顺序

ON DELETE SET DEFAULT 在父行删除时把子行外键设为默认值。若默认值缺失（外键列无 DEFAULT）或默认值不满足其他约束（如不存在对应父行、违反 NOT NULL/CHECK），则删除操作会报错。约束检查顺序：PostgreSQL 按约束类型与依赖顺序检查，通常先检查 NOT NULL/CHECK/FK 等，违反任一即报错。SET DEFAULT 需保证默认值合法存在，否则删除失败。

SET DEFAULT 依赖合法默认值，默认值违规会中断删除，约束检查按顺序执行。

• 孤儿数据排查
• LEFT JOIN IS NULL
• NOT EXISTS

添加外键前，可用 LEFT JOIN ... IS NULL 或 NOT EXISTS 查找孤儿数据（子表外键值在父表不存在），如 SELECT c.* FROM child c LEFT JOIN parent p ON c.parent_id = p.id WHERE p.id IS NULL; 或 WHERE NOT EXISTS (SELECT 1 FROM parent p WHERE p.id = c.parent_id)。查出后按需删除或修复。清理后再用 NOT VALID + VALIDATE 两阶段添加外键，避免大表长时间锁。

LEFT JOIN IS NULL / NOT EXISTS 是查孤儿数据的标准方法，先清后加避免外键校验失败。

• get_bit/set_bit
• bit 转 bytea
• bytea 转 bit

PostgreSQL 的 get_bit(bit, n) 返回第 n 位（0/1），set_bit(bit, n, 0/1) 设置第 n 位。bit 与 bytea 互转：bit 转 bytea 用 bit::bytea（或 decode），bytea 转 bit 用 bytea::bit(n) 或 bit(n)。位数对齐需注意（bytea 是字节，bit 是位）。这些用于位级操作与二进制数据互转，如权限位、协议处理。

get_bit/set_bit 做位操作，bit 与 bytea 互转做字节-位转换，需注意位数对齐。