数学、日期与 JSON 路径

• DATE_ADD/DATE_SUB
• INTERVAL
• MySQL 方言

MySQL 中日期加减用 DATE_ADD(date, INTERVAL expr unit) 和 DATE_SUB(date, INTERVAL expr unit)，unit 是单位（如 DAY、MONTH、YEAR、HOUR、MINUTE）。例如 DATE_ADD('2024-01-01', INTERVAL 1 DAY) 返回 '2024-01-02'。也可用 date + INTERVAL expr unit 语法（如 '2024-01-01' + INTERVAL 1 DAY）。INTERVAL 是关键字，expr 是数值，unit 是单位。MySQL 还支持 ADDDATE/SUBDATE 简写。这是 MySQL 特有的日期运算语法。

MySQL 用 DATE_ADD/DATE_SUB 配合 INTERVAL unit 做日期加减，也可用 date + INTERVAL 语法。

• DATE_TRUNC
• DATE_PART
• 截取/提取

DATE_TRUNC(unit, timestamp) 把时间戳截断到指定单位，如 DATE_TRUNC('day', ts) 返回当天 00:00:00，DATE_TRUNC('month', ts) 返回当月 1 日 00:00:00。DATE_PART(field, timestamp) 提取时间字段的值，如 DATE_PART('year', ts) 返回年份，DATE_PART('dow', ts) 返回星期几（0=周日）。DATE_TRUNC 用于向下取整到时间单位，DATE_PART 用于提取具体字段。两者是 PostgreSQL 特有函数。

DATE_TRUNC 截断到单位（时间向下取整），DATE_PART 提取字段值。是 PostgreSQL 特有的日期函数。

• NOW/CURRENT_TIMESTAMP
• CURRENT_DATE/CURRENT_TIME
• 差异

NOW() 与 CURRENT_TIMESTAMP 都返回当前时间戳（含时区），在 PostgreSQL 中 NOW() 等价于 CURRENT_TIMESTAMP，且在同一事务内返回相同值（事务开始时间）；CURRENT_DATE 返回当前日期（DATE 类型），CURRENT_TIME 返回当前时间（TIME 类型）。MySQL 中 NOW() 与 CURRENT_TIMESTAMP 返回当前时间戳（语句执行时间），CURRENT_DATE 返回日期，CURRENT_TIME 返回时间。差异：PostgreSQL 的 NOW() 是事务时间戳（事务内不变），MySQL 的 NOW() 是语句时间（同一事务多语句可能不同，除非用只读）。类型上 PostgreSQL 的 NOW() 是 timestamptz，MySQL 是 datetime。

NOW()/CURRENT_TIMESTAMP 返回当前时间戳，CURRENT_DATE/CURRENT_TIME 返回日期/时间。PostgreSQL 事务时间戳，MySQL 语句时间戳。

• generate_series
• 序列生成
• 日期用法

generate_series(start, stop, step) 生成从 start 到 stop 的序列，step 是步长（可省略，默认 1）。可用于整数：generate_series(1, 10) 生成 1..10；可用于日期：generate_series('2024-01-01'::date, '2024-01-31'::date, '1 day') 生成每天的日期；也可用于时间戳。它常用于生成日期维度表、填充缺失日期、测试数据。返回集合，可作 FROM 子查询或 SELECT 中展开。

generate_series 生成整数/日期序列，step 控制步长，常用于日期维度与补全缺失日期。

• date + integer
• date 减法
• interval 存储

日期算术的内部存储：date 类型在 PostgreSQL 中存储为自 2000-01-01 起的偏移天数（整数）。date + integer 表示加 n 天（date + integer 返回 date，如 '2024-01-01' + 1 返回 '2024-01-02'）；date - date 返回整数天数差；date + interval 返回 timestamp（date 加 interval 得到 timestamp）。interval 存储为微秒数（含月/日/时间部分）。不同数据库内部存储不同（MySQL 的 date 用 3 字节，PostgreSQL 用 4 字节整数偏移）。核心：date 是整数天数偏移，加减整数即天数，interval 是时间跨度。

PostgreSQL date 存为自 2000-01-01 的天数偏移，+integer 加天，date-date 返回整数天，+interval 进 timestamp。interval 存微秒。

• DATEDIFF
• 参数差异
• 单位

DATEDIFF 的差异：MySQL 的 DATEDIFF(date1, date2) 返回两个日期的天数差（date1 - date2，单位天）；SQL Server 的 DATEDIFF(unit, start, end) 返回指定单位（DAY、MONTH、YEAR 等）的差值，参数顺序是 unit 在前；PostgreSQL 没有 DATEDIFF 函数，用 date1 - date2（返回整数天）或 EXTRACT(EPOCH FROM (date1 - date2)) 计算秒数。因此三者的 DATEDIFF 参数与语义不同，需注意方言。

MySQL DATEDIFF 返回天数、SQL Server DATEDIFF(unit,start,end) 指定单位、PostgreSQL 无 DATEDIFF 用减法。

• ROUND
• CEIL/FLOOR
• TRUNC

ROUND(x) 四舍五入到整数；CEIL(x) 向上取整（不小于 x 的最小整数）；FLOOR(x) 向下取整（不大于 x 的最大整数）；TRUNC(x) 无条件截断（向零取整，去掉小数部分）。负数处理：CEIL(-1.5) = -1（向上），FLOOR(-1.5) = -2（向下），TRUNC(-1.5) = -1（向零），ROUND(-1.5) 依数据库而异（PostgreSQL 四舍五入到 -2，MySQL 返回 -2 或 -1 依实现）。区别：FLOOR 向负无穷，TRUNC 向零，CEIL 向正无穷。ROUND 有小数位参数（ROUND(x, n)）。

CEIL 向上、FLOOR 向下、TRUNC 向零、ROUND 四舍五入。负数时 FLOOR 与 TRUNC 不同（FLOOR(-1.5)=-2，TRUNC=-1）。

• to_char
• to_date
• to_timestamp

