note3

12. 查询

这个课程并没有讲解查询，特别是group by，with语句这些，难道这些和MySQL里面一样吗？要问一下。

特性	PostgreSQL (Postgres)	MySQL (InnoDB)	影响
SQL 标准遵守	高度遵守。被认为是更接近 SQL 标准的数据库。	遵守程度较高，但有许多自身的扩展和宽松处理。	Postgres 更适合严格的、可移植性强的应用。
标识符大小写	默认区分大小写。	默认不区分大小写（取决于操作系统和配置）。	Postgres 中的 `SELECT "username" FROM users` 必须使用双引号且区分大小写，否则会被转为小写。
布尔值处理	拥有原生的 `BOOLEAN` 类型 (`true`, `false`)。	通常将 `BOOLEAN` 映射为 `TINYINT(1)` (0 或 1)。	Postgres 在逻辑操作上更直观。
空字符串与 NULL	严格区分。`''` 不等于 `NULL`。	在某些情况下，会将空字符串 (`''`) 视为 `NULL`。	Postgres 的数据完整性更高。

这是 PostgreSQL 在查询功能上最突出的优势所在：

特性	PostgreSQL 查询优势	MySQL 对应功能
JSON 支持	JSONB 类型：二进制存储，支持强大的索引（GIN 索引）和原生的操作符 (`@>`, `?`, `->`)，查询性能极高。	`JSON` 类型：在 8.0 之后支持，但原生操作符和索引优化不如 Postgres 强大。
数组支持	原生数组类型 (`VARCHAR[]`, `INTEGER[]`)。支持数组操作符 (`&&`, `@>`) 和 GIN 索引。	无原生数组类型。通常需要使用 JSON 或逗号分隔的字符串来模拟。
日期/时间	原生支持时区 (`TIMESTAMPTZ`)，存储时区信息。	通常存储为 `DATETIME` 或 `TIMESTAMP`，时区处理依赖于服务器和连接配置。
地理空间	通过 PostGIS 扩展，提供世界上最强大的开源地理空间查询功能。	有地理空间扩展，但功能和生态不如 PostGIS 成熟。
UUID	原生支持 `UUID` 类型，并有高性能的索引支持。	通常需要将其存储为 `VARCHAR` 或 `BINARY(16)`。

差异点	PostgreSQL 行为	MySQL 行为	解释
分页 (Pagination)	使用标准的 `OFFSET ... FETCH NEXT ... ROWS ONLY` (或旧版 `LIMIT ... OFFSET`)。	使用简洁的 `LIMIT [offset], [count]` 语法。	Postgres 的 `FETCH` 语法更符合 SQL 标准。
GROUP BY	严格执行 SQL 标准：`SELECT` 列表中的所有非聚合列都必须出现在 `GROUP BY` 子句中。	宽松处理：默认允许在 `SELECT` 列表中包含未在 `GROUP BY` 中的非聚合列（依赖 `ONLY_FULL_GROUP_BY` 模式）。	Postgres 强制确保聚合结果的逻辑正确性。
类型转换	转换严格，通常需要显式转换（例如 `::TEXT` 或 `CAST(...)`）。	隐式类型转换更宽松。	Postgres 减少了意外的数据类型错误。

这是 SQL:2008 标准引入的语法，功能上与 LIMIT/OFFSET 相同，但更具可读性和标准化。

SQL 示例： 查询第 3 页数据，每页 20 条记录。


1
SELECT id, name, created_at
2
FROM products
3
ORDER BY created_at DESC
4
OFFSET 40 ROWS
5
FETCH NEXT 20 ROWS ONLY; -- 或者 FETCH FIRST 20 ROWS ONLY

注意： 这种方法同样存在 OFFSET 带来的深层分页性能问题。