PostgreSQL 的格式化函数：to_char(value, format) 把值格式化为字符串（如 to_char(ts, 'YYYY-MM-DD HH24:MI:SS')）；to_date(str, format) 把字符串按格式解析为 date；to_timestamp(str, format) 把字符串解析为 timestamp。常用格式模板：YYYY 四位年、MM 月、DD 日、HH24 24 小时制小时、MI 分、SS 秒、Mon 月名缩写、Day 星期名、DOW 星期几等。to_char 用于格式化输出，to_date/to_timestamp 用于解析输入。

to_char 格式化输出，to_date/to_timestamp 解析输入，格式模板如 YYYY-MM-DD HH24:MI:SS。

• RANDOM 每行求值
• volatile
• 陷阱

RANDOM() 是 volatile 函数，在 WHERE 中每行都会重新计算，导致随机结果不稳定。例如 WHERE col > RANDOM() 会对每行取不同的随机值，结果不可预测、不可重现。陷阱：1) 用 RANDOM() 做随机抽样时每行独立随机，结果不确定；2) 在 WHERE 中引用 RANDOM() 可能使谓词无法索引、每次执行结果不同。正确做法：把随机值物化到变量/子查询（如先 setseed(固定值) 再调用 RANDOM()），或使用 TABLESAMPLE 做采样。随机抽样应避免直接 WHERE RANDOM()。

RANDOM() volatile 每行重算，WHERE 中结果不稳定。抽样应物化随机值或用 TABLESAMPLE。

• 弧度制
• 三角函数
• 角度换算

PostgreSQL 及多数数据库的三角函数 SIN、COS、TAN 使用弧度制（radian），参数需为弧度。若要传角度，需先转换为弧度（角度 × PI()/180 或 RADIANS(deg)）。例如 SIN(30) 表示 30 弧度，不是 30 度。PostgreSQL 提供 RADIANS(deg) 和 DEGREES(rad) 做角度/弧度转换。SQL Server 的 SIN/COS/TAN 也用弧度。因此使用三角函数时需注意单位是弧度。

SIN/COS/TAN 用弧度制，角度需用 RADIANS() 转换。这是三角函数的使用要点。

• LOG
• LN
• LOG10

对数函数差异：LN(x) 是自然对数（以 e 为底）；LOG10(x) 是常用对数（以 10 为底）；LOG(x) 在 PostgreSQL 中默认以 10 为底（等价 LOG10），也可用 LOG(b, x) 指定底数 b（LOG(b, x) 表示以 b 为底 x 的对数）。MySQL 中 LOG(x) 是自然对数（以 e 为底），LOG(b, x) 指定底数。因此 LOG 的默认底数因数据库而异（PostgreSQL 默认 10，MySQL 默认 e），需注意。

LN 自然对数、LOG10 常用对数、LOG 默认底数因数据库而异（PostgreSQL 10，MySQL e）。LOG(b,x) 可指定底数。

• JSON 文本存储
• JSONB 二进制
• 键去重排序

JSON 与 JSONB 在 PostgreSQL 中的存储差异：JSON 按输入文本原样存储（保留空白、重复键、顺序），解析较慢；JSONB 解析为二进制格式，去除空白、去重键（重复键只保留最后一个）、按键排序、不保留原始顺序与空白。JSONB 存储效率更高、支持索引（GIN）、查询更快，但不保留输入原貌。JSON 保留文本原样但功能少、无索引。因此需要保留原始文本顺序时用 JSON，需要查询/索引/性能时用 JSONB。

JSON 文本原样存储，JSONB 二进制解析、去重键、排序键、不保留顺序与空白。JSONB 支持索引、性能好。

• jsonb_set
• jsonb_insert
• jsonb_delete

jsonb_set(jsonb, path, new_value, create_missing) 按路径设置/替换值，create_missing 控制路径不存在时是否创建（默认 true）；jsonb_insert(jsonb, path, new_value, insert_after) 在数组指定位置插入值（insert_after 控制插入位置前/后）；jsonb_delete(jsonb, key) 或 jsonb - key 删除指定键，jsonb_delete(jsonb, array) 删除多个键。这些函数用于更新 JSONB 数据，配合 UPDATE 使用。路径用数组表示（如 '{a,b}'）。

jsonb_set 替换、jsonb_insert 插入、jsonb_delete 删除。路径用数组，用于 JSONB 更新。

• json_agg
• jsonb_agg
• json_object_agg

json_agg(expr) 把组内多行聚合成 JSON 数组（json 类型），jsonb_agg 返回 jsonb 类型数组；json_object_agg(key, value) 把组内多行聚合成 JSON 对象（键值对）。差异：json_agg 返回 json 数组，jsonb_agg 返回 jsonb 数组（更快、支持索引），json_object_agg 返回对象（需要 key 和 value 列）。三者都支持 ORDER BY 排序组合。用于把行转成 JSON 聚合结果。

json_agg/jsonb_agg 返回 JSON 数组，json_object_agg 返回 JSON 对象。jsonb 版本更快、支持索引。

• -> 返回 json
• ->> 返回 text
• #> 路径

JSON 操作符：-> 返回 json 类型（可以继续链式访问），->> 返回 text 类型（纯文本）；#> 按路径返回 json，#>> 按路径返回 text。例如 data->'a' 返回键 a 的 json 值，data->>'a' 返回文本值。路径用数组：data#>>'{a,b}'。索引支持：->> 和 #>> 返回 text 时，若列是 jsonb，可对表达式建索引（如 CREATE INDEX ON t ((data->>'a'))）；而 jsonb 的 GIN 索引支持 @>、? 等操作符，-> 类操作符需建表达式索引。这些操作符大多不直接走 GIN（需表达式索引或 jsonb_path_ops）。

-> 返回 json（可链式）、->> 返回 text、#>/#>> 按路径。索引需表达式索引或 GIN（@> 等）。

• @> 包含
• <@ 包含于
• ? 键存在

JSONB 操作符：@> 判断左侧是否包含右侧（子集/值包含），<@ 判断左侧是否包含于右侧（反向）；? 判断键是否存在（顶层键）；?| 判断是否包含任意指定键（任一存在）；?& 判断是否包含所有指定键（全部存在）。这些操作用于 JSONB 查询与过滤，且 @>、?、?|、?& 可用 GIN 索引加速。@> 用于包含/匹配查询，? 系列用于键存在检查。

@> 包含、<@ 包含于、? 键存在、?| 任意键、?& 所有键。@> 与 ? 系列可走 GIN 索引。

• jsonb_ops
• jsonb_path_ops
• 索引差异

GIN JSONB 索引有两种操作符类：jsonb_ops（默认）索引每个键和值（及其路径），体积大、支持所有操作符（@>、?、?|、?&、@? 等）；jsonb_path_ops 只索引值路径（紧凑的路径+值），体积小（约 1/4 到 1/2）、@> 查询更快，但不支持 ?、?|、?& 等键存在操作符（支持 @>、@?、@@）。选择：若主要用 @> 包含查询且空间敏感用 jsonb_path_ops；若需键存在等操作符用 jsonb_ops。

jsonb_ops 索引键和值、支持多操作符、体积大；jsonb_path_ops 只索引路径+值、体积小、@> 快但不支持 ? 系列键存在操作符（支持 @>、@?、@@）。

• jsonb_path_query
• jsonb_path_exists
• SQL/JSON 路径

jsonb_path_query(jsonb, path) 按 SQL/JSON 路径表达式返回匹配的 jsonb 值（可能多行），jsonb_path_exists(jsonb, path) 返回布尔值（路径是否存在匹配）。它们支持 SQL/JSON 路径语言（如 '$.a[].b'、'$.a ? (@.x > 1)'），源于 SQL 标准 SQL/JSON（SQL:2016）。路径语法：$ 根、. 属性、[] 数组、?() 过滤器、@ 当前节点。jsonb_path_query 返回集合，jsonb_path_exists 判断存在。这些实现了 SQL/JSON 路径查询。

jsonb_path_query/exists 用 SQL/JSON 路径语言查询 JSONB，支持 $ 根、. 属性、[*] 数组、?() 过滤。

• JSON_TABLE
• SQL:2016
• 实现差异

JSON_TABLE 把 JSON 数据按路径展开为关系表（行/列），是 SQL:2016 标准。实现差异：Oracle 完整支持 JSON_TABLE（可指定 COLUMNS、NESTED PATH 等）；MySQL 8 支持 JSON_TABLE；PostgreSQL 原生不支持 JSON_TABLE（需用 jsonb_to_recordset、jsonb_path_query 或 LATERAL + jsonb_array_elements 手工实现）。因此 PostgreSQL 中 JSON_TABLE 功能需用等价函数模拟。差异主要在语法支持与嵌套展开能力。

JSON_TABLE 展开 JSON 为关系表。Oracle/MySQL 支持，PostgreSQL 原生不支持，需用 jsonb_to_recordset 等模拟。

• EXPLAIN 判断
• GIN 命中
• jsonb_path_ops 限制

通过 EXPLAIN 查看执行计划：若查询使用 GIN 索引，计划中会出现 "Bitmap Index Scan" 或 "Index Scan" 使用 gin 索引（如 "Index Scan using idx on t ... Index Cond: (data @> ...)"）。若计划为 Seq Scan 则未命中索引。jsonb_path_ops 只索引路径+值，不支持 ?、?|、?& 等键存在操作符，但支持 @>、@?、@@（PostgreSQL 12+）。注意 jsonb_path_exists() 函数调用本身不能直接命中 GIN 索引，应改写为等价的 @? 操作符才能走索引（@? 在两种操作符类下均可命中）。因此 jsonb_path_ops 的局限是不能加速键存在（? 系列）查询，而路径存在（@?）可以被 jsonb_path_ops 加速。

EXPLAIN 看是否有 Index Scan/GIN 节点判断命中。jsonb_path_ops 只索引路径+值，不支持 ? 系列键存在操作符，但支持 @>、@?、@@；jsonb_path_exists 函数本身不走索引，需改写为 @? 操作符。

• pg_jsonschema
• jsonb_matches_schema
• JSON Schema 校验

PostgreSQL 原生没有 JSON Schema 校验函数。可安装 pg_jsonschema 扩展，提供 jsonb_matches_schema(schema, jsonb) 和 json_matches_schema(schema, json) 函数，按 JSON Schema（Draft 4/6/7）校验 JSON 是否合法，返回布尔值。用法：CREATE EXTENSION pg_jsonschema; SELECT jsonb_matches_schema('{"type":"object","required":["id"]}', '{"id":1}')。可用于约束校验、数据质量检查。工程上用它做入库前的 Schema 校验。

pg_jsonschema 扩展提供 jsonb_matches_schema/json_matches_schema 做 JSON Schema 校验，返回布尔值。

• JSON 列适用
• 拆表适用
• 建模取舍

用 JSON 列的时机：数据结构不固定/多变、字段很少被单独查询、嵌套层级深、需要快速存储灵活数据、与外部系统交换数据。拆为关系表的时机：字段需要被查询/过滤/索引、字段有约束与关系、需要 JOIN 关联、数据一致性要求高、字段访问频繁。JSON 列适合灵活、低频查询的文档型数据；关系表适合结构化、高频查询、强约束的数据。混合建模：稳定字段拆表，灵活字段用 JSONB。

JSON 列适配灵活多变、低频查询；拆表适配结构化、高频查询、强约束。混合建模常见。

• SQL/JSON 标准
• JSON_EXISTS/QUERY/VALUE
• 兼容性差距

SQL/JSON 标准提供 JSON_EXISTS（存在性）、JSON_QUERY（返回 JSON 片段）、JSON_VALUE（返回标量值）及错误处理子句（ON ERROR/ON EMPTY 的 NULL/ERROR/EMPTY 选项）。PostgreSQL 的差距：原生不提供 JSON_EXISTS/JSON_QUERY/JSON_VALUE 函数（用 jsonb_path_exists/jsonb_path_query 等替代），错误处理子句支持有限（PostgreSQL 的 jsonb_path_query 有严格/宽松模式，但没有标准化的 ON ERROR/ON EMPTY 子句选项）。PostgreSQL 通过 SQL/JSON 路径函数（jsonb_path_*）实现部分标准，但函数名、语法与标准不完全一致。差距主要在标准函数名、错误处理子句的完整支持。

SQL/JSON 标准有 JSON_EXISTS/QUERY/VALUE 及 ON ERROR/ON EMPTY，PostgreSQL 用 jsonb_path_* 替代，函数名与错误处理子句支持不完整。

• JSONB 特点
• HSTORE 特点
• 取舍

JSONB 与 HSTORE 都是键值对存储，但差异：JSONB 支持嵌套结构、数组、数字、布尔等 JSON 类型，支持 SQL/JSON 路径查询、丰富的操作符（@>、?、->> 等）与 GIN 索引，功能更强大；HSTORE 是简单的 key-value 文本存储（值都是文本），不支持嵌套、数组、数字类型，操作符较少（->、->>、? 等），功能有限。取舍：需要复杂 JSON 结构、查询、索引时用 JSONB；只需简单键值对、性能要求高、功能简单时 HSTORE 更轻量。绝大多数场景 JSONB 更合适。

JSONB 支持嵌套/数组/类型/路径查询/GIN 索引，功能强；HSTORE 简单键值文本、功能有限。复杂场景用 JSONB。

• JSONB 行存
• 列存储
• 取舍

JSONB 存储与列存储（如 ClickHouse、OLAP 列存）的取舍：JSONB 在行存储（PostgreSQL）中按文档存储，适合检索、更新、复杂查询，但若 JSON 中有大量字段只取少数列，行存会读入整个 JSON，浪费 IO；列存储按列存储，适合分析和只取部分字段的聚合扫描，但列存通常不适合频繁更新与点查。取舍：事务型、频繁更新、需要灵活查询用 JSONB；分析型、按列聚合、大扫描用列存储。也可把 JSONB 展开为列存或使用列存扩展。

JSONB 行存适合检索/更新/灵活查询；列存储适合分析/列聚合/大扫描。按场景选择。

• JSON_EXTRACT
• JSON_OBJECT
• JSON_ARRAY

MySQL 的 JSON 函数：JSON_EXTRACT(json_doc, path) 提取 JSON 路径对应值（返回 JSON），等价于 -> 操作符；JSON_OBJECT(key, value, ...) 构造 JSON 对象；JSON_ARRAY(value, ...) 构造 JSON 数组。MySQL 用 -> 返回 JSON、->> 返回字符串（等价 JSON_UNQUOTE(JSON_EXTRACT(...))）。这些是 MySQL 的 JSON 方言，与 PostgreSQL 的 jsonb_* 函数不同。MySQL 8 支持 JSON_TABLE 等。

MySQL 用 JSON_EXTRACT/JSON_OBJECT/JSON_ARRAY 构造与提取 JSON，-> 返回 JSON、->> 返回字符串。

• MySQL JSON_TABLE
• 语法
• 实现

MySQL 8 的 JSON_TABLE(json_doc, path COLUMNS (col type PATH ...)) 把 JSON 展开为关系表。MySQL 的 JSON_TABLE 支持 COLUMNS 定义列（用 PATH 提取、type 指定类型，支持 FOR ORDINALITY 行号、EXISTS PATH 存在性、NESTED PATH 嵌套数组展开）。与 Oracle 相比，MySQL 的 JSON_TABLE 语法较简单，嵌套通过 NESTED PATH 实现，错误处理子句有限。它是 SQL:2016 的实现，但与 Oracle 的完整 JSON_TABLE 有差异。

MySQL 8 的 JSON_TABLE 用 COLUMNS/PATH 定义列，支持 NESTED PATH 嵌套，但语法较 Oracle 简单。

• JSON_VALUE
• JSON_QUERY
• 差异

SQL Server 的 JSON_VALUE(json, path) 返回 JSON 路径对应的标量值（字符串），JSON_QUERY(json, path) 返回 JSON 片段（对象/数组，保持 JSON 类型）。差异：JSON_VALUE 返回标量（字符串/数字等），JSON_QUERY 返回 JSON 对象或数组。若路径对应对象/数组，JSON_VALUE 返回 NULL（因为它只返回标量），需用 JSON_QUERY 取对象/数组。两者配合使用，JSON_VALUE 取标量字段、JSON_QUERY 取嵌套对象/数组。

JSON_VALUE 返回标量值，JSON_QUERY 返回 JSON 对象/数组。取对象/数组需 JSON_QUERY，取标量用 JSON_VALUE。

• IEEE 754 双精度
• JSON 数字精度
• 路径数值

SQL/JSON 路径语言中的数值运算基于 IEEE 754 双精度浮点（64 位），因此 JSON 数字在路径运算中可能丢失精度（如大整数、精度高于 15 位有效数字）。JSON 数字本身是文本形式，但 SQL/JSON 路径把数字转为 double，超出双精度范围或精度会舍入。处理：若需精确数字，应避免在路径中做浮点运算，或在数据库层用 numeric/decimal 类型处理。这是 JSON 数值精度限制（IEEE 754 双精度约 15-17 位有效数字）。

SQL/JSON 路径数值用 IEEE 754 双精度，大整数或高精度数字会丢失精度。精确运算需用 numeric。

• SQL/JSON 路径
• $ 根
• [*] 数组

jsonb_path_query(jsonb, '$.a[].b') 的路径语法：$ 表示 JSON 根节点；.a 访问顶层键 a；[] 遍历数组所有元素；.b 访问每个元素的键 b。整个路径从根出发，依次访问 a 的数组的每个元素的 b 属性，返回所有匹配的 b 值。SQL/JSON 路径支持 $（根）、.属性（对象成员）、[*]（数组遍历）、[n]（数组索引）、?()（过滤器）、@（当前节点）、.type() 等。该路径返回 a 数组中每个元素的 b 值。

$ 根、.a 顶层键、[*] 数组遍历、.b 属性。该路径返回 a 数组中每个元素的 b 值。

• 部分索引
• JSONB 条件
• 索引

部分索引（partial index）是带 WHERE 条件的索引，只索引满足条件的行。在 JSONB 上做部分索引：CREATE INDEX idx ON t USING GIN ((data)) WHERE data ? 'key' 或 WHERE data @> '{"type":"x"}'。只对满足 JSONB 条件的行建索引，缩小索引体积、提高命中率。也可对 JSONB 值表达式做部分索引。例如对 status 字段为特定值的 jsonb 列建索引。部分索引用于只须索引部分数据的高效场景。

部分索引用 WHERE 条件限定索引范围，JSONB 上可结合 @>、? 等条件建部分 GIN 索引。

• lax 宽松模式
• strict 严格模式
• 路径错误

jsonb_path_query/jsonb_path_exists 支持 lax 与 strict 模式。lax（默认）宽松模式：路径缺失或不匹配时自动容错（如省略不存在的键、数组自动展开、类型自动转换），不报错，返回空或 NULL；strict 严格模式：路径结构不匹配（如访问不存在的键、类型不匹配、数组索引越界）会报错（或返回 NULL 依 strict 设置）。lax 适合容错查询，strict 适合需要严格路径校验的场景。类型不匹配时 lax 尝试转换，strict 报错。

lax 宽松容错（缺失键/类型不匹配自动处理），strict 严格校验（路径/类型不匹配报错）。默认 lax。

• tsvector
• tsquery
• 全文检索

PostgreSQL 全文检索用两种类型：tsvector 是文档的向量（对文本分词后的词素 + 位置 + 权重），用 to_tsvector(config, text) 生成；tsquery 是查询（布尔运算的查询词），用 to_tsquery、plainto_tsquery、phraseto_tsquery 生成。匹配用 @@ 操作符：tsvector @@ tsquery 判断文档是否匹配查询。例如 to_tsvector('english', 'Hello world') @@ to_tsquery('english', 'hello') 返回 true。tsvector 存储分词结果可建 GIN 索引。

tsvector 是文档向量（分词），tsquery 是查询，@@ 匹配。to_tsvector 生成文档向量，to_tsquery 生成查询。

• 权重 A/B/C/D
• ts_rank
• 排序

tsvector 中每个词素可带权重（A、B、C、D），权重 A 最高、D 最低。权重用于标注词在文档中的重要性（如标题、正文）。ts_rank(vector, query) 计算文档与查询的匹配相关度（ranking），用于排序（ORDER BY ts_rank(...) DESC）。ts_rank 可接受权重数组参数（如 ts_rank(vector, query, '{0.1,0.2,0.4,1.0}')）调整各权重的系数。权重越高、匹配越多，相关度越高。ts_rank_cd 是覆盖密度排名。

权重 A/B/C/D 标记词重要性，ts_rank 计算相关度用于排序，可指定权重系数。

• GIN on tsvector
• 表达式索引
• 差异

GIN on tsvector 是对已存储的 tsvector 列建索引；GIN on to_tsvector(col) 是对文本列上即时生成的 tsvector 表达式建索引（表达式索引）。差异：若列本身就是 tsvector（已分词），直接用 GIN on tsvector 索引；若列是 text，需建表达式索引 GIN on to_tsvector(config, col)，查询时也要用相同表达式 to_tsvector(config, col) @@ query 才能命中索引。表达式索引需在查询中保持一致表达式。

tsvector 列直接 GIN 索引；text 列需表达式索引 GIN on to_tsvector(col)，查询表达式需一致。

• simple 字典
• english 字典
• 中文分词

PostgreSQL 全文检索字典：simple 字典把所有词当作词素（不做词干化、停用词），匹配简单；english 字典对英文做词干化（stemming，如复数、动词时态还原）和停用词（the、and 等）过滤，检索更精确；chinese_zh 是中文分词字典（需安装 zhparser/zht 等扩展，把中文按词切分）。差异：simple 不分词不处理，english 做英文词干化与停用词，中文需专用分词字典（chinese_zh）。选择字典影响检索效果。

simple 不做处理、english 词干化+停用词、中文需专用分词字典（chinese_zh）。字典影响分词效果。

• ts_headline
• 高亮
• 性能

ts_headline(document, query) 返回文档中与查询匹配的片段，并用 HTML 标签（ 等）高亮匹配词。用法：ts_headline(config, document, query, options)。它通常与全文检索配合，在结果中高亮关键词。性能：ts_headline 会对文档做分词并定位匹配，对长文档或大结果集有开销；建议只在结果集较小（如分页后）或对需要的行调用，避免对全表大结果集都做高亮。

ts_headline 高亮匹配词，返回带标签片段。性能开销较高，建议在结果集较小处调用。

• MySQL FULLTEXT
• PostgreSQL GIN
• 全文检索差异

MySQL FULLTEXT 索引用于全文检索，基于倒排索引，通过 MATCH ... AGAINST 查询，支持自然语言模式、布尔模式、查询扩展；PostgreSQL 用 GIN 索引 on tsvector 实现全文检索，用 @@ 匹配，支持 tsquery 布尔查询、权重、短语。差异：MySQL FULLTEXT 是内置全文索引（特定语法），默认不支持中文分词（需 ngram 插件）；PostgreSQL 全文检索更灵活（可配置分词器、字典、权重），但需显式 to_tsvector。两者都基于倒排思想，但语法与扩展性不同。

MySQL FULLTEXT 用 MATCH AGAINST，PostgreSQL 用 GIN on tsvector + @@。语法与分词扩展性不同。

• pg_trgm 模糊匹配
• 全文检索
• 取舍

pg_trgm 提供基于 trigram 的模糊匹配（相似度、LIKE、正则），适合拼写纠错、近似匹配、子串搜索；全文检索（tsvector + GIN）提供基于词素的语义检索，适合关键词、短语、权重检索。取舍：模糊匹配（拼写错误、子串包含）用 pg_trgm；语义/关键词检索（分词、词干、权重）用全文检索。pg_trgm 对中文效果有限（trigram 对 CJK 不擅长），中文多用全文检索分词。两者可结合。

pg_trgm 适合近似/子串匹配，全文检索适合语义/关键词检索。中文用全文检索分词更合适。

• 中文分词
• 集成方式
• 分词器

中文分词在 PostgreSQL 中通过扩展集成：zhparser（用 jieba 的结巴分词）或 zhparser 基于 Simple Chinese（SCWS）等提供中文分词；有的用 pg_jieba 等扩展。jieba、HanLP、IK 是独立的中文分词库，集成到 PostgreSQL 通常通过自定义 text search parser 或扩展（如 zhparser 基于 jieba 思路、pg_jieba 调用结巴）。集成后可配置中文分词器，用 to_tsvector('chinese', ...) 分词。选择取决于分词质量与扩展成熟度。

中文分词通过 zhparser/pg_jieba 等扩展集成，jieba/HanLP/IK 是底层分词库，配置为中文分词器。

• GIN 索引
• GiST 索引
• 全文检索取舍

GIN 与 GiST 都可用于全文检索（tsvector），但特点不同：GIN 索引体积大、构建慢，但查询快（精确匹配、适合频繁查询）；GiST 索引体积小、构建快，但查询慢（需访问堆行验证），且是 lossy 索引（可能需 recheck）。取舍：频繁查询、查询性能优先用 GIN；构建快、空间小、更新频繁用 GiST。GIN 是全文检索的推荐选择（查询快）。

GIN 查询快但体积大、构建慢，GiST 体积小、构建快但查询慢且 lossy。查询优先用 GIN。

• MATCH AGAINST
• 全文检索
• 模式

MySQL 全文检索用 MATCH(cols) AGAINST (expr [search_modifier])。search_modifier 可选：IN NATURAL LANGUAGE MODE（默认，自然语言、按相关度排序）、IN BOOLEAN MODE（布尔模式，支持 +、-、*、"" 等运算符）、WITH QUERY EXPANSION（查询扩展）。使用前需在列上建 FULLTEXT 索引。例如 SELECT * FROM t WHERE MATCH(title) AGAINST ('database' IN NATURAL LANGUAGE MODE)。BOOLEAN MODE 支持复杂布尔表达式。

MATCH(cols) AGAINST (expr [IN NATURAL LANGUAGE MODE / IN BOOLEAN MODE / WITH QUERY EXPANSION])，需 FULLTEXT 索引。

• 自定义字典
• 配置
• 同义词

PostgreSQL 配置自定义字典：创建同义词字典（CREATE TEXT SEARCH DICTIONARY synonym (TEMPLATE = synonym, SYNONYMS = 'my_syn') 定义同义词映射）、停用词字典（CREATE TEXT SEARCH DICTIONARY ... TEMPLATE = pg_catalog.simple 或配置停用词）、Ispell 字典（TEMPLATE = ispell 配词典文件）。然后创建自定义 text search configuration（CREATE TEXT SEARCH CONFIGURATION ...），指定 parser 与各 token 类型使用的字典。还可配置分词器（parser）与停用词。自定义字典用于专业领域分词与同义词。

用 CREATE TEXT SEARCH DICTIONARY 创建同义词/停用词/Ispell 字典，再建 TS CONFIGURATION 组合配置。

• to_tsquery
• plainto_tsquery
• phraseto_tsquery

三种 tsquery 构造差异：to_tsquery(config, 'word1 & word2') 支持显式布尔运算符（&、|、!）与括号，按词解析；plainto_tsquery(config, 'text') 把输入文本按空格分词后，用 AND（&）连接所有词（默认），且不保留布尔运算符；phraseto_tsquery(config, 'text') 把输入按短语处理，要求词按顺序相邻（短语匹配），用 <-> 连接。to_tsquery 灵活（布尔），plainto_tsquery 简单（AND），phraseto_tsquery 短语顺序匹配。

to_tsquery 支持布尔运算符、plainto_tsquery 用 AND 连接词、phraseto_tsquery 做短语顺序匹配。

• websearch_to_tsquery
• 搜索语法
• 引号

websearch_to_tsquery(config, text) 是 PostgreSQL 11+ 提供的类网页搜索的 tsquery 构造，把类似 Google 的搜索语法转为 tsquery。语法：普通词用 AND 连接；引号内的短语做短语匹配（"..."）；- 前缀表示排除（NOT）；OR 表示或。例如 websearch_to_tsquery('english', 'apple "iphone" -samsung') 表示匹配 apple AND 短语 "iphone" 且排除 samsung。它比 to_tsquery 更友好，适合用户输入。

websearch_to_tsquery 支持网页风格语法：词 AND、引号短语、- 排除、OR 或。适合用户搜索输入。

• EXTRACT
• YEAR
• EPOCH

EXTRACT(field FROM source) 提取时间字段：EXTRACT(YEAR FROM ts) 返回年份（整数，PostgreSQL 返回 numeric）；EXTRACT(EPOCH FROM ts) 返回自 Unix 纪元（1970-01-01 00:00:00 UTC）以来的秒数（浮点数，含小数秒）。EXTRACT 可提取 YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、DOW（星期几）、DOY（年内第几天）、EPOCH、QUARTER 等。EXTRACT 返回 numeric 类型（PostgreSQL），整数部分为字段值。

EXTRACT(YEAR FROM ts) 返回年份，EXTRACT(EPOCH FROM ts) 返回 Unix 纪元秒数。返回 numeric。

• AGE 函数
• EXTRACT YEAR
• 年龄计算

AGE(timestamp1, timestamp2) 返回两个时间戳之间的间隔（interval），按年/月/日表示，例如 AGE('2024-01-01', '2010-01-01') 返回 '14 years'。AGE(timestamp) 返回当前时间与时间戳的间隔。EXTRACT(YEAR FROM age(...)) 从 age 间隔中提取年份部分（int/numeric），用于计算年龄（完整年数）。例如计算年龄：EXTRACT(YEAR FROM AGE(birth_date))。注意 EXTRACT(YEAR) 只取间隔的年份部分，不包含月/日折算，所以是"整岁"。

AGE 返回 interval（年/月/日），EXTRACT(YEAR FROM age) 提取年数，用于算整岁。

• TIMESTAMP
• TIMESTAMPTZ
• AT TIME ZONE

TIMESTAMP（timestamp without time zone）存储墙钟时间，不涉及时区；TIMESTAMPTZ（timestamp with time zone）存储为 UTC 的内部值，显示时按会话时区转换。存储规则：TIMESTAMPTZ 把输入按会话时区转换为 UTC 存储，显示时转回会话时区。AT TIME ZONE 子句：timestamp AT TIME ZONE 'zone' 把 timestamp 解释为指定时区的时间并转换为 timestamptz（或反之）。例如 '2024-01-01 00:00' AT TIME ZONE 'Asia/Shanghai' 把该时间当作上海时区转换为 UTC。AT TIME ZONE 用于时区转换。

TIMESTAMP 无时区墙钟时间，TIMESTAMPTZ 存 UTC 显示按会话时区。AT TIME ZONE 做时区转换。

• RANDOM
• gen_random_uuid
• 性能

RANDOM() 返回 0 到 1 之间的随机浮点数（volatile），用于随机数、随机抽样；gen_random_uuid() 返回随机 UUID（v4），用于主键、标识符。性能：RANDOM() 每次调用产生随机数，volatile 不可缓存；gen_random_uuid() 生成 UUID 有开销但可接受。作为主键时，随机 UUID 导致索引插入随机、B-Tree 碎片化，性能不如自增序列；gen_random_uuid() 在 PostgreSQL 13+ 内置（pgcrypto 扩展早期提供）。RANDOM() 常用于 ORDER BY RANDOM() 抽样（但全表扫描）。

RANDOM() 随机浮点、gen_random_uuid() 随机 UUID。random UUID 作主键会碎片化，不如自增。

• CURRENT_TIMESTAMP
• LOCALTIMESTAMP
• 时区

CURRENT_TIMESTAMP 返回带时区的当前时间戳（timestamptz），等价于 NOW()；LOCALTIMESTAMP 返回不带时区的当前时间戳（timestamp），按会话时区表示的本地时间。差异：CURRENT_TIMESTAMP 是 timestamptz（含时区信息），LOCALTIMESTAMP 是 timestamp（无时区）。PostgreSQL 中 CURRENT_TIMESTAMP 返回 timestamptz，LOCALTIMESTAMP 返回 timestamp。两者都反映当前时间，但类型与时区语义不同。

CURRENT_TIMESTAMP 返回 timestamptz（含时区），LOCALTIMESTAMP 返回 timestamp（无时区）。类型差异。

• TIMESTAMP 字面量
• TIMESTAMPTZ 字面量
• 时区

TIMESTAMP '2024-01-01 10:00:00' 是不带时区的字面量（timestamp without time zone），按字面值存储；TIMESTAMPTZ '2024-01-01 10:00:00+08' 是带时区的字面量（timestamp with time zone），存储为 UTC（按指定时区转换）。差异：TIMESTAMP 字面量无时区信息，TIMESTAMPTZ 字面量含时区偏移（如 +08、+00、UTC），存储时转 UTC。无时区后缀的 TIMESTAMPTZ 字面量按会话时区解释。两者类型不同，比较/转换需注意。

TIMESTAMP 字面量无时区，TIMESTAMPTZ 字面量含时区偏移存 UTC。类型语义不同。

• AT TIME ZONE
• 时区转换
• 语义

timestamp AT TIME ZONE 'Asia/Shanghai' 的语义：把 timestamp 的值解释为上海时区的墙钟时间，并转换为 timestamptz（UTC 存储）。timestamptz AT TIME ZONE 'Asia/Shanghai' 反之：把 timestamptz 转换为上海时区的墙钟时间（返回 timestamp）。即 AT TIME ZONE 对 timestamp 赋予时区并转 UTC，对 timestamptz 显示指定时区时间。返回类型：timestamp AT TIME ZONE zone 返回 timestamptz；timestamptz AT TIME ZONE zone 返回 timestamp。

AT TIME ZONE 对 timestamp 解释为指定时区转 UTC（返回 timestamptz），对 timestamptz 显示指定时区（返回 timestamp）。

• jsonb_array_elements
• 数组展开
• 用法

jsonb_array_elements(jsonb) 把 JSONB 数组展开为多行（每行一个数组元素，类型 jsonb）。例如 jsonb_array_elements('[1,2,3]') 返回 3 行：1、2、3。它常用于 FROM 子句（配合 LATERAL 隐式）展开数组，配合 jsonb_each 等。jsonb_array_elements_text 返回 text 元素。用于把 JSON 数组扁平化为行，做行展开/分析。

jsonb_array_elements 把 JSONB 数组展开为多行（jsonb 元素），用于 FROM 展开数组。

• ABS
• SIGN
• 返回值

ABS(-5) 返回 5（绝对值），SIGN(-5) 返回 -1（符号：负数返回 -1）。SIGN(x) 返回：x>0 时 1，x=0 时 0，x<0 时 -1。因此 ABS(-5)=5，SIGN(-5)=-1。这是数学函数的简单语义。

ABS 绝对值、SIGN 符号（-1/0/1）。ABS(-5)=5，SIGN(-5)=-1。

• DATE 字面量
• 语法
• 标准

DATE 字面量语法是 DATE 'YYYY-MM-DD'，如 DATE '2024-01-01'。这是 SQL 标准语法，PostgreSQL 支持；MySQL 也支持 DATE '...' 字面量（返回 date）。类似地有 TIMESTAMP '...'、TIME '...'、INTERVAL '...'。DATE 字面量用单引号包裹日期字符串，格式为 YYYY-MM-DD。PostgreSQL 也允许 'YYYY-MM-DD' 直接隐式转换，但显式 DATE '...' 更明确。

DATE 'YYYY-MM-DD' 是标准日期字面量语法，PostgreSQL/MySQL 支持。

• DOW
• 星期几
• 0=Sunday

EXTRACT(DOW FROM date) 返回星期几的数字，DOW 从 0（Sunday）到 6（Saturday）。即 0=Sunday，1=Monday，...，6=Saturday。注意与 ISODOW 不同：ISODOW 从 1（Monday）到 7（Sunday）。DOW 是本地格式（0=Sunday），ISODOW 是 ISO 格式（1=Monday）。DOW 用于按星期几统计。

EXTRACT(DOW FROM date) 返回 0-6，0=Sunday。ISODOW 是 1-7（1=Monday）。

• POWER
• SQRT
• 精度

POWER(2, 10) 返回 1024（2 的 10 次方）。SQRT(2) 返回约 1.414213562373095，是浮点近似值（double），存在浮点精度误差（IEEE 754 双精度约 15-17 位有效数字）。POWER 返回与参数类型一致（整数参数返回 numeric 或整数），SQRT 返回 double 或 numeric。浮点精度：SQRT(2) 是近似值，不能精确表示，涉及精确比较时需注意。

POWER(2,10)=1024，SQRT(2)≈1.4142 是浮点近似，有精度误差。

• generate_series
• 步长
• 差异

generate_series(1, 10) 生成 1 到 10 的整数序列，步长为默认 1：1,2,3,...,10。generate_series(1, 10, 2) 指定步长为 2：1,3,5,7,9。差异在步长参数：三参数形式第三个参数为步长。无步长默认 1，指定步长则按步长递增。generate_series 支持正负步长与日期。

generate_series(1,10) 步长 1 生成 1..10，generate_series(1,10,2) 步长 2 生成奇数 1,3,5,7,9。

• jsonb_each_text
• 键值展开
• 用法

jsonb_each_text(jsonb) 把 JSONB 对象的每个键值对展开为两列（key text, value text），返回多行。例如 jsonb_each_text('{"a":1,"b":2}') 返回 (a, '1')、(b, '2')。它用于 FROM 子句展开 JSON 对象的键值，做行展开。jsonb_each 返回 key text、value jsonb；jsonb_each_text 返回 value 为 text。用于把 JSON 对象转成键值行。

jsonb_each_text 展开 JSONB 对象为 (key, value) 两列多行，value 为 text。用于对象键值展开。

• to_tsvector
• 分词
• 返回值

to_tsvector('english', 'hello world') 返回英文分词向量，形如 'hello':1 'world':2（词素 + 位置）。它把文本按英文分词器分词，转为 tsvector：每个词素后跟位置号。'hello' 在位置 1，'world' 在位置 2。这个 tsvector 可被 @@ 匹配与全文检索。返回类型是 tsvector。

to_tsvector 返回 tsvector，含词素与位置（如 'hello':1 'world':2）。

• ts_headline
• 高亮标签
• HTML

ts_headline 默认用 标签高亮匹配词，返回如 '...hello world...' 的片段。标签可通过选项自定义（StartSel/StopSel 指定起始/结束标签，如 StartSel=, StopSel=）。默认高亮标签是 和 。ts_headline 返回文档中匹配词被高亮标签包裹的片段文本。

ts_headline 默认用 ... 高亮匹配词，可用 StartSel/StopSel 自定义标签。

• MOD
• %
• DIV

MOD(a, b) 与 a % b 等价，都返回 a 除以 b 的余数（取模）。DIV 是整除（返回商，丢弃余数），MySQL 中 a DIV b 返回整数商。PostgreSQL 用 mod(a, b) 或 a % b（无 DIV 关键字，整除用 a / b 整数除法）。MySQL 支持 MOD、%、DIV。差异：MOD/% 求余数，DIV 求商。负数取模与数学定义相关（PostgreSQL 的 % 结果与被除数同号）。

MOD(a,b) 与 a%b 求余数，DIV 求整除商。MySQL 有 DIV，PostgreSQL 用 a/b 整数除法。

• NOW()
• 事务时间戳
• 特性

PostgreSQL 中 NOW() 返回事务开始时间戳（transaction_timestamp），在同一事务内多次调用返回相同值（事务内不变）。这是它的特性：NOW() 结果在整个事务内稳定，适合事务一致性记录。与之对比，clock_timestamp() 返回实际的当前时间（statement 级，随时间变化）。NOW() 的事务时间戳特性用于保证同一事务内时间一致性。MySQL 的 NOW() 是语句时间戳（可在多语句事务中变化）。

PostgreSQL 的 NOW() 是事务开始时间戳，事务内不变。clock_timestamp() 才是实时时钟。

• PI()
• double
• 精度

PI() 返回圆周率 π，类型为 double precision（双精度浮点），值约 3.141592653589793（约 15-16 位有效数字）。因为它返回 double，精度为 IEEE 754 双精度（约 15-17 位十进制有效数字）。PI() 用于三角函数和圆形计算。若需更高精度，可用 numeric 字面量（如 3.14159265358979323846）。

PI() 返回 double 类型的 π，约 3.141592653589793，精度为双精度浮点。

• jsonb_pretty
• 格式化
• 美观输出

jsonb_pretty(jsonb) 把 JSONB 值格式化为美观的缩进文本（text），用于可读性输出/调试。例如 jsonb_pretty('{"a":1,"b":[1,2]}') 返回带换行和缩进的 JSON 文本。它只影响显示格式，不改变数据本身。常用于命令行/调试时查看 JSONB 结构。注意它返回 text 类型，不是 jsonb。

jsonb_pretty 把 JSONB 格式化为缩进的美观文本（text），用于调试与可读输出。

• ts_rank
• 参数
• 相关度

ts_rank(vector, query [, weights]) 的参数：vector 是文档的 tsvector，query 是匹配的 tsquery，weights 是可选的四元权重数组（对应 A、B、C、D 权重的系数，默认 {0.1,0.2,0.4,1.0}）。ts_rank 计算文档与查询的相关度分数（浮点），用于排序。weights 控制不同权重的 TS 词在 rank 中的贡献。返回 float4。

ts_rank(vector, query, weights) 计算相关度，weights 是 A/B/C/D 权重系数数组，用于排序。

• tsvector 触发器
• 自动更新
• 更新模式

tsvector 的更新触发器模式：为避免每次查询都调用 to_tsvector 分词，可把 tsvector 存储为列，并用触发器在 INSERT/UPDATE 时自动更新 tsvector 列。创建 BEFORE INSERT OR UPDATE 触发器，在触发器中用 to_tsvector(config, text_col) 更新 tsvector 列。这样查询直接用 tsvector 列（可建 GIN 索引），无需每次分词。此模式提升查询性能，但写入时增加分词开销。

用触发器在 INSERT/UPDATE 时自动更新物化的 tsvector 列，查询直接用它，避免每次分词。