懂Mysql，就能让你秒杀PostgreSql

PostgreSQL 作为一款功能全面的开源数据库，其隐藏的强大特性足以替代多款专用数据库，让架构更简洁、成本更低、性能更优

古城小栈

509人浏览 · 2026-04-12 21:44:31

古城小栈 · 2026-04-12 21:44:31 发布

一、数据表： Mysql —> PgSql

示例1：

CREATE TABLE `news` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) DEFAULT '' COMMENT '标题',
  `del_at` int(10) NOT NULL DEFAULT '0' COMMENT '注销时间',
  `links` varchar(300) DEFAULT '' COMMENT '点击跳转',
  `goods_id` int(10) DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='快讯';




-- 1. 创建数据表
CREATE TABLE news (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title VARCHAR(100) DEFAULT '',
  del_at BIGINT NOT NULL DEFAULT 0,
  links VARCHAR(300) DEFAULT '',
  goods_id BIGINT DEFAULT 0,
);

-- 2. 添加表和列的注释
COMMENT ON TABLE news IS '快讯';
COMMENT ON COLUMN news.title IS '标题';
COMMENT ON COLUMN news.del_at IS '注销时间';
COMMENT ON COLUMN news.links IS '点击跳转';

1、标识符引号

MySQL: 使用反引号 (`aa`) 来包裹表名和列名
PostgreSQL: 使用双引号 (“aa”) 来包裹标识符
最佳实践是: 避免使用保留字和特殊字符作为标识符，并使用小写字母和下划线（snake_case）命名，这样就不需要引号。

2、自增主键

Mysql:
AUTO_INCREMENT + PRIMARY KEY (id) USING BTREE 分开写；
PgSql：
GENERATED ALWAYS AS IDENTITY + PRIMARY KEY 同行写。

PostgreSQL 中 SERIAL 和 IDENTITY 都能实现“自增主键”，但分别代表了两个时代。

IDENTITY 是从 PostgreSQL 10 版本开始引入的。这个版本是在 2017年 10月正式发布的。

分界线：2017年是一个分水岭。在此之前，PostgreSQL 实现自增主键主要靠 SERIAL 类型（或者手动管理 Sequence）。

标准化：PostgreSQL 10 引入 IDENTITY 是为了更好地兼容 SQL 标准，解决 SERIAL 类型长期以来存在的一些非标准行为和权限管理上的痛点。

所以，如果你维护的系统是在 2017 年之前建立的，很可能会看到大量的 SERIAL；而 2017 年之后的新项目，或者经过重构的系统，则更倾向于使用 IDENTITY。

以下是它们在定义主键时的核心区别：

1. 底层实现机制不同（最根本的区别）

SERIAL（伪类型）：
它并不是真正的数据类型。当你定义一个 SERIAL 列时，PostgreSQL 实际上在后台帮你做了三件事：
1. 创建一个独立的序列对象（Sequence）。
2. 将该列的默认值设置为 nextval('sequence_name')。
3. 将该序列的所有者关联到该列。
- 本质：它是一个拥有默认值的普通整数列。
IDENTITY（列属性）：
它是列定义的一部分，符合 SQL:2003 标准。序列是紧密绑定在列上的，你不能在不删除列的情况下单独删除这个序列。
- 本质：它是列的一种固有属性，数据库知道这个列的值是由系统自动生成的。

2. 删除表时的行为（维护性）

这是开发中最容易踩坑的地方：

SERIAL：如果你直接删除了表，底层的序列对象可能不会自动删除（特别是在某些复杂的重命名或权限操作后），导致数据库里残留“孤儿”序列，占用空间且容易造成混淆。
IDENTITY：序列与列是“生死与共”的。当你删除表或列时，关联的序列会自动且彻底地被清理掉。

3. 控制权的精细度（灵活性）

IDENTITY 提供了两种模式，而 SERIAL 只有一种（相当于“默认”模式）。

特性	SERIAL	IDENTITY (GENERATED BY DEFAULT)	IDENTITY (GENERATED ALWAYS)
标准性	PostgreSQL 独有	SQL 标准	SQL 标准
手动插入 ID	允许 (容易导致序列不同步)	允许 (系统不干涉)	禁止 (除非使用 `OVERRIDING SYSTEM VALUE`)
安全性	低 (容易误操作)	中	高 (强制系统生成)
定义参数	需在创建后 `ALTER SEQUENCE`	可直接在 `CREATE TABLE` 中定义	可直接在 `CREATE TABLE` 中定义

场景举例：如果你希望主键绝对由数据库生成，禁止任何业务代码手动插入 ID（防止数据迁移或脚本错误导致的主键冲突），IDENTITY (GENERATED ALWAYS) 是唯一选择。

4. 代码对比

旧版写法 (SERIAL):

CREATE TABLE users_old (
    id SERIAL PRIMARY KEY, -- 只是语法糖
    name VARCHAR(50)
);

新版写法 (IDENTITY - 推荐):

CREATE TABLE users_new (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 规范定义
    name VARCHAR(50)
);

4. SERIAL 的 5大短板

4.1 同步序列值

这是 SERIAL 最臭名昭著的短板。
如果你手动插入了一个很大的 ID，序列（报数器）是不知道的，它还会按原来的节奏报数，结果就会撞上。
如果序列是 2，你插入 100，下次序列发 3 没问题），但一旦序列增长到 100，就会突然报错，导致程序崩溃。
所以，只要手动干预了主键 ID，事后一定要记得“校准”一下序列。

4.2 缺乏强制约束（安全性短板）

这是 SERIAL 和 IDENTITY 最大的区别之一。

SERIAL 的弱点：它本质上只是给列设了一个 DEFAULT（默认值）。这意味着，任何有权插入数据的人，都可以手动指定一个 ID。数据库不会阻止你，除非你写代码时非常小心。这留下了 SQL 注入或人为误操作的风险。
IDENTITY 的优势：你可以定义 GENERATED ALWAYS。这相当于给列加了一把“锁”，明确告诉数据库：“这个 ID 只能由系统生成，谁手动插都不行”。如果有人尝试手动插入 ID，数据库会直接报错拒绝。

比喻：

SERIAL 就像是一扇自动门，虽然它会自动开，但你随时可以伸手把它推开。
IDENTITY (ALWAYS) 就像是一扇闸机，只有刷卡（系统生成）才能过，硬闯会报警。

4.3 权限管理的噩梦（运维短板）

这是一个非常隐蔽但令人头疼的问题。

SERIAL 的弱点：当你创建一个 SERIAL 列时，PostgreSQL 会创建一个独立的序列对象（比如 users_id_seq）。
- 如果你给用户 A 授予了表的 INSERT 权限，用户 A 仍然无法插入数据。
- 为什么？因为插入时需要调用序列的 nextval()，而用户 A 默认没有这个序列的 USAGE 权限。
- 后果：DBA 必须记得额外执行一条命令：GRANT USAGE ON SEQUENCE users_id_seq TO user_a;。如果忘了，程序就会报错“无权访问序列”。
IDENTITY 的优势：序列是依附于列的。当你授予用户表的 INSERT 权限时，数据库会自动处理序列的权限。你不需要单独管理序列权限，省心省力。

4.4 非标准与兼容性（架构短板）

SERIAL 的弱点：SERIAL 是 PostgreSQL 特有的“方言”，不是 SQL 标准。
- 如果你将来想把数据库从 PostgreSQL 迁移到 Oracle、SQL Server 或 DB2，SERIAL 语法是不通用的，你需要重写大量的建表语句。
IDENTITY 的优势：它是 SQL:2003 标准的一部分。使用 IDENTITY 编写的代码，在大多数现代主流数据库上都能直接运行或只需微调，迁移成本极低。

4.5 残留垃圾（清理短板）

SERIAL 的弱点：因为序列是独立对象，如果你删除了表（DROP TABLE），在某些特定操作下（或者如果你只是删除了列），底层的序列对象可能会变成“孤儿”残留在数据库里，占用资源且难以察觉。
IDENTITY 的优势：序列与列是强绑定的。列在序列在，列亡序列亡。清理得非常干净。

💡 建议

新项目：请务必使用 IDENTITY。它是未来的标准，语义更清晰，且能避免很多因为序列残留或不同步导致的诡异 Bug。
老项目维护：如果现有系统大量使用 SERIAL，可以继续使用，但要注意在手动插入数据后，记得同步序列值（使用 setval 函数），防止主键冲突。

总结来说，IDENTITY 就像是给主键加了一把“智能锁”，而 SERIAL 只是给门装了个“自动闭门器”，前者显然更安全、更规范。

3、类型映射

📊 核心数据类型映射对照表

MySQL 类型	PostgreSQL 推荐类型	说明与注意事项
数值型
`TINYINT(1)`	`BOOLEAN`	MySQL 常用 1/0 表示布尔，PG 有原生布尔类型。
`TINYINT`	`SMALLINT`	如果不用作布尔值，对应 PG 的小整数。
`INT` / `INTEGER`	`INTEGER`	完全兼容。
`BIGINT`	`BIGINT`	完全兼容。
`FLOAT` / `DOUBLE`	`REAL` / `DOUBLE PRECISION`	PG 中 `DOUBLE` 需写全为 `DOUBLE PRECISION`。
`DECIMAL(M,D)`	`NUMERIC(M,D)`	PG 中精确小数通常使用 `NUMERIC`，两者在 PG 中是同义词，但 `NUMERIC` 更标准。
字符串型
`CHAR(N)`	`CHAR(N)`	定长字符串，注意 PG 会严格填充空格。
`VARCHAR(N)`	`VARCHAR(N)`	变长字符串。
`TEXT` / `LONGTEXT`	`TEXT`	重点：PG 的 `TEXT` 性能极好，无需像 MySQL 那样为了性能避免使用它。
时间日期
`DATETIME`	`TIMESTAMP`	PG 中对应 `TIMESTAMP WITHOUT TIME ZONE`。
`TIMESTAMP`	`TIMESTAMPTZ`	PG 中对应 `TIMESTAMP WITH TIME ZONE`，推荐在 PG 中统一使用带时区的类型。
`DATE`	`DATE`	完全兼容。
二进制/大对象
`BLOB` / `LONGBLOB`	`BYTEA`	PG 使用 `BYTEA` 存储二进制数据。
`VARBINARY`	`BYTEA`	同上。
特殊类型
`JSON`	`JSONB`	强烈建议：PG 的 `JSONB` 是二进制格式，支持索引，查询性能远超 MySQL 的 JSON。
`ENUM`	`ENUM`	PG 的枚举是全局类型，需先 `CREATE TYPE` 再在表中引用。

💡 关键差异深度解析

1. 字符串类型：TEXT 的地位反转

MySQL: 历史上 VARCHAR 性能优于 TEXT，且 TEXT 不能直接设默认值。
PostgreSQL: TEXT、VARCHAR 和无限制的 VARCHAR 在性能上几乎没有区别。
建议: 在 PostgreSQL 中，除非有严格的长度校验需求，否则首选 TEXT，它更灵活且符合标准。

2. 布尔值：从模拟到原生

MySQL: 没有真正的布尔类型，通常用 TINYINT(1) 模拟，存储的是 0 和 1。
PostgreSQL: 拥有原生的 BOOLEAN 类型，存储 true/false。
迁移注意: 如果你的 MySQL 中 TINYINT(1) 存了除 0/1 以外的值（比如 2），迁移到 PG 的 BOOLEAN 时会报错。

3. 约束升级：从 UNSIGNED 属性到规则性约束

MySQL: UNSIGNED 定义一个无符号整数类型。
PostgreSQL:
- PostgreSQL 本身并不支持 UNSIGNED 属性，但有更灵活的方式来实现相同的效果——检查约束（CHECK Constraint）

-- 创建一个带有非负约束的表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    stock_count INTEGER CHECK (stock_count >= 0) -- 库存数量不能为负
);
-- 当你尝试插入或更新一个负的 stock_count 时，数据库会直接报错
-- PostgreSQL 的设计哲学更倾向于遵循 SQL 标准，并提供更通用、更强大的约束机制。

4. JSON 支持：JSONB 是杀手锏

MySQL: JSON 类型虽然支持，但索引和优化相对较弱。
PostgreSQL: 强烈建议使用 JSONB 而不是 JSON。
- JSON: 存储为原始文本，输入快，查询慢。
- JSONB: 存储为二进制格式，支持 GIN 索引，查询和修改性能极高，是 PG 区别于 MySQL 的一大优势。

5. 枚举类型：全局 vs 局部

MySQL: 枚举类型是绑定在表上的。表 A 可以有一个 ('red', 'blue') 的枚举，表 B 也可以有一个同名的但互不干扰。
PostgreSQL: 枚举类型是数据库级别的对象。你需要先执行 CREATE TYPE color AS ENUM ('red', 'blue');，然后在建表时使用 color 类型。这意味着你不能在不同表中随意创建同名的枚举类型。

📊 综合示例

CREATE TABLE `comprehensive_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `order_no` varchar(64) NOT NULL COMMENT '订单编号',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `amount` decimal(18,4) NOT NULL COMMENT '订单金额',
  `tax` decimal(10,2) DEFAULT '0.00' COMMENT '税费',
  `is_vip` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否VIP用户',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '订单状态: 1-待支付 2-已支付 3-已发货',
  `category` enum('ELECTRONICS','CLOTHING','FOOD') DEFAULT NULL COMMENT '商品分类',
  `tags` json DEFAULT NULL COMMENT '订单标签',
  `meta_info` json DEFAULT NULL COMMENT '元数据',
  `description` text COMMENT '订单描述',
  `memo` varchar(255) DEFAULT '' COMMENT '备注',
  `contract_file` blob COMMENT '合同文件',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted_at` datetime DEFAULT NULL COMMENT '删除时间(软删除)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_status` (`user_id`,`status`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_meta_info` ((CAST(`meta_info`->>'$.key' AS CHAR(64)))) COMMENT 'MySQL 8.0+ 函数索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='综合订单表';

🛠️ 转换后的 PostgreSQL 建表语句

在 PostgreSQL 中，我们需要将表选项（如引擎、字符集）移除，将数据类型进行精准映射，并使用独立的语句来添加注释和索引。

-- 1. 创建枚举类型 (PostgreSQL 的枚举是全局类型，需先创建)
CREATE TYPE order_category AS ENUM ('ELECTRONICS', 'CLOTHING', 'FOOD');

-- 2. 创建数据表
CREATE TABLE comprehensive_orders (
  -- [主键] BIGINT 对应 MySQL 的 bigint unsigned (防止溢出)，使用标准 IDENTITY 语法
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  
  -- [字符串] VARCHAR 保持不变
  order_no VARCHAR(64) NOT NULL,
  
  -- [数值] BIGINT 对应 MySQL 的 bigint
  user_id BIGINT NOT NULL,
  
  -- [高精度数值] NUMERIC 对应 MySQL 的 decimal
  amount NUMERIC(18,4) NOT NULL,
  tax NUMERIC(10,2) DEFAULT 0.00,
  
  -- [布尔] BOOLEAN 对应 MySQL 的 tinyint(1)
  is_vip BOOLEAN NOT NULL DEFAULT FALSE,
  
  -- [普通整数] SMALLINT 对应 MySQL 的 tinyint(4)
  status SMALLINT NOT NULL DEFAULT 1,
  
  -- [枚举] 使用刚才创建的自定义类型
  category order_category,
  
  -- [JSON] 强烈建议使用 JSONB 以获得更好的性能和索引支持
  tags JSONB,
  meta_info JSONB,
  
  -- [长文本] TEXT 对应 MySQL 的 text
  description TEXT,
  memo VARCHAR(255) DEFAULT '',
  
  -- [二进制] BYTEA 对应 MySQL 的 blob
  contract_file BYTEA,
  
  -- [时间] TIMESTAMPTZ 对应 MySQL 的 datetime (推荐带时区)
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at TIMESTAMPTZ
);

-- 3. 添加表和字段的注释
COMMENT ON TABLE comprehensive_orders IS '综合订单表';
COMMENT ON COLUMN comprehensive_orders.id IS '主键ID';
COMMENT ON COLUMN comprehensive_orders.order_no IS '订单编号';
COMMENT ON COLUMN comprehensive_orders.user_id IS '用户ID';
COMMENT ON COLUMN comprehensive_orders.amount IS '订单金额';
COMMENT ON COLUMN comprehensive_orders.tax IS '税费';
COMMENT ON COLUMN comprehensive_orders.is_vip IS '是否VIP用户';
COMMENT ON COLUMN comprehensive_orders.status IS '订单状态: 1-待支付 2-已支付 3-已发货';
COMMENT ON COLUMN comprehensive_orders.category IS '商品分类';
COMMENT ON COLUMN comprehensive_orders.tags IS '订单标签';
COMMENT ON COLUMN comprehensive_orders.meta_info IS '元数据';
COMMENT ON COLUMN comprehensive_orders.description IS '订单描述';
COMMENT ON COLUMN comprehensive_orders.memo IS '备注';
COMMENT ON COLUMN comprehensive_orders.contract_file IS '合同文件';
COMMENT ON COLUMN comprehensive_orders.created_at IS '创建时间';
COMMENT ON COLUMN comprehensive_orders.updated_at IS '更新时间';
COMMENT ON COLUMN comprehensive_orders.deleted_at IS '删除时间(软删除)';

-- 4. 创建索引
-- 唯一索引
CREATE UNIQUE INDEX uk_order_no ON comprehensive_orders (order_no);

-- 普通联合索引
CREATE INDEX idx_user_status ON comprehensive_orders (user_id, status);

-- 时间索引
CREATE INDEX idx_created_at ON comprehensive_orders (created_at);

4、注释

MySQL: 直接在列定义后面写 COMMENT ‘…’。
PostgreSQL: 使用 独立的 COMMENT ON COLUMN … IS ‘…’ 语句。
这样设计的好处是修改注释不需要修改表结构。

5、索引

MySQL: 在建表语句内部定义 KEY …。
PostgreSQL: 推荐使用 独立的 CREATE INDEX 语句。
这使得索引的管理更加灵活（例如可以在不锁表结构的情况下添加索引）。

6、默认值

在 PostgreSQL 中，是否设置 DEFAULT 约束与字段的数据类型（如 INTEGER, TEXT, JSONB 等）没有直接关系。
只要业务上允许该字段存储 NULL 值，你就可以不为它设置默认值。

MySQL 中，设置默认值（避免 NULL）通常能提升查询性能。

PostgreSQL 中，不设置默认值（允许 NULL）通常能大幅提升 DDL（表结构变更）的效率，但对查询性能的影响视情况而定。

要不要加默认值

简单来说：设计新表时建议加（为了数据规范），但在老表上加字段时建议先不加（为了性能和避免锁表）。

1. 场景一：设计新表（或数据量很小的表）

👉 建议：加上默认值

如果你正在创建一张新表，或者表里的数据很少，强烈建议设置合理的默认值。

理由：
- 数据安全：防止应用程序代码写得不严谨，插入数据时漏掉该字段，导致数据库里存了一堆 NULL，后续统计和查询会很麻烦。
- 性能无损：新表没有历史数据包袱，直接加默认值没有任何性能问题。
- 代码简洁：插入数据时不用每次都显式地写这个字段的值。

-- 新表推荐写法
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'active', -- 推荐：保证数据一致性
    created_at TIMESTAMP DEFAULT NOW()
);

2. 场景二：老表变更（生产环境，数据量大）

👉 建议：先不加默认值（或者采用“三步走”策略）

这是 PostgreSQL 开发中最需要注意的**“深坑”**。如果你的表已经有几百万、几千万行数据，千万不要直接执行 ALTER TABLE ... ADD COLUMN ... DEFAULT ...。

三步走策略实践

在 PostgreSQL 中对大表进行结构变更时，“三步走”策略是保障业务连续性和数据库稳定性的黄金法则。

它的核心思想是：将“高风险的元数据变更”拆解为“低风险的多次小事务操作”，从而避免长时间的表级锁（Access Exclusive Lock）导致业务中断。

下面我将以一个实际场景为例，详细说明每一步的操作、原理及代码。

🎯 场景背景

假设你有一张名为 orders 的订单表，数据量高达 5000 万行。
需求：需要新增一个 status 字段，要求：

不能为空 (NOT NULL)。
默认值为 'pending'。
历史数据也需要填充为 'pending'。

❌ 错误做法（直接执行）：

-- ⚠️ 警告：在 5000 万行数据的表上，这可能会锁表数小时！
ALTER TABLE orders ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'pending';

后果：数据库会尝试重写整张表，期间所有对该表的读写请求都会卡死。

第一步：添加字段（允许为空，无默认值）

目标：以毫秒级速度完成字段添加，不阻塞业务。

操作代码：

-- 1. 仅添加字段，允许 NULL，不设置默认值
ALTER TABLE orders ADD COLUMN status VARCHAR(20);

原理解析：

在 PostgreSQL 11 及以上版本，这条命令不会重写表数据。
数据库只是更新了系统目录（元数据），告诉系统“我有这个字段了，旧数据默认为 NULL”。
耗时：通常在 几毫秒到几秒 内完成，几乎无感知。

第二步：分批更新历史数据

目标：在后台慢慢把旧数据的 NULL 刷成 'pending'，避免长事务撑爆磁盘或锁表。

操作代码：
你需要编写一个简单的脚本（或使用存储过程）来循环执行更新。

-- 2. 创建一个分批更新的函数（可选，也可以直接在应用层写循环）
CREATE OR REPLACE FUNCTION update_orders_status() RETURNS void AS $$
DECLARE
    updated_rows INT;
BEGIN
    LOOP
        -- 每次更新 10,000 行
        UPDATE orders
        SET status = 'pending'
        WHERE ctid IN (
            SELECT ctid
            FROM orders
            WHERE status IS NULL
            LIMIT 10000
        );
        
        -- 获取本次更新的行数
        GET DIAGNOSTICS updated_rows = ROW_COUNT;
        
        -- 提交事务（非常重要！释放锁，避免长事务）
        COMMIT;
        
        -- 如果没有更多数据需要更新，退出循环
        EXIT WHEN updated_rows = 0;
        
        -- 可选：稍微休眠一下，给数据库喘息的机会，避免IO打满
        PERFORM pg_sleep(0.1); 
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 执行函数
SELECT update_orders_status();

原理解析：

LIMIT 10000：每次只锁 1 万行，而不是全表。其他业务查询可以访问未被锁住的行。
COMMIT：每批更新后立即提交，防止产生巨大的回滚段（XID wraparound 风险）和长时间持有行锁。
ctid：使用 ctid 定位行是 PostgreSQL 中更新大量数据时的高效技巧，可以避免重复扫描索引。

第三步：设置默认值和非空约束

目标：将字段属性“固化”，确保新插入的数据符合规范，且字段不再允许为空。

操作代码：

-- 3. 设置默认值
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';

-- 4. 设置非空约束
-- 注意：因为第二步已经把所有 NULL 都刷成了 'pending'，
-- 所以这里加 NOT NULL 会瞬间完成，不会报错。
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

原理解析：

SET DEFAULT：这只是修改元数据，告诉数据库“以后插入数据如果不填这个字段，就自动填 ‘pending’”。这对旧数据没有物理影响（旧数据物理上已经是 ‘pending’ 了）。
SET NOT NULL：数据库会检查表中是否有 NULL 值。因为第二步已经处理干净了，所以这个检查是瞬间完成的，不需要扫描全表重写数据。

7、表选项

- MySQL: ENGINE=InnoDB, AUTO_INCREMENT=3, DEFAULT CHARSET=utf8mb4, ROW_FORMAT=DYNAMIC, COMMENT='快讯' 等都是 MySQL 特有的 表级选项。
- PostgreSQL:
  - ENGINE: PostgreSQL 没有存储引擎的概念，直接删除。
  - AUTO_INCREMENT: 表的自增起始值在 PostgreSQL 中通过序列（Sequence）管理，通常在插入数据时体现，建表时可忽略。
  - CHARSET: PostgreSQL 数据库级别默认使用 UTF8 编码，无需在表级别指定。
  - ROW_FORMAT: PostgreSQL 没有此概念，直接删除。
  - COMMENT: 表注释同样需要使用独立的 COMMENT ON TABLE 语句。

二、时间类型

🕒 核心类型：选 TIMESTAMP 还是 TIMESTAMPTZ？

这是开发中最先遇到的问题。PostgreSQL 提供了两种主要的时间戳类型：

TIMESTAMP (无时区)
- 含义：存储字面值。你存 2023-10-01 12:00:00，读出来就是 2023-10-01 12:00:00。
- 缺点：它不包含时区信息。如果你的服务器在东京，用户在北京，同一个时间点显示的数字会不一样，容易造成混乱。
TIMESTAMPTZ (带时区)
- 含义：存储时转换为 UTC（世界标准时间），读取时根据客户端的时区设置自动转换。
- 优点：全球统一标准，永远不会搞错“这个时间点到底是几点”。

最佳实践

无脑选择 TIMESTAMPTZ。
在 PostgreSQL 中，TIMESTAMPTZ 是标准写法（它是 TIMESTAMP WITH TIME ZONE 的缩写）。即使你插入的数据没有带时区，PG 也会默认使用当前会话的时区将其转换为 UTC 存储。

为了让你直观地感受到 TIMESTAMP 和 TIMESTAMPTZ 的核心区别，我为你编写了一套完整的 SQL 演示代码。

这个示例将模拟一个跨国业务场景：

场景：服务器在 UTC 时区，但用户分别在上海（UTC+8）和纽约（UTC-5）。
操作：我们在上海时区插入同一个时间，然后切换到纽约时区去查询，看看会发生什么。

🧪 演示准备：创建对比表

我们将创建一张表，包含两种类型的列，以便对比。

-- 1. 创建测试表
CREATE TABLE time_demo (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(50),
    ts_no_tz TIMESTAMP,        -- 不带时区
    ts_with_tz TIMESTAMPTZ     -- 带时区
);

-- 2. 设置当前会话时区为上海 (模拟开发/服务器环境)
SET TIMEZONE = 'Asia/Shanghai';

📝 场景一：插入数据（在上海时区）

假设我们要记录一个“会议开始时间”，我们在上海时间的中午 12:00 插入数据。

-- 模拟在上海 (UTC+8) 的中午 12:00 插入数据
INSERT INTO time_demo (event_name, ts_no_tz, ts_with_tz) 
VALUES ('跨国会议', '2023-10-27 12:00:00', '2023-10-27 12:00:00');

此时数据库内部发生了什么？

ts_no_tz (TIMESTAMP): 数据库原样存储 2023-10-27 12:00:00。它不知道这是上海时间还是火星时间。
ts_with_tz (TIMESTAMPTZ): 数据库意识到当前是上海时区，于是将这个时间转换为 UTC 存储。
- 内部存储实际是：2023-10-27 04:00:00+00 (12点减去8小时)。

🔍 场景二：查询数据（在上海时区）

此时我们还在上海，查询刚才的数据。

-- 查看当前时区设置
SHOW TIMEZONE; -- 输出: Asia/Shanghai

-- 查询数据
SELECT 
    event_name, 
    ts_no_tz, 
    ts_with_tz 
FROM time_demo;

查询结果：

event_name	ts_no_tz	ts_with_tz
跨国会议	2023-10-27 12:00:00	2023-10-27 12:00:00+08

分析：看起来两者一样？别急，ts_with_tz 后面多了一个 +08，表示这是东八区的时间。

🌍 场景三：切换时区查询（模拟纽约用户）

现在，假设一个位于纽约（UTC-5）的同事连接到了同一个数据库。我们将会话时区切换为纽约时间来模拟。

-- 1. 模拟纽约同事登录，切换会话时区
SET TIMEZONE = 'America/New_York';

-- 2. 再次查询同一行数据
SELECT 
    event_name, 
    ts_no_tz, 
    ts_with_tz 
FROM time_demo;

查询结果（关键点来了！）：

event_name	ts_no_tz	ts_with_tz
跨国会议	2023-10-27 12:00:00	2023-10-27 00:00:00-04

💡 核心区别解析：

ts_no_tz (TIMESTAMP): 完全没变！ 依然显示 12:00。

后果：纽约同事会误以为会议是纽约时间的中午 12 点开始，但实际上那是上海时间的中午 12 点（此时纽约是凌晨 0 点）。这会导致严重的沟通事故。

ts_with_tz (TIMESTAMPTZ): 自动转换了！

数据库从内部存储的 UTC (04:00) 转换回了纽约时间。

结果显示为 00:00:00-04（夏令时）。这才是正确的对应时间关系。

🌏 时区设置：数据库需要设时区吗？

不需要。 实际上，强烈建议不要修改 PostgreSQL 配置文件中的时区设置。

最佳实践是将 PostgreSQL 数据库服务器的时区保持为默认的 UTC。

以下是详细的原因分析和正确的处理方案：

为什么不建议在配置文件中设置时区？

PostgreSQL 的时区配置（在 postgresql.conf 中设置 timezone = '...'）主要影响的是数据的显示格式和无时区时间的解释，而不是存储方式。

如果你在配置文件中强行设置为 Asia/Shanghai，会带来以下隐患：

存储混乱：
- TIMESTAMPTZ 类型在内部始终以 UTC 存储。
- 如果你把服务器时区改为北京时间，当你插入一个不带时区的时间字符串（如 '2023-10-01 12:00:00'）时，PG 会误以为这就是北京时间，然后把它转成 UTC 存进去。这会导致数据在跨时区查询时出现偏差。
运维困难：
- 如果你的应用部署在全球不同区域，或者你的服务器是云数据库（通常默认 UTC），修改配置文件会导致环境不一致。
- 日志文件的时间戳会变成当地时间，当你需要和运维团队（通常使用 UTC 对账）沟通时，会产生混淆。
Docker/容器化陷阱：
- 如果你使用 Docker 部署，修改配置文件往往不如直接传递环境变量方便。

正确的做法：三层时区策略

在开发中，我们通常遵循 “存储用 UTC，展示用本地” 的原则。

1. 数据库层（保持 UTC）

配置：保持 postgresql.conf 中的 timezone = 'UTC' 默认值。
目的：确保所有数据在底层都有一个统一的基准，无论你的服务器在东京、伦敦还是北京，存储的时间戳都是绝对一致的。

2. `连接层`（可选：设置会话时区）

如果你希望数据库返回的时间直接是北京时间（例如为了兼容旧系统），你可以在建立连接时设置会话时区，而不是修改配置文件。

方法 A：在 JDBC 连接串中设置

jdbc:postgresql://localhost:5432/mydb?currentSchema=public&TimeZone=Asia/Shanghai

方法 B：在 SQL 初始化时设置
每次应用启动连接池时，执行一条 SQL：
```
SET TIME ZONE 'Asia/Shanghai';
```
这样，虽然数据库存的是 UTC，但你的 Java/Python 程序拿到的时间自动变成了北京时间。

3. 应用层（推荐：代码处理）

这是最现代、最灵活的做法。

存储：数据库存 UTC。
读取：后端代码（Java/Go/Python）读取到的也是 UTC 对象（如 Instant 或 time.Time）。
展示：在返回给前端 JSON 时，或者在生成报表时，再由代码格式化为 Asia/Shanghai 的字符串。

⏱️ 更新时间自动刷新

MySQL 有 ON UPDATE CURRENT_TIMESTAMP 这种方便的语法，但 PostgreSQL 没有。在 PG 中，我们需要使用触发器来实现。

步骤 1：创建通用函数

这个函数用于将 updated_at 字段更新为当前时间。

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.updated_at = NOW();
   RETURN NEW;
END;
$$ language 'plpgsql';

步骤 2：绑定触发器

假设你的表叫 news，字段叫 update_at：

CREATE TRIGGER update_news_modtime
BEFORE UPDATE ON news
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

注：PostgreSQL 14+ 提供了一个内置扩展 moddatetime，也可以实现类似功能，但手写触发器是最通用、最可控的方式。

📅 时间范围搜索：千万别用 BETWEEN

这是 90% 的开发者都会犯的错误。

❌ 错误写法

-- 想要查询 2023-10-01 这一整天的数据
WHERE created_at BETWEEN '2023-10-01' AND '2023-10-31'

问题：

BETWEEN 是闭区间（包含首尾）。
'2023-10-31' 会被隐式转换为 '2023-10-31 00:00:00'。
结果：你只会查到 10月31日 0点0分0秒的那一条数据，31号剩下的 23 小时 59 分的数据全丢了。

✅ 正确写法：左闭右开 `[start, end)`

WHERE created_at >= '2023-10-01 00:00:00' 
AND created_at < '2023-11-01 00:00:00'

优点：

不需要关心那一秒是不是有微秒（比如 23:59:59.999999）。
逻辑清晰：大于等于 1号，且小于下个月1号。
索引友好：这种写法能完美利用 created_at 上的 B-tree 索引。

🛠️ 常用时间函数

PostgreSQL 的时间函数非常强大，以下是开发中最常用的：

函数/操作符	说明	示例
`NOW()`	获取当前事务开始的时间（带时区）	`SELECT NOW();`
`CURRENT_TIMESTAMP`	同上，SQL 标准写法	`SELECT CURRENT_TIMESTAMP;`
`date_trunc`	日期截断（非常重要）	`date_trunc('day', NOW())` -> 当天 00:00:00`date_trunc('month', NOW())` -> 当月 1号 00:00:00
`EXTRACT`	提取部分	`EXTRACT(HOUR FROM NOW())` -> 当前小时`EXTRACT(DOW FROM NOW())` -> 星期几
`AGE`	计算时间差	`AGE(timestamp1, timestamp2)`
`INTERVAL`	时间间隔计算	`NOW() - INTERVAL '7 days'` -> 7天前

实战：按天/按月统计

如果你想统计“每天的订单量”，使用 date_trunc 是最标准的做法：

SELECT 
    date_trunc('day', created_at) as stat_day, 
    COUNT(*) 
FROM orders 
GROUP BY 1 
ORDER BY 1;

实战：时间类型的默认值

CREATE TABLE example_table (
    id SERIAL PRIMARY KEY,
    -- 使用 NOW()
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- 仅日期，使用 CURRENT_DATE
    created_date DATE DEFAULT CURRENT_DATE
);

重点说明：
加上 WITH TIME ZONE（也就是 TIMESTAMPTZ）并不是为了存储“北京时间”或“纽约时间”这个标签，而是为了存储一个全球统一的、绝对的 时间点。

如果你不加：一旦你的服务器时区设置变了，或者你在不同的时区查询数据，你的日志时间就会乱套。
如果你加了：数据库会统一存为 UTC。当你查询时，数据库会根据你的客户端设置，自动把 UTC 时间换算回你当地的显示时间（比如自动把 UTC 02:00 变回北京时间 10:00）。

三、事务

核心概念

PostgreSQL 的事务严格遵循 ACID 四大特性，这是理解事务的基础：

原子性 (Atomicity)
事务中的所有操作是一个整体，要么全部成功，要么全部失败回滚。例如，银行转账时，从A账户扣款和向B账户加款这两个操作必须同时成功或同时失败。
一致性 (Consistency)
事务执行前后，数据库必须从一个一致状态转换到另一个一致状态。这意味着所有的数据完整性约束（如主键、外键、唯一性约束）都必须得到遵守。
隔离性 (Isolation)
多个并发事务之间是相互隔离的。一个事务的中间状态对其他事务是不可见的，这避免了并发操作导致的数据混乱。PostgreSQL 通过多版本并发控制（MVCC）来实现高效的隔离。
持久性 (Durability)
一旦事务被提交（COMMIT），它对数据库的修改就是永久性的，即使系统发生故障（如断电）也不会丢失。这通过预写式日志（WAL）来保证。

🛠️ 基本操作：BEGIN, COMMIT, ROLLBACK

在 PostgreSQL 中，默认情况下每条 SQL 语句都是一个独立的事务（自动提交）。要手动控制事务，需要使用以下命令：

BEGIN; 或 START TRANSACTION;: 开启一个新事务。
COMMIT;: 提交事务，将所有修改永久保存到数据库。
ROLLBACK;: 回滚事务，撤销所有未提交的修改。

示例：一个成功的转账事务

-- 1. 开启事务
BEGIN;

-- 2. 执行一系列操作
-- 从账户 1 扣除 100 元
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 向账户 2 增加 100 元
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 3. 提交事务，操作永久生效
COMMIT;

示例：一个失败回滚的事务

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- 假设这里检查发现账户 1 余额不足
-- 我们决定取消整个操作
ROLLBACK;
-- 此时，账户 1 的余额不会发生任何变化

🔄 高级用法：保存点 (Savepoint)

对于复杂的事务，你可能不希望因为一个小小的错误就回滚整个事务。这时可以使用保存点（Savepoint），它允许你将事务回滚到某个特定的中间点，而不是全部撤销。

SAVEPOINT savepoint_name;: 创建一个保存点。
ROLLBACK TO SAVEPOINT savepoint_name;: 回滚到指定的保存点。
RELEASE SAVEPOINT savepoint_name;: 释放一个保存点，表示不再需要回滚到该点。

示例：部分回滚

BEGIN;

-- 第一步：创建订单
INSERT INTO orders (product_id, quantity) VALUES (101, 2);
-- 设置一个保存点
SAVEPOINT after_order;

-- 第二步：扣减库存
UPDATE inventory SET stock = stock - 2 WHERE product_id = 101;

-- 假设库存扣减失败（例如库存不足）
-- 我们只回滚到保存点，订单记录依然保留
ROLLBACK TO SAVEPOINT after_order;

-- 此时可以执行其他逻辑，比如记录一个待处理状态
INSERT INTO order_status_log (order_id, status) VALUES (currval('orders_id_seq'), 'PENDING_STOCK');

COMMIT;

并发控制：事务隔离级别

当多个用户同时操作数据库时，可能会出现脏读、不可重复读、幻读等问题。PostgreSQL 通过设置事务隔离级别来解决这些问题。

隔离级别	脏读	不可重复读	幻读	说明
Read Committed (默认)	否	是	是	事务只能看到其他事务已提交的数据。
Repeatable Read	否	否	是	事务内多次读取同一数据结果一致。
Serializable	否	否	否	最高级别，事务串行执行，完全隔离。

如何设置隔离级别？

-- 在事务开始时设置
BEGIN ISOLATION LEVEL REPEATABLE READ;

-- 或者在事务开始后设置（必须在第一条读写语句之前）
START TRANSACTION ISOLATION LEVEL SERIALIZABLE;

📚 实战案例：电商订单处理

这是一个典型的需要事务来保证数据一致性的场景。

-- 假设我们要创建一个订单，需要：1.扣库存 2.创建订单 3.记录日志

BEGIN;

-- 1. 扣减库存
UPDATE products 
SET stock = stock - 1 
WHERE product_id = 100 AND stock >= 1;

-- 检查上一条 UPDATE 是否成功影响了行（即库存是否充足）
-- 在应用程序中，我们会检查 affected_rows
-- 这里为了演示，我们假设库存充足

-- 2. 创建订单记录
INSERT INTO orders (order_id, customer_id, product_id, quantity)
VALUES (5001, 3001, 100, 1);

-- 3. 记录交易日志
INSERT INTO transaction_log (order_id, action, timestamp)
VALUES (5001, 'order_created', NOW());

-- 所有操作都成功，提交事务
COMMIT;

如果在任何一步发生错误（例如库存不足导致 UPDATE 失败），应用程序可以捕获异常并执行 ROLLBACK;，确保数据库状态不会混乱。

和 Mysql 的区别

表面上看，两者都遵循 ACID 标准，都支持 BEGIN、COMMIT 和 ROLLBACK，但在底层实现和极端场景下的行为，两者有着本质的区别。

简单来说：MySQL (InnoDB) 的事务是为了“高性能”做了妥协，而 PostgreSQL 的事务是为了“严谨性”做到了极致。

以下是它们最明显的几个差别：

🏗️ 架构层面的差别：引擎 vs 核心

MySQL (插件式)：
- MySQL 的事务支持是依赖存储引擎的。
- 只有 InnoDB 引擎支持事务。如果你不小心用了 MyISAM 引擎（早期版本默认），COMMIT 是无效的，数据直接写入磁盘，无法回滚。
- 这意味着在 MySQL 中，事务不是“全局”的，而是引擎层面的特性。
PostgreSQL (一体化)：
- 事务是核心架构的一部分，对所有表都生效。
- 你不需要选择引擎，PostgreSQL 的所有操作默认都在事务中运行，不存在“不支持事务的表”。

🔨 DDL 事务支持：最致命的区别

这是开发中最大的痛点。假设你要在一个事务里修改表结构（DDL）并插入数据。

MySQL (DDL 导致隐式提交)：
- 在 MySQL 中，执行 CREATE、ALTER、DROP 等语句时，数据库会强制先提交当前事务。
- 后果：如果你在事务中间修改了表结构，然后报错了想回滚，修改表结构的操作无法回滚，但数据操作回滚了。这会导致数据库结构和数据不一致。
PostgreSQL (完全支持 DDL 事务)：
- PostgreSQL 的 DDL 操作（如 ALTER TABLE）也是事务性的。
- 后果：如果你在一个事务里改了表结构，然后报错了，执行 ROLLBACK，表结构会完美恢复到修改前的状态，就像什么都没发生过一样。

场景演示

-- PostgreSQL 示例
BEGIN;
ALTER TABLE users ADD COLUMN age INT; -- 修改表结构
INSERT INTO users (name, age) VALUES ('Alice', 25);
-- 假设这里报错了
ROLLBACK; 
-- 结果：age 列没加上，Alice 也没插入。一切恢复原状。

-- MySQL 示例
START TRANSACTION;
ALTER TABLE users ADD COLUMN age INT; -- 触发隐式提交！
INSERT INTO users (name, age) VALUES ('Alice', 25);
-- 假设这里报错了
ROLLBACK;
-- 结果：age 列已经加上了（无法回滚），但 Alice 没插入。
-- 你的数据库现在处于“结构变了，但数据没跟上”的尴尬状态。

🚦 默认隔离级别：保守 vs 激进

虽然两者都支持四种标准隔离级别，但默认值不同：

MySQL (InnoDB)：默认是 可重复读 (Repeatable Read, RR)。
- 这是一个比较严格的级别。MySQL 通过 间隙锁 (Next-Key Lock) 来解决幻读问题。
- 优点：数据一致性更强，适合处理复杂的并发写入。
- 缺点：锁的开销大，高并发下容易发生死锁。
PostgreSQL：默认是 读已提交 (Read Committed, RC)。
- 这是一个比较宽松的级别。PostgreSQL 依靠其强大的 MVCC 机制，让读操作不阻塞写，写操作不阻塞读。
- 优点：并发性能极高，死锁概率低。
- 注意：如果你需要 MySQL 那种级别的严格隔离，必须在 PG 中手动设置 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;。

♻️ MVCC 实现机制：Undo Log vs 多版本

这是导致两者维护成本不同的根本原因。

MySQL (Undo Log)：
- 当一行数据被更新时，MySQL 会把旧数据写到 Undo Log 里，新数据覆盖旧数据（或者在聚簇索引中更新）。
- 清理：后台线程自动清理 Undo Log，对应用透明，基本不需要人工干预。
PostgreSQL (Append Only)：
- 当一行数据被更新时，PostgreSQL 不会覆盖旧数据，而是把新数据作为新版本写入磁盘，旧数据标记为“过期”（死元组）。
- 清理：必须依赖 VACUUM 进程来回收这些过期数据的空间。
- 风险：如果 VACUUM 跟不上更新速度，表文件会无限膨胀（Bloat），导致性能下降。这是 PG 运维中需要特别关注的点。

四、索引

PostgreSQL 和 MySQL 在索引方面的设计理念差异很大。简单来说，MySQL 的索引是为了“快”（专注于高性能的 B+ 树），而 PostgreSQL 的索引是为了“全”（提供多种索引类型以适应复杂场景）。

以下是两者的详细对比和代码示例：

核心差异概览

特性	MySQL (InnoDB)	PostgreSQL
默认索引结构	B+ 树 (聚簇索引)	B- 树 (堆表结构)
索引丰富度	较少 (B+Tree, Hash, Fulltext, R-Tree)	极多 (B-Tree, Hash, GIN, GiST, BRIN, SP-GiST)
函数/表达式索引	支持 (MySQL 8.0.13+)，需特殊语法	原生支持，非常强大且常用
部分索引	不支持 (只能用前缀索引模拟)	支持 (`WHERE` 子句)，非常实用
包含列索引	支持 (`INCLUDE` 语法)	支持 (`INCLUDE` 语法)
全文索引	基础支持	强大 (配合 GIN 索引)

默认索引结构：B+ 树 vs B 树

这是最底层的区别，决定了数据存储方式。

MySQL (聚簇索引)

特点：InnoDB 的主键索引（聚簇索引）的叶子节点直接存储行数据。
优势：主键查询极快，范围查询效率高。
劣势：二级索引（非主键索引）的叶子节点存储的是主键值。如果回表（通过二级索引查数据），需要先查主键，再查主键索引，多一次查找。

PostgreSQL (堆表结构)

特点：索引（无论是 B-Tree B树 🌳 还是其他）的叶子节点存储的是数据的物理地址（CTID）。数据和索引是分开存储的。
优势：二级索引回表通常比 MySQL 快（直接定位物理行）。
劣势：更新数据时（MVCC 机制），会产生新版本的行，导致索引也需要更新或产生死元组，对写入性能有一定影响。

高级功能对比（带代码示例）

这是 PostgreSQL 索引最“杀手级”的优势所在。

1. 函数索引 / 表达式索引

在开发中，我们经常遇到 WHERE LOWER(email) = '...' 这样的查询。

MySQL (8.0.13+)：
支持，但语法略显繁琐，实际上是创建了一个“隐藏生成列”。

-- MySQL 写法
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100),
    -- 必须在这里定义一个虚拟列
    email_lower VARCHAR(100) GENERATED ALWAYS AS (LOWER(email)) VIRTUAL,
    -- 然后对这个虚拟列建索引
    INDEX idx_email_lower (email_lower)
);

PostgreSQL：
原生支持，直接对表达式建索引，非常直观。

-- PostgreSQL 写法
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100)
);

-- 直接对函数结果建索引
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

效果：当你查询 WHERE LOWER(email) = 'test@example.com' 时，PG 会自动利用这个索引。

2. 部分索引

如果你只需要索引表中一部分数据（例如“未支付订单”），PostgreSQL 的部分索引可以极大地节省空间并提高性能。

MySQL：
不支持。你只能对整个列建索引，或者用前缀索引（如 name(10)）来节省空间，但这无法过滤行。

PostgreSQL：
支持 WHERE 子句。

-- 场景：订单表大部分是“已支付”，只有少量“未支付”
-- 我们只给“未支付”的订单建索引
CREATE INDEX idx_orders_unpaid ON orders (created_at) 
WHERE status = 'UNPAID';

效果：索引体积非常小，查询未支付订单极快。

3. 特殊索引类型

MySQL 主要依赖 B+ 树，而 PostgreSQL 针对不同的数据类型提供了专门的索引。

GIN 索引 (通用倒排索引)：

用途：处理数组、JSONB、全文检索。
场景：查询包含某个标签的文章，或者 JSON 字段中包含某个键值对。

-- PostgreSQL: 给 JSONB 字段建索引
CREATE INDEX idx_logs_data ON logs USING GIN (data);
-- 查询时自动走索引
SELECT * FROM logs WHERE data @> '{"type": "error"}';

BRIN 索引 (块范围索引)：

用途：处理超大表的时间序列数据。
场景：日志表，数据按时间插入。

-- PostgreSQL: 给几十亿行的日志表建索引
-- 索引体积极小（只有 B-Tree 的几百分之一）
CREATE INDEX idx_logs_time ON huge_logs USING BRIN (created_at);

包含列索引

两者现在都支持 INCLUDE 语法，用于实现覆盖索引，避免回表。

-- 场景：经常查询 user_id 和 status，但不需要其他列
-- MySQL 8.0+ 和 PostgreSQL 都支持
CREATE INDEX idx_user_status ON orders (user_id) INCLUDE (status);

区别：MySQL 的二级索引叶子节点存的是主键，而 PG 存的是物理地址。但在 INCLUDE 场景下，两者都能极大提升性能。

总结

MySQL 的索引：简单、直接、高效。对于常规的 Web 业务（增删改查），B+ 树已经足够应付 95% 的场景。
PostgreSQL 的索引：复杂、灵活、强大。如果你有复杂的查询需求（如模糊查询优化、JSON 查询、数组查询、条件索引），PostgreSQL 的索引体系能提供降维打击般的性能优势。

五、日志系统

PostgreSQL 的日志系统与 MySQL 类似，也由其核心日志和辅助日志共同构成，共同保障了数据库的事务持久性、可恢复性和可观测性。

PostgreSQL 的日志系统主要由以下几类日志组成：

🗂️ 预写式日志

预写式日志（Write-Ahead Log，简称 WAL）是 PostgreSQL 日志系统的核心，它确保了数据的持久性和崩溃后的恢复能力。

作用：WAL 遵循“日志先行”的原则。在对数据文件进行任何修改之前，所有变更操作都会先被记录到 WAL 中。当数据库发生崩溃时，可以通过重放（Replay）WAL 日志来恢复数据到崩溃前的状态，确保数据不丢失。此外，WAL 也是实现时间点恢复（PITR）和流复制（Streaming Replication）的基础。
位置：默认位于数据目录下的 pg_wal/ 文件夹中（在 PostgreSQL 10 之前的版本中，该目录名为 pg_xlog）。
特点：WAL 日志是强制开启的，其内容是二进制格式，对人类不具可读性。

WAL vs. Binlog：核心区别：

特性	PostgreSQL WAL	MySQL Binlog
日志类型	物理日志	逻辑日志
记录内容	记录数据页（Page）或数据块（Block）的物理变化。例如：“在文件X的第Y页，将第Z个字节从A改为B”。	记录行级别的逻辑变更。例如：`INSERT INTO table VALUES (1, 'a')` 或 `UPDATE table SET name='b' WHERE id=1`。
复制方式	基于物理复制，从库直接重演主库的 WAL 日志，实现字节级的完全一致。	基于逻辑复制，从库接收并重新执行 binlog 中的 SQL 事件，实现最终一致性。
主要优势	复制效率极高，主从数据强一致，对主库性能影响相对较小。	逻辑清晰，易于理解和解析，支持更灵活的复制拓扑（如链式、双主）。

📝 服务器运行日志

服务器运行日志记录了 PostgreSQL 数据库实例在运行过程中的各种事件和信息，是进行日常监控、故障排查和性能分析的主要工具。

作用：记录数据库的启动和关闭信息、错误信息、慢查询、连接和断开事件、检查点活动、锁等待等。通过分析这些日志，可以了解数据库的运行状态，定位性能瓶颈和错误根源。
位置：默认位于数据目录下的 pg_log/ 文件夹中，但具体路径和文件名可以通过配置文件 postgresql.conf 进行自定义。
特点：此日志默认是关闭的，需要配置 logging_collector = on 来启用。与 WAL 不同，服务器运行日志是人类可读的文本文件，并且可以配置为按时间或大小自动轮转。

📊 事务状态日志

事务状态日志（Commit Log），通常被称为 pg_clog（在较新版本中为 pg_xact），用于记录事务的提交状态。

作用：它记录了每个事务是已提交、已中止还是仍在运行。这是一个非常重要的元数据，数据库在启动时通过检查它来确定哪些事务需要回滚，哪些需要确认。
位置：位于数据目录下的 pg_xact/ 文件夹中。
特点：强制开启，对人类不具可读性。这个文件通常非常小，但至关重要，不应随意删除或修改。

🔍 其他辅助日志

除了上述核心日志，PostgreSQL 还提供了其他几种日志或日志相关的功能，以满足特定需求。

审计日志：用于记录用户的数据库活动，以满足安全合规性要求。这通常通过安装 pgaudit 扩展来实现，可以精细地配置需要审计的操作（如读、写、DDL 等）。
统计信息：虽然不完全是传统意义上的“日志”，但 pg_stat_statements 扩展模块会收集并记录所有执行过的 SQL 语句的性能统计信息（如执行次数、总耗时、平均耗时等），是进行 SQL 性能分析和优化的重要工具。

📌 总结对比

为了更清晰地理解，可以将 PostgreSQL 的主要日志与 MySQL 的日志进行一个大致上的对应：

PostgreSQL 日志	大致对应 MySQL 日志	核心作用
WAL	Redo Log + Bin log	保证数据持久性，用于崩溃恢复和时间点恢复。
服务器运行日志	慢查询日志、错误日志	记录运行状态、错误、慢查询等，用于监控和排查。
`pg_clog`/`pg_xact`	Undo Log (部分功能)	记录事务的提交状态，用于事务管理。
`pgaudit` (扩展)	通用日志/审计插件	记录用户操作，用于安全审计。

六、postgresql.conf 配置文件

1、修改默认的本地链接

默认情况下的配置文件这一项配置为：

#listen_addresses = 'localhost'		# what IP address(es) to listen on;

因为行首有一个 # 号，这行配置是无效的。PostgreSQL 会使用默认值，而默认值就是 localhost。
这意味着数据库只接受来自服务器本机的连接，远程客户端无法连接。

✅ 解决方案
你需要修改这一行，让它生效并监听所有网络接口。

打开 postgresql.conf 文件。
找到 #listen_addresses = ‘localhost’ 这一行。
将其修改为：

listen_addresses = '*'

重启 PostgreSQL 服务才能生效。

常见配置优化

七、主从复制

PostgreSQL 的主从复制（也称为流复制）配置起来非常直观。下面我将以两台服务器为例，为你详细描述从准备到验证的完整配置流程。

环境准备

我们将使用两台服务器，假设它们的操作系统均为 CentOS 7 或 Ubuntu 20.04，并已安装相同版本的 PostgreSQL（例如 15.x）。

角色	主机名/IP	说明
主库 (Primary)	192.168.1.10	负责所有读写操作，并作为复制源。
从库 (Standby)	192.168.1.11	从主库同步数据，通常用于只读查询或灾备。

注意：以下步骤均需在两台服务器的 postgres 用户下执行，或使用 sudo -u postgres 提权。
sudo -u postgres 的意思就是：
你（当前用户）：就像一个普通客户。
postgres 用户：就像银行的金库管理员，只有他能进入核心区域（数据库）。
sudo：就像你的VIP通行证。你向保安（系统）出示通行证，证明你有特权。
-u postgres：意思是“我要借用一下金库管理员的工牌”。

第一步：配置主库 (192.168.1.10)

1. 修改核心配置文件 `postgresql.conf`

找到并编辑主库的 postgresql.conf 文件。文件路径通常为 /var/lib/pgsql/15/data/postgresql.conf (CentOS) 或 /etc/postgresql/15/main/postgresql.conf (Ubuntu)。

需要修改或添加以下关键参数：

# 允许所有IP连接，生产环境建议指定从库IP
listen_addresses = '*'

# WAL日志级别，必须设置为replica才能支持流复制
wal_level = replica

# 允许的最大复制连接数，即最多可以有多少个从库
max_wal_senders = 10

# 保留的WAL日志大小，防止从库因网络延迟等原因落后太多时WAL被过早清理
wal_keep_size = 1GB

# 允许从库提供只读查询（热备）
hot_standby = on

2. 配置访问控制文件 `pg_hba.conf`

编辑主库的 pg_hba.conf 文件（与 postgresql.conf 在同一目录），添加规则以允许从库连接进行复制。

# 允许从库(192.168.1.11)使用replicator用户进行复制连接
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     replicator      192.168.1.11/32         scram-sha-256

说明：
【1】DATABASE 项配置的 replication 是固定写法，并不是指你要复制的业务数据库（比如 mydb 或 testdb），而是一个专门表示允许建立复制连接的协议标识符。

【2】 METHOD 可以是 md5 或 scram-sha-256，取决于你的 PostgreSQL 版本和密码加密方式。
【3】推荐使用 192.168.1.11/32 这个 32的指定。这个 32 是 CIDR（无类别域间路由） 表示法中的掩码长度。简单来说，它用来界定 IP 地址的范围。/32 的意思就是精确匹配这一个 IP 地址，不允许其他任何 IP。

配置示例	掩码含义	允许访问的 IP 范围	说明
192.168.1.11/32	单个主机	仅 192.168.1.11	最安全，精确指定某一台服务器（推荐用于主从复制配置）。
192.168.1.0/24	C 类子网	192.168.1.1 ~ 192.168.1.254	允许整个局域网网段访问。
192.168.0.0/16	B 类子网	192.168.0.1 ~ 192.168.255.254	允许更大的内网范围访问。
0.0.0.0/0	全网段	任意 IP 地址	风险最高，表示允许互联网上任何 IP 尝试连接。

所以说即使黑客控制了同一网段的其他机器（比如 192.168.1.12），或者你的从库所在的整个机房都被攻破了，只要攻击者的 IP 不是 192.168.1.11，PostgreSQL 就会直接在门口拒绝连接。

3. 创建复制用户并重启服务

登录到主库的 psql 命令行，创建一个专门用于复制的用户。

-- 创建具有复制权限的用户
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'your_secure_password';

配置完成后，重启主库的 PostgreSQL 服务以使配置生效。

# CentOS
sudo systemctl restart postgresql-15
# Ubuntu
sudo systemctl restart postgresql

第二步：配置从库 (192.168.1.11)

1. 停止服务并清空数据目录

首先，确保从库的 PostgreSQL 服务已停止，并清空其默认的数据目录。因为从库的数据将完全从主库复制而来。

# 停止从库服务
sudo systemctl stop postgresql

# 清空从库数据目录 (请根据实际路径调整)
sudo rm -rf /var/lib/pgsql/15/data/*

2. 使用 `pg_basebackup` 从主库拉取数据

这是最关键的一步。使用 pg_basebackup 工具从主库获取一份完整的基础备份，并自动生成必要的复制配置文件。

# 以postgres用户身份在从库执行
sudo -u postgres pg_basebackup \
    -h 192.168.1.10 \
    -D /var/lib/pgsql/15/data \
    -U replicator \
    -P \
    -R \
    -X stream

pg_basebackup 是 PostgreSQL 自带的一个命令行工具。它通过 流复制协议，从正在运行的 PostgreSQL 集群中获取基础备份。
这个命令就像一个“搬运工”，在线地把主库的所有数据文件（表数据、索引、系统表等）原封不动地通过网络传输到从库的硬盘上

命令参数详解：

-h: 主库的 IP 地址。
-D: 从库的数据目录路径。
-U: 用于连接主库的复制用户名。执行时会提示输入密码。
-P: 显示备份进度。
-R: 关键参数。它会自动在从库数据目录中创建 standby.signal 和 postgresql.auto.conf 两个关键文件，
standby.signal：告诉 PostgreSQL，“别把自己当主库，启动时进入‘从库模式’（只读）”。
postgresql.auto.conf：自动写入主库的连接信息（IP、端口、用户、密码），告诉从库“去连这个主库”。
-X stream: 在备份过程中同时传输所需的 WAL 日志，确保备份的一致性。

第三步：启动从库并验证

1. 启动从库服务

数据备份完成后，直接启动从库的 PostgreSQL 服务即可。

# 启动从库服务
sudo systemctl start postgresql

2. 验证复制状态

在主库上验证：
登录主库的 psql，查询 pg_stat_replication 视图。如果能看到从库的连接信息，且 state 为 streaming，则表示复制连接已成功建立。

SELECT client_addr, state, sync_state FROM pg_stat_replication;

示例输出：

 client_addr |   state   | sync_state
-------------+-----------+------------
 192.168.1.11| streaming | async
(1 row)

在从库上验证：
登录从库的 psql，执行以下查询。如果返回 t (true)，则说明当前实例正处于恢复（即从库）模式。

SELECT pg_is_in_recovery();

数据一致性验证：
你可以在主库创建一个测试表并插入数据，然后立即在从库查询，看数据是否同步过来。

主库执行：

CREATE TABLE test_replication (id int, name text);
INSERT INTO test_replication VALUES (1, 'hello pg');

从库执行：
```
SELECT * FROM test_replication;
```
如果查询结果与主库一致，恭喜你，主从复制配置成功！

七.二、主从复制——临危受命：从升主

操作步骤

当确认主库已无法恢复时，你需要在从库上执行以下操作，使其接管服务。

1. 执行提升命令

在从库服务器上， 使用 SQL 函数（推荐）
这是一种更现代、更便捷的方法。登录到从库的 psql 命令行执行：

sudo -u postgres psql -c "SELECT pg_promote();"

如果返回 t (true)，表示提升指令已发送成功。

2. 验证提升是否成功

执行提升命令后，需要验证从库是否已成功转换身份。

检查恢复状态
在从库上执行以下 SQL 查询。如果返回 f (false)，说明它已不再是恢复模式（即不再是只读的从库），而是可以读写的主库了。
```
SELECT pg_is_in_recovery();
```
检查集群状态
在命令行执行 pg_controldata，查看集群状态。
```
sudo -u postgres pg_controldata | grep "Database cluster state"
```
如果输出为 in production，则说明提升成功，数据库已作为主库正常运行。

3. 清理复制配置

提升成功后，建议清理掉旧的复制配置，避免混淆。

编辑新主库（原从库）数据目录下的 postgresql.auto.conf 文件。
找到并注释掉或删除 primary_conninfo 这一行配置。
重新加载配置使其生效：

sudo systemctl reload postgresql

核心原理

SELECT pg_promote(); 这个命令就像是按下了一个 “申请独立的按钮”：

剪断连接（停止接收日志）；
撕掉标签（删除 standby.signal，解除只读）；
另立门户（开启新的时间线，正式成为主库）。

这就是为什么一条简单的 SQL 命令就能完成如此复杂的角色切换。
下面详细讲解这 3步：

🛑 第一步：终止“回放”进程

从库平时处于“待机状态”，它的核心工作是由一个叫 walreceiver 的后台进程负责接收主库发来的日志，并由 startup 进程负责回放这些日志。
当你执行 pg_promote() 时，PostgreSQL 会立即停止这两个进程。

结果：从库不再接收主库的任何新指令，也不再应用任何新日志。它与主库的“脐带”被剪断了。

🚧 第二步：移除“只读”封印

从库之所以是只读的，是因为它在启动时检测到了 standby.signal 文件（或者在旧版本中配置了 recovery.conf）。这个文件告诉数据库：“你现在是备胎，只能读，不能写”。
pg_promote() 的核心作用之一就是自动删除 standby.signal 文件。

结果：一旦这个文件消失，数据库就认为自己不再是“备胎”了，解除了只读限制，允许写入操作。

🚀 第三步：切换“时间线”

这是 PostgreSQL 最精妙的设计之一。为了防止未来发生“脑裂”（即旧主库和新主库同时认为自己是老大），PostgreSQL 引入了时间线（Timeline）的概念。
当从库被提升时，它会创建一条新的时间线（例如从 Timeline 1 变为 Timeline 2）。

结果：
- 新主库（原从库）会在新的时间线上记录日志。
- 如果旧主库修好后想重新加入，它会发现自己还在 Timeline 1，而现在的老大已经在 Timeline 2 了。这迫使旧主库必须“认怂”，只能作为从库去同步 Timeline 2 的数据，从而避免了数据冲突。

八、用户、权限管理

PostgreSQL 和 MySQL 在用户管理上的核心理念差异很大：
MySQL 将“用户”和“权限”绑定在一起，而 PostgreSQL 则将“用户/角色”与“权限”分离。

简单来说：

MySQL：创建一个用户时，必须同时指定他“从哪里来”（Host），权限也是直接赋予这个“用户@主机”组合的。
PostgreSQL：创建的是一个“角色”（Role），它只是一个身份。至于这个身份能干什么，需要通过 GRANT 命令另外赋予；至于这个身份能不能登录，取决于是否赋予 LOGIN 属性。

下面将从增、删、改、查四个维度，通过代码对比来详细讲解。

8.1 创建用户

MySQL

MySQL 的用户名由 用户名 和 主机名 两部分组成，格式为 'user'@'host'。创建时必须指定主机，这决定了用户可以从哪里连接。

-- 1. 创建一个只能从本地连接的用户
CREATE USER 'dev_user'@'localhost' IDENTIFIED BY 'password123';

-- 2. 创建一个可以从任意 IP 连接的用户
CREATE USER 'dev_user'@'%' IDENTIFIED BY 'password123';

-- 3. 创建用户并直接授予所有权限
CREATE USER 'admin_user'@'%' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON mydb.* TO 'admin_user'@'%';

在 MySQL 中，新创建的用户默认只会被赋予一个 USAGE 权限 处于一种“进得来，但寸步难行” 的状态。

PostgreSQL

PostgreSQL 创建用户（本质是带登录权限的角色）时，不关心网络来源。网络访问控制由 pg_hba.conf 文件统一管理。

-- 1. 创建一个基本用户（等同于 CREATE ROLE user WITH LOGIN）
CREATE USER dev_user WITH PASSWORD 'password123';

-- 2. 创建一个具有创建数据库权限的用户
CREATE USER db_creator WITH PASSWORD 'password123' CREATEDB;

-- 3. 创建一个超级用户（拥有所有权限，慎用）
CREATE USER admin_user WITH PASSWORD 'secure_password' SUPERUSER;

核心差异：

MySQL：用户和来源IP强绑定，权限和授权一步到位。
PostgreSQL：用户只是一个身份，权限和网络访问是分离的。

8.2 权限管理

MySQL

权限直接授予 'user'@'host' 组合。

针对开发人员的账号权限
允许修改表结构（加字段、建索引）、建临时表、操作数据，但禁止删库跑路和授权给别人。

-- 1. 创建用户 (限制只能从内网网段或跳板机连接)
CREATE USER 'dev_user'@'192.168.1.%' IDENTIFIED BY 'StrongPassword123!';

-- 2. 授予权限
-- 包含：增删改查 + 建表/删表 + 修改表结构 + 创建索引 + 创建视图/存储过程
GRANT SELECT, INSERT, UPDATE, DELETE, 
       CREATE, DROP, ALTER, INDEX, 
       CREATE VIEW, SHOW VIEW, 
       CREATE ROUTINE, ALTER ROUTINE 
ON myapp_dev.* TO 'dev_user'@'192.168.1.%';

-- 3. 刷新权限
FLUSH PRIVILEGES;

生产环境应用账号权限
遵循“最小权限原则”。只允许读写数据，严禁修改表结构或删除表，防止代码 Bug 导致灾难。

-- 1. 创建用户 (严格限制只能从应用服务器 IP 连接)
CREATE USER 'app_run_user'@'10.0.0.%' IDENTIFIED BY 'VeryStrongComplexPassword!@#';

-- 2. 授予权限
-- 仅包含：增改查 (DML操作)
-- 严禁：CREATE, DROP, ALTER (DDL操作)
GRANT SELECT, INSERT, UPDATE
ON myapp_prod.* TO 'app_run_user'@'10.0.0.%';

-- 3. 刷新权限
FLUSH PRIVILEGES;

管理员权限

-- 授予特定数据库的所有表权限
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'dev_user'@'%';

-- 授予所有数据库的所有权限
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'%';

-- 刷新权限（某些版本需要）
FLUSH PRIVILEGES;

PostgreSQL

权限是分层级的，需要先授予角色对“数据库”或“模式”的权限，再授予对“表”的权限。

PostgreSQL 的权限体系比 MySQL 更严谨，它是分层级的。你不能直接给“整个实例”授权，必须按照 连接数据库 -> 使用模式 -> 操作表 的顺序一层层开权限。

下面是针对你提到的三类账号的详细 SQL 实现代码。假设我们要管理的数据库叫 myapp_db。

1. 应用账号（线上连接用）

目标：只能读写数据，不能修改表结构，不能删表。

-- 1. 创建用户
CREATE USER app_user WITH PASSWORD 'AppSecurePassword123!';

-- 2. 授予连接数据库的权限
GRANT CONNECT ON DATABASE myapp_db TO app_user;

-- 3. 进入该数据库执行后续命令 (\c myapp_db)

-- 4. 授予模式的使用权 (必须给，否则找不到表)
GRANT USAGE ON SCHEMA public TO app_user;

-- 5. 授予现有表的增删改查权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- 6. 授予序列（自增ID）的使用权限 (否则无法插入数据)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- 7. 【关键】设置默认权限
-- 确保未来新创建的表，app_user 也能自动拥有增删改查权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO app_user;

2. 开发人员账号（开发/测试环境用）

目标：可以建表、改字段、删表、查数据。

-- 1. 创建用户
CREATE USER dev_user WITH PASSWORD 'DevPassword123!';

-- 2. 授予连接权限
GRANT CONNECT ON DATABASE myapp_db TO dev_user;

-- 3. 进入该数据库执行后续命令 (\c myapp_db)

-- 4. 授予模式的使用权和创建权 (允许建表)
GRANT USAGE, CREATE ON SCHEMA public TO dev_user;

-- 5. 授予现有表的所有权限 (包括删表 ALTER/DROP)
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dev_user;

-- 6. 授予序列的所有权限
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO dev_user;

-- 7. 【关键】设置默认权限
-- 确保未来新创建的表，dev_user 也能自动拥有所有权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO dev_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO dev_user;

3. 超级管理员账号（DBA/运维用）

目标：拥有最高权限，可以管理用户、修改配置、操作所有数据。

-- 1. 创建用户
CREATE USER admin_user WITH PASSWORD 'AdminSecurePassword123!';

-- 2. 授予超级用户权限
-- 这将绕过所有权限检查，等同于 root
ALTER USER admin_user WITH SUPERUSER;

-- 3. 或者，如果不给 SUPERUSER，可以给所有预定义角色权限 (PG14+)
-- GRANT pg_read_all_data, pg_write_all_data, pg_monitor TO admin_user;

PostgreSQL 权限管理的 3 个核心知识点

必须要给 USAGE ON SCHEMA
在 MySQL 里，只要给了表权限就能访问。但在 PostgreSQL 里，模式 就像是一个 文件夹 。
如果用户没有 USAGE 权限，即使他拥有表的 SELECT 权限，查询时也会报错 permission denied for schema public。
ALTER DEFAULT PRIVILEGES 非常重要
PostgreSQL 的权限不会自动继承给未来创建的对象。
- 如果你不执行第 7 步，开发人员今天新建了一张表 user_logs，应用账号明天就会因为无法访问这张新表而报错。
- 注意：默认权限只对执行该命令之后创建的表有效。
序列权限容易被忽略
如果你的表里有自增主键（SERIAL 或 BIGSERIAL），PostgreSQL 底层是使用序列来实现的。如果只给表权限而不给序列的 USAGE 权限，执行 INSERT 时会报错：

permission denied for sequence table_id_seq

PostgreSql 的授权解读

MySQL：权限模型相对扁平，通常是 数据库.*。
PostgreSQL：权限模型更精细，分为 数据库 -> 模式 -> 表 -> 列 等多个层级，控制力更强。

1. 模式（Schema ）

PostgreSQL 的层级结构是这样的：

集群
数据库
模式
数据表/视图/函数等

你可以把这个结构想象成一个公司的组织架构：

集群 = 整个集团公司
数据库 = 不同的分公司
模式 = 分公司下的不同部门
数据表 = 部门里的具体员工

关键点：
“部门”（模式）下面不能开“子部门”（子模式），部门下面直接就是“员工”（数据表）。

不同模式下的数据表可以连表查询吗？可以在一个事务中操作吗？答案：完全可以，推荐使用使用 全限定名（格式为：schema_name.table_name）。

作用：

逻辑分组：可以将相关的表组织在一起。例如，一个电商系统可以有 core（核心业务）、user（用户管理）、finance（财务）等多个 Schema，让结构更清晰。
避免命名冲突：不同的 Schema 下可以有同名的表。例如，schema_a.users 和 schema_b.users 可以同时存在，互不干扰。
权限隔离：可以方便地对整个 Schema 进行授权。例如，让报表团队只能访问 reporting Schema 下的所有表，而无法触碰 core Schema 的数据。

2. public模式

public 是 PostgreSQL 中一个特殊的、默认存在的 Schema，相当于是 土著。

默认位置：当你创建一个新数据库时，它会自动包含一个名为 public 的 Schema。
默认行为：如果你在执行 CREATE TABLE 等命令时没有指定 Schema，PostgreSQL 会默认将对象创建在 public Schema 下。
- 例如，CREATE TABLE users (...); 等价于 CREATE TABLE public.users (...);。

3. 模式授权命令解读

说明：在 PostgreSQL 中，SQL 关键字（如 GRANT, ON, SCHEMA, TO）和对象名称（如 public, dev_user）默认都是不区分大小写的，这点和 MySQL 一致。

grant usage, create on schema public to dev_user;

这条命令赋予了 dev_user 用户对 public 这个 “文件夹” 的两种关键权限：

USAGE 权限
- 作用：允许用户“进入”这个 Schema 并查找其中的对象。
CREATE 权限
- 作用：允许用户在这个 Schema 中创建新的对象，如表、视图、函数等。

4. 发号机（sequence：n.序列 v. 排序）

你可以把 SEQUENCE 想象成一个发号机：

每次程序需要插入新数据时，就会向这个发号机请求：“给我下一个号码”。
发号机（SEQUENCE）就会吐出一个数字（比如 101），并且内部记录“下一个号码是 102”。
程序拿到 101，插入到数据库表中。

为什么要专门给 SEQUENCE 授权？
很多从 MySQL 转过来的开发者会不习惯，因为在 MySQL 中，自增是表的一个属性，你有了表的 INSERT 权限，自然就能自增。

但在 PostgreSQL 中，SEQUENCE 是一个 独立的 对象，它和表是“邻居”关系，而不是“父子”关系。

你拥有了表的 INSERT 权限，只代表你能往表里写数据。
但如果你没有SEQUENCE 的 USAGE 权限，你就拿不到自增 ID，也就实际无法完成写入。

5. 发号机授权命令解读

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

1. ON ALL SEQUENCES
这不仅仅是针对一张表，而是针对当前 public 模式下所有的“发号机”。

如果你的数据库里有 50 张表，每张表都有一个自增 ID，那么后台就有 50 个 SEQUENCE。
这条命令一次性把这 50 个发号机的权限都给了 app_user，省去了你写 50 次授权的麻烦。

2. USAGE 权限
这是对 SEQUENCE 最核心的权限。

含义：允许用户“使用”这个发号机来获取下一个值。
对应函数：nextval()。
实际场景：当你的代码执行 INSERT INTO users (name) VALUES ('Tom'); 时，数据库底层会自动执行 nextval('users_id_seq') 来获取 ID。
后果：如果没有 USAGE 权限，你的程序无法插入新数据，会直接报错 permission denied for sequence ...。

3. SELECT权限
这是对 SEQUENCE 的查看权限。

含义：允许用户查看当前发号机发到了多少号，或者发号机的配置信息。
对应函数：currval()（查看当前会话获取的值）或直接查询序列表 SELECT * FROM users_id_seq。
实际场景：
- 当你插入数据后，想立刻知道刚才生成的 ID 是多少（例如为了返回给前端），通常会用 RETURNING id，这底层依赖 currval。
- 排查问题时，想看看 ID 是不是快要用完了（比如到了 21 亿）。
后果：如果没有 SELECT 权限，某些 ORM 框架（如 Hibernate, GORM）在插入数据后尝试获取生成的 ID 时会报错。

6. 更改时空符印（`默认权限`）规则

在 PostgreSQL 中，权限分为 “现有对象” 和 “未来对象”。
即 “今天给的权限，管不了明天新建的表。”

场景还原
假设你现在只执行了普通的 GRANT 命令：

-- 1. 给 dev_user 授权现有表
GRANT ALL ON ALL TABLES IN SCHEMA public TO dev_user;

此时发生了什么？

dev_user 可以操作此时此刻数据库里存在的所有表（比如 users, orders）。

然后，过了一个小时…

你在数据库里新建了一张表 logs。当然你对这张表拥有绝对的控制权。

此时发生了什么？

dev_user 无法访问 logs 表！
原因：PostgreSQL 认为新表是“新出生”的婴儿，它只继承拥有者的权限，或者系统默认的权限，而不会自动继承你之前给其他用户的授权。

解决办法： 更改时空符印的规则

# 数据表
alter default privileges in schema public grant all privileges on tables to dev_user;

# 发号机
alter default privileges in schema public grant all privileges on SEQUENCES to dev_user;

alter default privileges
- 这是“修改默认权限”的指令。它不操作具体的数据表，而是操作权限配置规则。
in schema public
- 作用域：这个规矩只在 public 这个文件夹（模式）里生效。
- 注意：如果你在 别的 模式下建表，这个规矩是不管用的。
grant all privileges
- 赋予的权限：包括 SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER 等。
- 注意：对于开发人员，通常意味着他对自己建的表有完全控制权，对别人建的表也有完全控制权（因为规则是全局的）。
on tables
- 适用对象：仅针对表（包括视图）。
- 为什么需要第二句？：因为序列不是表。序列是独立对象。如果你只写了这一句，开发人员建新表没问题，但如果表里有自增 ID，他插入数据时依然会报错（因为没有序列的权限）。
to dev_user
- 受益人：这个规矩是专门为 dev_user 设立的。

7. 谁来更改时空符印规则

这是 alter default privileges 最隐蔽的逻辑：默认权限只针对“当前执行命令的用户” 未来创建的对象。

场景 A：DBA 执行了这两句命令

如果你是超级管理员（DBA）登录，执行了这两句命令：

结果：以后 DBA 创建 的表，dev_user 自动有权限。
漏洞：如果 dev_user 自己登录去创建表，或者 app_user 创建表，这个规则不生效！

场景 B：dev_user 自己执行这两句命令

如果你让 dev_user 自己登录，执行这两句命令：

结果：以后 dev_user 自己创建 的表，他自动拥有所有权限（虽然他自己本来就是拥有者，但这确保了权限的完备性），或者更准确地说，这是为了确保他创建的表能自动授权给其他用户（如果命令里写的是 TO app_user）。

场景 C：正确的生产环境用法

通常，我们希望无论谁（无论是 DBA、dev_user 还是部署脚本）创建了表，app_user（线上账号）都能自动访问。

你需要为每一个可能创建表的角色设置默认权限。

示例：
假设你的表通常是由 dev_user 创建的，但线上是由 app_user 连接的。
你需要以 dev_user 的身份（或者针对 dev_user 角色）执行：

-- 意思是：以后凡是 dev_user 建的表，自动给 app_user 只读权限
ALTER DEFAULT PRIVILEGES FOR ROLE dev_user IN SCHEMA public 
GRANT SELECT ON TABLES TO app_user;

8. 全部权限有哪些？

针对数据表 tables

all privileges on tables

权限名称	通俗解释	典型场景
select	读	执行 `select * from table` 查询数据。
insert	增	执行 `insert into table` 插入新数据。
update	改	执行 `update table set ...` 修改现有数据。
delete	删	执行 `delete from table` 删除特定行。
truncate	清空	执行 `truncate table` 瞬间清空全表数据（比 delete 快，且不记录单行删除日志）。
references	引用	在创建外键时，引用这张表作为父表。
trigger	触发器	在这张表上创建触发器（trigger）。

针对发号机 sequences

all privileges on sequences

权限名称	对应的函数	通俗解释	为什么需要它？
usage	`nextval()`	使用权	最核心权限。没有它，用户无法获取下一个序列号，插入数据时会报错“permission denied”。
select	`currval()`, `lastval()`	查看权	允许用户查看当前会话刚刚获取的值。通常用于插入后确认 ID。
update	`setval()`	修改权	允许用户强制重置序列的当前值（比如从 100 重置回 1）。

8.3 修改用户

MySQL

主要用于修改密码或账户属性（如锁定）。

-- 1. 修改用户密码
ALTER USER 'dev_user'@'%' IDENTIFIED BY 'new_password';

-- 2. 锁定/解锁账户
ALTER USER 'dev_user'@'%' ACCOUNT LOCK; -- 登录环节：直接拦截
ALTER USER 'dev_user'@'%' ACCOUNT UNLOCK;

PostgreSQL

可以修改角色的各种属性，非常灵活。

-- 1. 修改密码
ALTER USER dev_user PASSWORD 'new_password';

-- 2. 赋予或撤销超级用户权限
ALTER USER dev_user SUPERUSER;
ALTER USER dev_user NOSUPERUSER;

-- 3. 赋予创建数据库的权限
ALTER USER dev_user CREATEDB;

-- 4. 设置连接有效期
ALTER USER dev_user VALID UNTIL '2026-12-31';

撤销权限：
revoke 在 PostgreSQL 中就是权限回收的意思。你可以把它看作是 grant 的·反向操作，专门用来给账号“做减法”。

🛠️ 具体用法与举例
为了方便理解，我们继续沿用之前的两个角色：

app_owner：管理员/表拥有者
dev_user：普通用户

假设 dev_user 原本有增、删、改、查权限，

场景：收回 dev_user 对 public 模式下所有表的修改和删除权限。

revoke update, delete on all tables in schema public from dev_user;

结果：dev_user 依然可以 select 和 insert，但不能再更新或删除数据了。

8.4 删除用户

MySQL

删除用户会同时清除其所有权限记录。

-- 删除指定用户
DROP USER 'dev_user'@'%';

PostgreSQL

删除角色时，如果该角色拥有任何数据库对象（如表），会失败。必须先处理掉这些对象。

动作一：删除角色（会失败）
当你执行 drop role tom; 时，PostgreSQL 会检查：“Tom 名下还有没有东西？”

如果 Tom 拥有表 abc，系统会报错：“无法删除角色，因为有对象依赖它”。
结果：Tom 还在，表 abc 还在，什么都没发生。

动作二：删除角色的资产（会成功）
当你执行 drop owned by tom; 时，PostgreSQL 会执行：“把 Tom 名下的所有东西都销毁”。

系统会找到表 abc（因为 Tom 是 Owner），然后直接执行 drop table abc。
结果：表 abc 被彻底删除了，但 Tom 这个账号还在（只是变穷了，名下没东西了）。

标准做法：

-- a. 将用户拥有的对象转给其他用户（如 postgres）
reassign owned by tom to postgres; -- 把 Tom 名下的表都过户给管理员，确保表还在。

-- b. 撤销用户拥有的任何待处理权限
drop owned by tom; -- 这时候 Tom 名下已经没有表了（都过户走了），这个命令只会清理一些零碎的权限，非常安全。

-- c. 再删除用户
drop role tom;

在 PostgreSQL 中，drop owned 命令的逻辑是：“删除这个角色拥有的所有东西”。

如果 tom 是表 abc 的创建者（Owner），那么这张表就是 tom 的“私有财产”。
当你执行 drop owned by tom 时，数据库会找到所有属于 tom 的表，并把它们全部删除。
表 abc 属于 postgres，只是给了 Tom 操作权限**
- 执行 drop owned by tom：表 abc 不会被删除。
- 原因：表不是 Tom 的，他只是个“租客”。命令只会收回 Tom 的钥匙（权限），不会拆房东的房子。

8.5 查询用户信息

MySQL

用户信息存储在 mysql.user 系统表中。

-- 查看所有用户及其主机
SELECT user, host FROM mysql.user;

-- 查看特定用户的权限
SHOW GRANTS FOR 'dev_user'@'%';

PostgreSQL

用户/角色信息存储在 pg_roles 系统视图中。

-- 查看所有角色及其属性
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin FROM pg_roles;

-- 查看特定角色被授予了哪些权限（需要查询多个系统表，较复杂）
-- 查看表级权限
SELECT grantee, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'dev_user';

核心差异：

MySQL：查询权限非常直观，SHOW GRANTS 命令简单好用。
PostgreSQL：查询权限相对复杂，需要查询 information_schema 或 pg_catalog 下的多个系统表。

8.6 用户组

PostgreSQL **“用户组”**的概念不仅存在，而且是权限管理中最核心的机制。

你可以这样理解：在 PG 里，“组”本质上就是一个“不能登录”的角色。

为了帮你理清这个概念，我整理了一个对比表：

👥 角色与用户组的对比

特性	用户	组
本质	一个拥有 `LOGIN` 权限的角色	一个没有 `LOGIN` 权限的角色
用途	代表具体的人或应用（如 `dev_user`）	代表职位或权限集合（如 `dev_group`）
登录能力	✅ 可以登录数据库	❌ 不能登录数据库
典型操作	执行 SQL，查询数据	被赋予权限，然后被“挂载”给用户

🛠️ 为什么需要“组”？（最佳实践）

如果没有组，管理权限会非常痛苦。

没有组的场景：公司有 50 个开发，每个人都要单独执行 GRANT SELECT...。如果有新员工入职，你要执行 50 次授权；如果有人离职，你要执行 50 次回收。
有组的场景：
1. 创建一个组 dev_group。
2. 把权限一次性给 dev_group。
3. 把 50 个用户加入这个组。
4. 结果：所有人自动拥有权限。新人入职只需把他拉进群，离职只需踢出群。

🎬 实战演练：如何创建和使用“组”

第一步：创建一个“组”

创建一个名为 dev_group 的角色，并明确指定它不能登录（nologin）。

-- 创建一个组角色
create role dev_group nologin;

第二步：给“组”授权

把之前讨论的那些权限（表权限、序列权限）全部给这个组，而不是给具体的人。

-- 给组授权（全家桶）
grant usage on schema public to dev_group;
grant all privileges on all tables in schema public to dev_group;
grant all privileges on all sequences in schema public to dev_group;

第三步：把“用户”加入“组”

假设你有一个用户 dev_user，让他继承组的权限。

-- 把 dev_user 加入 dev_group
-- 语法是：把 组 给 用户
grant dev_group to dev_user;

效果：dev_user 现在不仅拥有他自己的权限，还自动拥有了 dev_group 里的所有权限。

第四步：如何把人移出“组”（收回权限）

如果 dev_user 转岗了，不再需要开发权限。

-- 把 dev_user 从 dev_group 踢出
revoke dev_group from dev_user;

这样管理权限，既清晰又高效。

九、锁

MySQL (InnoDB) 依赖“间隙锁”来保证数据一致性，而 PostgreSQL 则完全依靠 MVCC（多版本并发控制）来实现。
这直接导致了它们在 并发性能 和 死锁概率 上的不同表现。

相同点：基础机制一致

尽管底层实现不同，但两者在宏观概念上有很多共通之处：

锁的粒度：两者都支持 表级锁 和 行级锁。
- 表锁：开销小，但并发度低（适合批量操作）。
- 行锁：开销大，但并发度高（适合高并发写入）。
锁的类型：都区分 共享锁 和 排他锁。
- 共享锁：通常用于读操作，允许多人同时读。
- 排他锁：通常用于写操作，同一时间只允许一个人修改。
MVCC 支持：两者都使用 MVCC 来实现事务隔离，让“读不阻塞写，写不阻塞读”成为可能。

不同点：核心差异对决

这是你最需要关注的部分，决定了你在高并发场景下的选型和调优策略。

1. 间隙锁：最大的区别

这是两者最本质的差异，直接影响了死锁的概率。

MySQL (InnoDB)：有间隙锁
- 机制：在“可重复读”隔离级别下，MySQL 不仅锁住扫描到的行，还会锁住行与行之间的“间隙”。
- 目的：为了解决“幻读”问题。
- 副作用：锁的范围容易扩大。例如查询 id=10，如果 id=10 不存在，它可能会锁住整个范围，导致其他事务无法插入数据。这大大增加了死锁的概率。
PostgreSQL：无间隙锁
- 机制：PostgreSQL 不锁间隙，只锁具体的行。
- 实现：它完全依靠 MVCC 的快照机制来防止幻读。
- 优势：锁粒度非常精准，不会误伤无辜的插入操作，因此死锁概率比 MySQL 低得多。

2. MVCC 的实现方式：存储与清理

虽然都叫 MVCC，但“存旧数据”的方式完全不同。

MySQL：存 Undo Log
- 旧版本数据保存在独立的 Undo Log 区域。
- 事务提交后，旧数据由后台线程慢慢清理。
PostgreSQL：存数据页
- 旧版本数据直接留在表文件里（这就是为什么 PG 的表容易膨胀）。
- 必须依赖 Vacuum 进程定期清理死元组。

3. 锁的冲突与等待

MySQL：
- 读写冲突较明显。虽然 MVCC 解决了部分问题，但在某些场景下（如 SELECT ... FOR UPDATE），锁竞争依然激烈。
- 如果查询没有走索引，行锁会直接退化为表锁，导致性能雪崩。
PostgreSQL：
- 读写几乎不冲突。读操作不加锁，写操作也不阻塞读，完全靠版本判断。
- 支持更高级的锁语法，例如 SKIP LOCKED（跳过被锁住的行），非常适合秒杀、队列等场景。

📊 核心对比总结表

特性	MySQL	PostgreSQL
防止幻读	依赖 Next-Key Lock	依赖 MVCC 快照
间隙锁	有	无
死锁概率	较高（间隙锁容易误伤）	较低（锁粒度精准）
无索引查询	退化为表锁	依然保持行锁
锁管理	依赖索引实现行锁	依赖系统表管理锁
适用场景	简单高并发 CRUD	复杂查询、高一致性要求

十、 MySQL 和 Pgsql 在数据 CURD 的区别

一、前置准备：统一测试环境与表结构

为了让对比更直观，我们先搭建统一的测试环境，创建相同逻辑的表（用户表 user_info），后续所有增删查改操作均基于此表展开。需要注意的是，两者在表结构定义上已有细微差异，这也是后续操作差异的伏笔。

1.1 MySQL 表结构创建

-- MySQL 版本：8.0+（主流稳定版）
CREATE TABLE IF NOT EXISTS user_info (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 自增主键（MySQL 特有语法）
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    age TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄（非负）',
    gender CHAR(1) DEFAULT '未知' COMMENT '性别：男/女/未知',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';

1.2 Pgsql 表结构创建

-- Pgsql 版本：14+（主流稳定版）
CREATE TABLE IF NOT EXISTS user_info (
    id SERIAL PRIMARY KEY,  -- 自增主键（Pgsql 特有语法，等价于 MySQL 的 AUTO_INCREMENT）
    -- 或 Pgsql 10+ 推荐写法：id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    age SMALLINT CHECK (age >= 0) DEFAULT 0 COMMENT '年龄（非负，通过 CHECK 约束实现）',
    gender CHAR(1) DEFAULT '未知' COMMENT '性别：男/女/未知',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) COMMENT='用户信息表';

二、核心操作对比：增删查改（CRUD）

CRUD 是数据库操作的基础，也是日常开发中使用频率最高的操作。两者在基础语法上高度一致，但在特殊场景（如批量插入、分页查询、条件筛选）上存在明显差异，以下逐一详解。

2.1 新增操作（Create）：INSERT

新增操作的核心是向表中插入数据，支持单行插入、多行插入，两者基础语法一致，但在自增主键处理、批量插入优化、特殊数据类型插入上有差异。

2.1.1 单行插入（基础场景）

两者语法完全一致，无需额外调整，示例如下：

-- MySQL 和 Pgsql 通用（单行插入，指定字段）
INSERT INTO user_info (username, age, gender)
VALUES ('张三', 25, '男');

-- 插入后返回自增主键（常用场景，差异明显）
-- MySQL：使用 LAST_INSERT_ID()
INSERT INTO user_info (username, age, gender)
VALUES ('李四', 23, '女');
SELECT LAST_INSERT_ID() AS new_id;  -- 返回刚插入的 id

-- Pgsql：使用 RETURNING 关键字（更灵活，可返回多个字段）
INSERT INTO user_info (username, age, gender)
VALUES ('王五', 27, '男')
RETURNING id AS new_id, username;  -- 可同时返回 id 和用户名

2.1.2 多行插入（批量场景）

基础多行插入语法一致，但 Pgsql 支持更灵活的批量插入方式，且在大数据量批量插入时性能更优。

-- 方式1：标准多行插入（MySQL 和 Pgsql 通用）
INSERT INTO user_info (username, age, gender)
VALUES 
('赵六', 22, '女'),
('孙七', 24, '男'),
('周八', 26, '女');

-- 方式2：Pgsql 特有（从子查询插入，适合批量导入数据）
INSERT INTO user_info (username, age, gender)
SELECT name, age, gender FROM temp_user  -- temp_user 是临时表，存储待导入数据
WHERE age > 20;

-- 方式3：MySQL 特有（LOAD DATA INFILE，适合大批量数据导入，比 INSERT 高效）
LOAD DATA INFILE '/data/user_data.csv'
INTO TABLE user_info
FIELDS TERMINATED BY ','  -- 按逗号分隔字段
LINES TERMINATED BY '\n'  -- 按换行分隔行
IGNORE 1 ROWS;  -- 忽略 CSV 文件的表头行

2.1.3 特殊场景：插入冲突处理（重要）

当插入的数据违反唯一约束（如 username 唯一）时，两者都支持“冲突处理”，但语法差异较大，这是开发中极易踩坑的点。

-- 先给 username 加唯一约束（两者通用）
ALTER TABLE user_info ADD UNIQUE KEY uk_username (username);

-- 场景：插入 username 已存在的数据，处理冲突（更新原有数据）
-- MySQL：使用 ON DUPLICATE KEY UPDATE
INSERT INTO user_info (username, age, gender)
VALUES ('张三', 26, '男')  -- 张三已存在，触发冲突
ON DUPLICATE KEY UPDATE age = 26, gender = '男';  -- 更新年龄和性别

-- Pgsql：使用 ON CONFLICT ... DO UPDATE（需指定冲突的唯一约束）
INSERT INTO user_info (username, age, gender)
VALUES ('张三', 26, '男')
ON CONFLICT (username) DO UPDATE  -- 指定冲突的字段（需是唯一约束字段）
SET age = 26, gender = '男';

-- 补充：冲突时忽略插入（两者都支持，语法一致）
INSERT IGNORE INTO user_info (username, age, gender)
VALUES ('张三', 26, '男');  -- 已存在则忽略，不报错

2.2 查询操作（Retrieve）：SELECT

查询是 CRUD 中最复杂、使用最频繁的操作，两者在基础查询上一致，但在分页查询、模糊查询、函数使用、复杂查询优化上差异显著。

2.2.1 基础查询（无差异）

-- 查询所有字段（不推荐生产环境，字段过多影响性能）
SELECT * FROM user_info;

-- 查询指定字段、条件筛选、排序
SELECT id, username, age FROM user_info
WHERE age > 23 AND gender = '男'
ORDER BY age DESC;

-- 聚合查询（统计、分组）
SELECT gender, COUNT(*) AS user_count, AVG(age) AS avg_age
FROM user_info
GROUP BY gender
HAVING COUNT(*) > 1;

2.2.2 分页查询（高频场景，差异明显）

分页查询是 web 开发的必备场景，两者语法差异较大，极易混淆。

-- 场景：查询第2页数据，每页显示2条（跳过前2条，取2条）
-- MySQL：使用 LIMIT 偏移量, 条数（偏移量 = 页码-1 * 每页条数）
SELECT id, username, age FROM user_info
ORDER BY id DESC
LIMIT 2, 2;  -- 偏移量2，取2条（第2页，每页2条）

-- Pgsql：使用 LIMIT 条数 OFFSET 偏移量（更直观，不易出错）
SELECT id, username, age FROM user_info
ORDER BY id DESC
LIMIT 2 OFFSET 2;  -- 取2条，跳过前2条（与上面效果一致）

-- 补充：Pgsql 13+ 支持 FETCH NEXT 语法（更符合 SQL 标准），效果上一样
SELECT id, username, age FROM user_info
ORDER BY id DESC
OFFSET 2 FETCH NEXT 2 ROWS ONLY;

2.2.3 模糊查询（LIKE 与正则）

两者都支持 LIKE 模糊查询，但 Pgsql 对正则表达式的支持更完善，且区分大小写的规则不同。

-- 基础模糊查询（两者通用，% 匹配任意字符，_ 匹配单个字符）
SELECT * FROM user_info WHERE username LIKE '张%';  -- 匹配姓张的用户

-- 区分大小写差异
-- MySQL：默认不区分大小写（取决于字符集排序规则，utf8mb4_general_ci 不区分）
SELECT * FROM user_info WHERE username LIKE 'zhang%';  -- 会匹配 '张三'（不区分大小写）
-- Pgsql：默认区分大小写
SELECT * FROM user_info WHERE username LIKE 'zhang%';  -- 不会匹配 '张三'，需用 ILIKE 不区分大小写
SELECT * FROM user_info WHERE username ILIKE 'zhang%';  -- 等价于 MySQL 的 LIKE（不区分大小写）

-- 正则查询（差异较大）
-- MySQL：使用 REGEXP 或 RLIKE
SELECT * FROM user_info WHERE username REGEXP '^张[三五四]';  -- 匹配 张三、张五、张四
-- Pgsql：使用 ~ （区分大小写）、~*（不区分大小写）
SELECT * FROM user_info WHERE username ~ '^张[三五四]';  -- 区分大小写
SELECT * FROM user_info WHERE username ~* '^张[三五四]';  -- 不区分大小写

2.2.4 函数使用差异（常用函数）

两者都支持常用的 SQL 函数，但部分函数名称、用法不同，以下列举高频函数差异：

-- 1. 字符串拼接（最常用）
-- MySQL：使用 CONCAT 函数，或 ||（需开启 sql_mode=PIPES_AS_CONCAT）
SELECT CONCAT(username, '-', gender) AS user_gender FROM user_info;
-- Pgsql：支持 || 拼接（默认开启），也支持 CONCAT 函数
SELECT username || '-' || gender AS user_gender FROM user_info;

-- 2. 日期函数（获取当前日期时间）
-- MySQL：CURDATE()（日期）、CURTIME()（时间）、NOW()（日期+时间）
SELECT CURDATE(), CURTIME(), NOW();
-- Pgsql：CURRENT_DATE（日期）、CURRENT_TIME（时间）、NOW()（日期+时间）
SELECT CURRENT_DATE, CURRENT_TIME, NOW();

-- 3. 空值处理（IFNULL vs COALESCE）
-- MySQL：IFNULL(字段, 默认值)（仅支持两个参数）
SELECT IFNULL(age, 0) AS age FROM user_info;  -- age 为空则返回 0
-- Pgsql：COALESCE(字段, 默认值)（支持多个参数，更灵活）
SELECT COALESCE(age, 0) AS age FROM user_info;  -- 与上面效果一致
SELECT COALESCE(age, gender, '未知') AS info FROM user_info;  -- 依次判断，取第一个非空值

2.3 修改操作（Update）：UPDATE

修改操作的核心是更新表中已有数据，两者基础语法一致，但在事务隔离、批量更新、自增字段更新上有差异。

2.3.1 基础修改（无差异）

-- 更新单个字段
UPDATE user_info SET age = 28 WHERE id = 1;

-- 更新多个字段
UPDATE user_info SET age = 29, gender = '男' WHERE username = '李四';

2.3.2 批量更新（特殊场景）

-- 场景：根据不同条件批量更新不同数据
-- MySQL：使用 CASE WHEN（通用）
UPDATE user_info
SET age = CASE 
    WHEN username = '张三' THEN 27
    WHEN username = '李四' THEN 24
    ELSE age  -- 其他用户不修改
END
WHERE username IN ('张三', '李四');

-- Pgsql：支持 CASE WHEN（通用），也支持更灵活的 UPDATE ... FROM 语法
UPDATE user_info u
SET age = t.new_age
FROM (
    SELECT '张三' AS username, 27 AS new_age
    UNION ALL
    SELECT '李四' AS username, 24 AS new_age
) t
WHERE u.username = t.username;

2.3.3 自增字段更新（差异显著）

自增字段的更新的限制不同，这也是开发中容易踩坑的点。

-- MySQL：支持直接更新自增主键（需谨慎，可能导致主键冲突）
UPDATE user_info SET id = 10 WHERE id = 1;  -- 允许执行（前提是 id=10 不存在）

-- Pgsql：默认不允许更新 SERIAL/IDENTITY 自增字段（会报错）
UPDATE user_info SET id = 10 WHERE id = 1;  -- 报错：cannot update column "id" of relation "user_info"
-- 若需更新，需先修改表结构（不推荐，破坏自增逻辑）
ALTER TABLE user_info ALTER COLUMN id DROP IDENTITY;
UPDATE user_info SET id = 10 WHERE id = 1;
ALTER TABLE user_info ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY PRIMARY KEY;

2.3.4 事务隔离下的更新差异

MySQL（InnoDB 引擎）和 Pgsql 都支持 ACID 事务，但默认事务隔离级别不同，导致更新操作的锁机制有差异：

MySQL 默认隔离级别：可重复读（Repeatable Read），更新时会加行锁，若存在间隙锁（防止幻读），可能导致锁冲突；
Pgsql 默认隔离级别：读已提交（Read Committed），更新时仅加行锁，无间隙锁，锁冲突概率更低，更适合高并发更新场景。

-- 事务示例（两者通用，但锁机制不同）
BEGIN;  -- 开启事务
UPDATE user_info SET age = 30 WHERE id = 1;  -- 加行锁
-- 其他事务更新 id=1 的数据会阻塞，直到当前事务提交/回滚
COMMIT;  -- 提交事务，释放锁

2.4 删除操作（Delete）：DELETE

删除操作的核心是删除表中不需要的数据，两者基础语法一致，但在批量删除、级联删除、删除后自增字段重置上有差异。

2.4.1 基础删除（无差异）

-- 删除单个数据
DELETE FROM user_info WHERE id = 1;

-- 删除符合条件的批量数据
DELETE FROM user_info WHERE age < 20;

-- 删除所有数据（保留表结构，慎用）
DELETE FROM user_info;

2.4.2 级联删除（关联表场景）

当存在表关联（如用户表 user_info 和订单表 order_info，order_info 有外键关联 user_info.id）时，删除用户数据需处理关联数据，两者语法差异较大。

-- 先创建关联表（订单表）
-- MySQL：创建外键时指定 ON DELETE CASCADE（级联删除）
CREATE TABLE IF NOT EXISTS order_info (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_no VARCHAR(30) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user_info(id) ON DELETE CASCADE
);

-- Pgsql：创建外键时指定 ON DELETE CASCADE（级联删除），语法一致
CREATE TABLE IF NOT EXISTS order_info (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    order_no VARCHAR(30) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user_info(id) ON DELETE CASCADE
);

-- 级联删除测试（两者通用）
DELETE FROM user_info WHERE id = 2;  -- 会自动删除 order_info 中 user_id=2 的所有订单

-- 差异点：MySQL 支持外键自动命名，Pgsql 需显式命名（推荐）
-- Pgsql 推荐写法（显式命名外键，便于后续删除/修改外键）
CREATE TABLE IF NOT EXISTS order_info (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    order_no VARCHAR(30) NOT NULL,
    CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES user_info(id) ON DELETE CASCADE
);

2.4.3 删除后自增字段重置（重要差异）

删除数据后，自增字段的起始值是否重置，两者差异显著，直接影响后续插入数据的主键取值。

-- 场景：删除所有数据后，插入新数据，观察自增主键的起始值
DELETE FROM user_info;

-- MySQL：自增主键不会重置，继续从删除前的最大值+1开始
INSERT INTO user_info (username, age, gender) VALUES ('吴九', 28, '男');
SELECT id FROM user_info;  -- id 为删除前的最大值+1（如之前最大id=5，新id=6）
-- MySQL 重置自增主键的方法（需谨慎）
ALTER TABLE user_info AUTO_INCREMENT = 1;

-- Pgsql：SERIAL/IDENTITY 自增字段也不会自动重置，需手动重置
INSERT INTO user_info (username, age, gender) VALUES ('吴九', 28, '男');
SELECT id FROM user_info;  -- id 继续从删除前的最大值+1开始
-- Pgsql 重置自增主键的方法（两种）
-- 方法1：TRUNCATE（删除所有数据并重置自增，慎用，不可恢复）
TRUNCATE TABLE user_info;
-- 方法2：修改序列（适用于 SERIAL 类型，IDENTITY 类型需先删除再重建）
ALTER SEQUENCE user_info_id_seq RESTART WITH 1;

2.4.4 批量删除性能差异

当删除大量数据（如10万+条）时，两者性能差异明显：

MySQL：DELETE 会逐行删除，记录日志（binlog），速度较慢，适合小批量删除；大批量删除推荐用 TRUNCATE（无日志，速度快，但不可恢复）；
Pgsql：DELETE 同样逐行删除，但支持批量删除优化（如配合 LIMIT 分批删除），且 TRUNCATE 速度更快，同时支持 TRUNCATE ... CASCADE（级联清空关联表）。

-- Pgsql 分批删除（避免一次性删除大量数据导致锁表）
WHILE EXISTS (SELECT 1 FROM user_info WHERE age < 20) LOOP
    DELETE FROM user_info WHERE age < 20 LIMIT 1000;  -- 每次删除1000条
END LOOP;

三、拓展内容：实操必备知识点

除了核心的 CRUD 差异，以下拓展知识点的在实际开发中高频使用，能帮助我们更好地选型和避坑。

3.1 数据类型拓展差异

两者都支持基础数据类型（int、varchar、timestamp 等），但 Pgsql 支持更多高级数据类型，灵活性更强，适合复杂业务场景。

Pgsql 特有数据类型：
- JSON/JSONB：支持 JSON 数据的存储和查询，JSONB 支持索引，查询效率更高；
- ARRAY：支持数组类型，如 INT\[\]（整数数组）、VARCHAR\[\]（字符串数组）；
- GIS 地理类型：支持经纬度、地理位置查询（需安装 PostGIS 插件）；
- RANGE 类型：如 INT4RANGE（整数范围）、TSRANGE（时间范围），适合区间查询场景。
MySQL 特有优化：
- ENUM 类型：适合固定选项的场景（如性别、状态），比 Pgsql 的 ENUM 更简洁；
- SET 类型：支持多值选择（如兴趣爱好），Pgsql 需用数组或关联表实现。

-- Pgsql 数组类型示例
CREATE TABLE IF NOT EXISTS user_hobby (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    hobby VARCHAR[]  -- 字符串数组
);
INSERT INTO user_hobby (user_id, hobby) VALUES (1, '{篮球,足球,游戏}');
SELECT * FROM user_hobby WHERE '篮球' = ANY(hobby);  -- 查询爱好包含篮球的用户

-- Pgsql JSONB 示例
CREATE TABLE IF NOT EXISTS user_extend (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    extend_info JSONB  -- JSONB 类型，支持索引
);
INSERT INTO user_extend (user_id, extend_info) VALUES (1, '{"address": "北京", "phone": "13800138000"}');
CREATE INDEX idx_extend_info ON user_extend USING GIN (extend_info);  -- 给 JSONB 字段建索引
SELECT * FROM user_extend WHERE extend_info ->> 'address' = '北京';  -- 查询地址为北京的用户

-- MySQL ENUM 类型示例
CREATE TABLE IF NOT EXISTS user_status (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    status ENUM('正常', '禁用', '冻结') DEFAULT '正常'
);

3.2 索引差异（影响查询性能）

索引是提升查询性能的核心，两者都支持 B-Tree 索引（最常用），但 Pgsql 支持更多索引类型，适配更复杂的查询场景。

索引类型	MySQL	Pgsql	说明
B-Tree 索引	支持	支持	默认索引类型，适合等值查询、范围查询
Hash 索引	支持（基本不用）	支持	适合等值查询，不支持范围查询
GIN 索引	不支持	支持	适合数组、JSONB、全文搜索
GiST 索引	不支持	支持	适合 GIS 地理类型、全文搜索
全文索引	支持（功能简单）	支持（功能强大）	Pgsql 支持中文全文搜索，无需额外插件

3.3 选型建议（结合 CRUD 差异）

结合前面的 CRUD 差异和拓展知识点，给出具体的选型建议，帮助开发者快速匹配业务场景：

优先选 MySQL 的场景：
- 互联网高频 CRUD 场景（如电商、用户系统），追求高并发、高可用，配置简单、维护成本低；
- 开发团队熟悉 MySQL 生态，需要大量批量导入/导出数据（LOAD DATA INFILE 优势明显）；
- 业务逻辑简单，不需要复杂数据类型（如 JSON、数组）和复杂查询。
优先选 Pgsql 的场景：
- 复杂业务场景（如金融、数据分析），追求数据强一致性、复杂查询优化（如多表关联、子查询）；
- 需要使用高级数据类型（JSONB、数组、GIS），或需要全文搜索、区间查询；
- 高并发更新场景（无间隙锁，锁冲突概率低），或需要自定义函数、存储过程（Pgsql 支持更完善）。

十一、不守规矩的优秀

tsvector 类型

作用是将原本冗长、非结构化的文本（如文章标题和内容），转换成一种经过预处理、排序且去重的“关键词列表”。

1. `TSVECTOR` 到底是什么？

TSVECTOR（Text Search Vector）是一个有序的、无重复的词位（Lexeme）列表。当你把一段中文文本放入 to_tsvector() 函数处理后，它会变成类似这样的结构：

SELECT to_tsvector('chinese_zh', '小明硕士毕业于中国科学院计算所，后在日本京都大学深造');

# to_tsvector                                                          |
# ---------------------------------------------------------------------+
# '中国科学院计算所':4 '小明':1 '日本京都大学':5 '毕业':3 '深造':6 '硕士':2|

注意这里发生了什么变化：

分词： 句子被切成了独立的词（对于中文，这通常依赖 zhparser 等插件）。
去重与排序： 相同的词只保留一个，并按字母顺序排列，方便计算机快速查找。
位置记录： 数字（如 :1, :4）代表该词在原句中出现的第几个位置，这对于计算相关性排名（Ranking）和短语搜索至关重要。

2. 为什么要专门用这个类型？（核心作用）

如果不使用 TSVECTOR，你通常会用 LIKE '%关键词%' 来搜索。这种方式在处理大量数据时非常慢，因为数据库必须进行全表扫描，逐行读取文本进行比对。

使用 TSVECTOR 带来了以下巨大的优势：

🚀 极致的查询性能 (配合 GIN 索引)

这是最主要的原因。TSVECTOR 格式非常适合建立 GIN 索引（倒排索引）。

没有 TSVECTOR： 就像你要在一堆乱序的书里找包含“苹果”的句子，你必须翻开每一页从头读到尾。
有 TSVECTOR + GIN 索引： 就像查字典或书后的索引，数据库直接跳到包含“苹果”的行，速度是毫秒级的，即使有几百万条数据也能瞬间返回。

🧠 智能的语义处理 (标准化)

TSVECTOR 不仅仅是切分字符串，它还负责“清洗”数据：

去除停用词： 自动过滤掉像“的”、“了”、“是”这种出现频率极高但对搜索没意义的词，减小存储体积并提高准确度。
词形归一化： 虽然主要针对英文（如将 running 变为 run），但在中文语境下，它确保了存储的是标准的“词”，而不是杂乱的字符组合。

📊 支持相关性排序

因为 TSVECTOR 记录了词的位置和权重（Weight），PostgreSQL 可以计算出某篇文章与你的搜索词有多“匹配”。

例如：搜索词出现在标题里的文章，得分会比只出现在正文末尾的文章高。通过 ts_rank() 函数，你可以实现像百度或谷歌那样的“按相关性排序”功能。

3. 形象的对比

特性	普通文本 (`TEXT`)	向量列 (`TSVECTOR`)
存储形式	“我喜欢吃苹果，你也喜欢吃苹果吗？”	`'吃':3,6 '苹果':4,7 '喜欢':2,5` (示例)
搜索方式	`LIKE '%苹果%'` (模糊匹配)	`@@ to_tsquery('苹果')` (精确匹配)
索引支持	B-Tree 索引对 `%` 前缀无效，效率低	GIN 索引，极速查找
适用场景	简单的字符串查找	复杂的全文检索、排序、高亮

说明：如果不需要限制字符串的长度，使用 TEXT 或 VARCHAR（不带长度参数）在 PostgreSQL 中是完全等价的，在性能和存储上几乎没有差别。

@@ 操作符

@@ 是一个多态操作符，其功能由上下文决定。
也就是说 @@ 是一个功能强大的操作符，其 具体含义 取决于它操作的 数据类型。
它主要有两大应用场景：全文检索和JSON/JSONB 查询。

操作数类型	功能描述	核心用途
`tsvector`, `tsquery`	全文检索匹配	判断文档是否包含指定的关键词或短语
`jsonb`, `jsonpath`	JSON 路径断言	判断 JSON 数据是否满足某个路径表达式定义的条件

场景一：全文检索 (Full-Text Search)

这是 @@ 最常见的用法，用于判断一个文档（tsvector 类型）是否包含某个查询（tsquery 类型）。当匹配成功时返回 true，否则返回 false。

核心概念

tsvector: 一种经过预处理的文档格式。它将文本分割成词位（lexemes），并进行标准化处理（如转小写、词干化），同时记录每个词在原文中的位置。
tsquery: 一种经过预处理的查询格式。它将查询字符串转换成可以与 tsvector 匹配的结构，并支持布尔运算符（& 与, | 或, ! 非）和短语运算符（<-> 紧随）。

SQL 演示：

SELECT to_tsvector('chinese_zh', '小明硕士毕业于中国科学院计算所，后在日本京都大学深造');

to_tsvector                                        |
---------------------------------------------------+
'中国科学院计算所':4 '小明':1 '日本京都大学':5 '毕业':3 '深造':6 '硕士':2|


-- 1. 基础匹配：检查文档中是否同时包含 '小明' 和 '毕业'
SELECT to_tsvector('chinese_zh', '小明硕士毕业于中国科学院计算所，后在日本京都大学深造') @@ to_tsquery('chinese_zh', '小明 & 毕业');-- true

-- 2. 或 逻辑的 匹配
SELECT to_tsvector('chinese_zh', '小明硕士毕业于中国科学院计算所，后在日本京都大学深造') @@ to_tsquery('chinese_zh', '小明 | 毕业'); -- true

-- 3. 非 逻辑的 匹配
SELECT to_tsvector('chinese_zh', '小明硕士毕业于中国科学院计算所，后在日本京都大学深造') @@ to_tsquery('chinese_zh', '小明 |!毕业'); -- true
SELECT to_tsvector('chinese_zh', '小明硕士毕业于中国科学院计算所，后在日本京都大学深造') @@ to_tsquery('chinese_zh', '小明 &!毕业'); -- false

-- 4.  短语搜索：'小明' 后面必须紧跟着 '硕士'
SELECT to_tsvector('chinese_zh', '小明硕士毕业于中国科学院计算所，后在日本京都大学深造') @@ to_tsquery('chinese_zh', '小明 <-> 硕士'); -- true
SELECT to_tsvector('chinese_zh', '小明硕士毕业于中国科学院计算所，后在日本京都大学深造') @@ to_tsquery('chinese_zh', '小明 <-> 毕业'); -- false

语法

@@ 操作符支持多种参数组合，PostgreSQL 会进行隐式转换：

语法	描述
`tsvector @@ tsquery`	标准用法，文档匹配查询
`tsquery @@ tsvector`	顺序可互换
`text @@ tsquery`	文本会被隐式转换为 `tsvector`
`text @@ text`	两个文本都会被转换，等同于 `to_tsvector(x) @@ plainto_tsquery(y)`

1. `tsvector @@ tsquery` (标准用法)

这是最规范、性能最好的写法。左边是处理好的文档（向量），右边是处理好的查询（条件）。

-- 左边：to_tsvector 将文档转换为向量
-- 右边：to_tsquery 将查询词转换为带逻辑的查询对象
SELECT to_tsvector('english', 'PostgreSQL is a powerful database') 
       @@ 
       to_tsquery('english', 'powerful & database');
-- 结果: t (true)
-- 原理: 向量中包含 'power' 和 'databas' (词干化后)，查询要求两者同时存在，匹配成功。

2. `tsquery @@ tsvector` (顺序互换)

@@ 操作符支持交换律。虽然逻辑上我们习惯说“文档匹配查询”，但在 SQL 中反过来写“查询匹配文档”也是完全合法的，结果一样。

-- 左边：查询对象
-- 右边：文档向量
SELECT to_tsquery('english', 'powerful') 
       @@ 
       to_tsvector('english', 'PostgreSQL is a powerful database');
-- 结果: t (true)
-- 原理: 只要查询中的词在向量里能找到，就返回 true。

3. `text @@ tsquery` (左侧隐式转换)

左边直接写字符串，右边写标准的查询对象。PostgreSQL 会自动把左边的字符串转换成 tsvector。

-- 左边：原始文本 'PostgreSQL is a powerful database'
-- 右边：标准的 tsquery
SELECT 'PostgreSQL is a powerful database' 
       @@ 
       to_tsquery('english', 'powerful');
-- 结果: t (true)
-- 原理: 数据库内部自动执行了 to_tsvector('PostgreSQL...')，然后进行匹配。

4. `text @@ text` (全隐式转换 - 最简写法)

两边都是普通字符串。这通常用于快速测试。
关键点：右边的文本会被当作自然语言处理，自动转为 plainto_tsquery（即单词之间默认是 AND 关系）。

-- 左边：原始文档
-- 右边：普通搜索字符串 (注意：这里不能写 & 或 |，会被当成普通单词)
SELECT 'PostgreSQL is a powerful database' 
       @@ 
       'powerful database'; 
-- 结果: t (true)
-- 原理: 等同于 to_tsvector(...) @@ plainto_tsquery('powerful database')
--       也就是查找同时包含 'powerful' 和 'database' 的文档。

避坑指南

虽然第 3 和第 4 种写法（隐式转换）写起来很爽，但在生产环境中，我强烈建议你使用 第 1 种 (tsvector @@ tsquery) 显式写法。

原因如下：

配置不明确：隐式转换会使用数据库默认的 default_text_search_config 配置（通常是 simple 或 english）。如果你的业务需要中文分词（如 chinese_zh），隐式转换会导致分词失败或不准。
性能隐患：显式写出 to_tsvector 可以让你更清楚地配合 GIN 索引使用，避免全表扫描时的意外行为。

场景二：JSON/JSONB 路径查询

当操作数是 jsonb 类型和 jsonpath 类型时，@@ 的作用变为检查 JSON 路径的断言是否返回 TRUE。

这与另一个操作符 @? 不同：

@?：针对路径，检查 JSON 路径是否能匹配到任何元素（存在性检查）。
@@：针对路径的值，检查 JSON 路径的断言表达式结果是否为 TRUE。

语法与演示

SQL 演示：

假设我们有一个名为 test_json 的表，其中包含一个 gps_jsonb 列。

-- 假设 gps_jsonb 列的数据结构如下：
-- {"track": {"segments": [{"HR": 73}, {"HR": 135}]}}

-- 1. 使用 @@ 检查断言：判断 segments 数组的元素数量是否小于 3
SELECT gps_jsonb @@ '$.track.segments.size() < 3' FROM test_json;
-- 结果: t (true)，因为数组有2个元素，2 < 3 为真

-- 2. 使用 @@ 检查断言：判断是否存在 HR > 100 的元素
-- 注意：这里 @@ 会检查整个路径表达式的布尔结果
SELECT gps_jsonb @@ '$.track.segments[*] ? (@.HR > 100)' FROM test_json;
-- 结果: t (true)，因为存在一个元素 HR 为 135，满足条件

-- 3. 对比 @? 操作符：只检查路径是否存在，不评估断言的真假
-- 这条语句会返回 true，因为它找到了 segments 数组，而不关心 HR 的值
SELECT gps_jsonb @? '$.track.segments[*] ? (@.HR > 100)' FROM test_json;
-- 结果: t (true)

@家族操作符汇总

操作符	名称	核心用途
`@?`	JSON Path 存在	路径能否找到元素
`@@`	JSON Path 断言	路径的布尔表达式是否为真
`@>`	包含	左侧是否包含右侧
`<@`	被包含	左侧是否被右侧包含
`?`	键存在	顶层是否存在某个键
`?\|`	任意键存在	顶层是否存在数组中的任意键
`?&`	所有键存在	顶层是否同时存在所有键
`->`	获取JSONB	按索引或键获取元素
`->>`	获取文本	按索引或键获取文本值
`#>`	按路径获取JSONB	按路径数组获取嵌套元素
`#>>`	按路径获取文本	按路径数组获取文本值
`-`	删除	删除键或数组元素
`\|\|`	连接	合并两个 JSONB

📜 路径查询操作符 (JSON Path)

这组操作符使用 SQL/JSON Path 语言进行查询，功能强大，适合复杂逻辑。

@? (路径存在性检查)
- 作用：检查 JSON 数据中是否存在能被指定路径匹配到的元素。只要有匹配，就返回 true。
- 示例：'{"a": [1, 2, 3]}'::jsonb @? '$.a[*] ? (@ > 2)' 返回 t，因为路径能找到大于2的元素。
@@ (路径断言检查)
- 作用：检查 JSON 路径的断言表达式结果是否为 TRUE。它更侧重于逻辑判断。
- 示例：'{"a": [1, 2, 3]}'::jsonb @@ '$.a.size() > 2' 返回 t，因为数组大小确实大于2。

📦 包含关系操作符

这组操作符用于判断一个 JSONB 文档是否包含另一个，是日常开发中最常用的。

@> (包含)
- 作用：判断左边的 jsonb 是否包含右边的 jsonb。这是 GIN 索引最擅长加速的查询之一。
- 示例：'{"name": "Alice", "age": 30}'::jsonb @> '{"name": "Alice"}'::jsonb 返回 t。
<@ (被包含)
- 作用：与 @> 相反，判断左边的 jsonb 是否被包含于右边的 jsonb。
- 示例：'{"name": "Alice"}'::jsonb <@ '{"name": "Alice", "age": 30}'::jsonb 返回 t。

🔑 键/元素存在性操作符

这组操作符专门用于检查 JSONB 对象的顶层键或数组元素是否存在。

? (键存在)
- 作用：检查 jsonb 的顶层是否存在指定的键。
- 示例：'{"a": 1, "b": 2}'::jsonb ? 'a' 返回 t。
?| (任意键存在)
- 作用：检查 jsonb 的顶层是否存在给定文本数组中的任意一个键。
- 示例：'{"a": 1, "b": 2}'::jsonb ?| array['a', 'c'] 返回 t (因为 ‘a’ 存在)。
?& (所有键存在)
- 作用：检查 jsonb 的顶层是否同时存在给定文本数组中的所有键。
- 示例：'{"a": 1, "b": 2}'::jsonb ?& array['a', 'b'] 返回 t。

✂️ 提取与修改操作符

这组操作符用于获取 JSONB 中的值或对其进行修改。

-> (获取 JSONB 对象)
- 作用：根据键或数组索引获取子元素，返回类型仍是 jsonb。
- 示例：'{"a": {"b": "foo"}}'::jsonb->'a' 返回 {"b": "foo"}。
->> (获取文本)
- 作用：根据键或数组索引获取子元素，但返回类型是 text。常用于与字符串进行比较。
- 示例：'{"a": "hello"}'::jsonb->>'a' 返回 hello (没有引号)。
#> (按路径获取 JSONB 对象)
- 作用：通过一个路径数组（如 '{a,b}'）来获取深层嵌套的元素，返回 jsonb。
- 示例：'{"a": {"b": "foo"}}'::jsonb #> '{a,b}' 返回 "foo"。
#>> (按路径获取文本)
- 作用：与 #> 类似，但返回类型是 text。
- 示例：'{"a": {"b": "foo"}}'::jsonb #>> '{a,b}' 返回 foo。
- (删除)
- 作用：从 jsonb 中删除指定的键或数组元素。
- 示例：'{"a": 1, "b": 2}'::jsonb - 'a' 返回 {"b": 2}。
|| (连接/合并)
- 作用：将两个 jsonb 值合并为一个新的 jsonb 值。
- 示例：'["a", "b"]'::jsonb || '["c", "d"]'::jsonb 返回 ["a", "b", "c", "d"]。

类型断言

在 PostgreSQL 中，::类型 是一种类型转换语法，通常被称为PostgreSQL 风格的类型转换符。

以 ::jsonb 简单来说，它的作用是将前面的数据（通常是字符串）强制转换为 jsonb 类型，以便存入数据库或进行特定的 JSON 操作。

以下是详细的解释和用法示例：

1. 语法含义

::：这是 PostgreSQL 特有的操作符，用于将左侧的表达式转换为右侧指定的数据类型。
jsonb：这是目标数据类型，代表以二进制格式存储的 JSON 数据。

整体含义：把 :: 左边的内容，当作 jsonb 类型来处理。

2. 为什么要用它？

PostgreSQL 对类型检查非常严格。如果你直接写一个 JSON 格式的字符串（例如 '{"a": 1}'），数据库默认会把它当作普通的文本（text）。
如果你试图把这个文本存入 jsonb 类型的字段，或者用 JSON 特有的操作符（如 ->）去查询它，数据库会报错，除非你先告诉它：“嘿，这不是普通文本，这是 JSONB 数据。”

3. 常见用法示例

A. 在插入数据时 (INSERT)

当你向表中插入 JSON 数据时，通常需要转换：

-- 错误写法（如果字段是 jsonb 类型）：
-- INSERT INTO my_table (data) VALUES ('{"key": "value"}'); 
-- 数据库可能会认为这是字符串，导致类型不匹配错误。

-- 正确写法：
INSERT INTO my_table (data) VALUES ('{"key": "value"}'::jsonb);

B. 在查询或测试时

当你想要直接测试 JSON 函数或操作符时：

-- 提取 JSON 中的 "name" 字段
SELECT '{"name": "Alice", "age": 30}'::jsonb -> 'name';
-- 结果: "Alice"

4. 标准 SQL 写法 (替代方案)

除了使用 ::，PostgreSQL 也支持标准的 SQL 类型转换语法 CAST(... AS ...)。
两者的效果是完全一样的：

PostgreSQL 简写（推荐，更常用）：
```
SELECT '{"a": 1}'::jsonb;
```
标准 SQL 写法：
```
SELECT CAST('{"a": 1}' AS jsonb);
```

5. 常见类型转换

以下是除了 ::jsonb 之外，最常见和实用的 :: 转换用法：

目标类型	示例代码	说明
JSONB	`'{"a":1}'::JSONB`	将字符串转为 JSON 对象
整数	`'100'::INTEGER`	字符串转数字
文本	`100::TEXT`	数字转字符串
布尔	`'yes'::BOOLEAN`	识别 yes/no, true/false
日期	`'2023-01-01'::DATE`	字符串转日期
时间间隔	`'1 hour'::INTERVAL`	自然语言转时间间隔
数组	`'{1,2}'::INT[]`	字符串转数组

Json路径的两大家族

在 PostgreSQL 的 jsonb 查询中，$.track.segments 和 'track'->>'segments' 是两种完全不同但功能相似的语法，它们分别属于两套不同的查询体系。

简单来说：

$.track.segments 属于 SQL/JSON Path 语言，是一种更现代、功能更强大的路径表达式。
'track'->>'segments' 属于 原生 JSON 操作符，是 PostgreSQL 早期版本就支持的链式访问方式。

下面将详细解读这两种语法。

SQL/JSON Path (`$.track.segments`)

这是一种标准化的路径语言，语法风格类似于 JavaScript 的对象属性访问。它通常作为参数传递给专门的函数，如 jsonb_path_query、jsonb_path_exists 等。

核心特点

以 $ 开头：代表当前 JSON 文档的根对象。
使用点号 .：用于访问对象的键。
使用中括号 []：用于访问数组元素，支持索引（如 [0]）和通配符（如 [*]）。
功能强大：支持复杂的过滤表达式（如 ?(@.HR > 130)）、递归搜索等高级功能。

SQL 演示

假设我们有一个名为 my_table 的表，其中包含一个 data 列（jsonb 类型），数据如下：

{
  "track": {
    "segments": [
      {"location": [47.763, 13.4034], "HR": 73},
      {"location": [47.706, 13.2635], "HR": 135}
    ]
  }
}

-- 1. 查询 'segments' 数组
-- 返回整个 segments 数组
SELECT jsonb_path_query(data, '$.track.segments') FROM my_table;

-- 2. 查询数组中第一个元素的 'location'
-- 返回: [47.763, 13.4034]
SELECT jsonb_path_query(data, '$.track.segments[0].location') FROM my_table;

-- 3. 查询所有元素的 'location' (使用通配符)
-- 返回两行，分别是两个 location 数组
SELECT jsonb_path_query(data, '$.track.segments[*].location') FROM my_table;

-- 4. 条件过滤：查询 HR 大于 100 的 'start time'
-- 返回: "2018-10-14 10:39:21"
SELECT jsonb_path_query(data, '$.track.segments[*] ? (@.HR > 100)."start time"') FROM my_table;

原生 JSON 操作符 (`'track'->>'segments'`)

这是 PostgreSQL 特有的链式操作符，通过组合 -> 和 ->> 来逐层深入 JSON 结构。

核心特点

-> 操作符：返回 jsonb 类型的值。返回的结果可以继续被其他操作符访问。
->> 操作符：返回 text 类型的值。这是链式访问的终点，通常用于获取最终的标量值（如字符串、数字）。
链式调用：通过 -> 一层层深入，直到使用 ->> 获取最终值。

SQL 演示

使用与上面相同的数据结构。

-- 1. 获取 'track' 对象 (返回 jsonb 类型)
-- 结果: {"segments": [...]}
SELECT data->'track' FROM my_table;

-- 2. 获取 'segments' 数组 (返回 jsonb 类型)
-- 结果: [{"location":...}, {"location":...}]
SELECT data->'track'->'segments' FROM my_table;

-- 3. 获取第一个元素的 'location' (返回 jsonb 类型)
-- 结果: [47.763, 13.4034]
SELECT data->'track'->'segments'->0->'location' FROM my_table;

-- 4. 获取第一个元素的 'HR' 值 (返回 text 类型)
-- 结果: "73" (注意，这是一个文本字符串)
SELECT data->'track'->'segments'->0->>'HR' FROM my_table;

对比

特性	SQL/JSON Path (`$.track.segments`)	原生操作符 (`'track'->'segments'`)
语法风格	类似 JavaScript 的路径表达式	PostgreSQL 特有的链式操作符
核心符号	`$`, `.`, `[]`, `?()`	`->`, `->>`
使用方式	作为参数传给函数，如 `jsonb_path_query()`	直接在列名后链式调用
主要优势	功能强大，支持复杂过滤、通配符和递归	语法直观，适合简单的逐层访问
返回类型	由函数决定，通常是 `jsonb` 或 `setof jsonb`	`->` 返回 `jsonb`，`->>` 返回 `text`

区别

它们在功能上并不等价，无法做到 100% 的互相替代。

虽然对于绝大多数常见的读取和查询场景，两者确实可以互换使用，但在一些特定的功能上，它们各有侧重，存在一方能做到而另一方做不到（或做起来非常麻烦）的情况。

简单来说，可以这样理解：

原生操作符 (->, ->>, @>)：像是为日常高频操作定制的快捷工具，在简单操作上非常高效。
SQL/JSON Path ($.a.b, jsonb_path_query)：像是一门功能完备的编程语言，语法更强大、更通用，能处理极其复杂的查询。

下面详细解释它们之间“不等价”的地方。

⚠️ 原生操作符能做到，但 SQL/JSON Path 难以替代的场景

1. 修改 JSONB 数据

这是最核心的区别。PostgreSQL 提供了专门用于修改 jsonb 数据的函数，如 jsonb_set 和 jsonb_insert，而这些函数要求使用原生操作符的路径语法（即文本数组）。

场景：你需要更新一个嵌套字段的值。

原生操作符：可以直接与 jsonb_set 函数配合使用。

-- 将 data 列中 'user'->'address'->'city' 的值修改为 'Shanghai'
UPDATE my_table
SET data = jsonb_set(data, '{user,address,city}', '"Shanghai"');

SQL/JSON Path：无法直接用于数据修改。你不能用 jsonb_set 函数来接收一个 $.user.address.city 这样的路径。

2. 高性能的“包含”查询 (`@>`)

当配合 GIN 索引使用时，原生操作符 @> 在查询“是否包含某个子结构”时，性能通常优于 SQL/JSON Path。

场景：查询 tags 数组中包含 "postgresql" 的所有记录。

原生操作符：语法简洁，且能高效利用 GIN 索引。

-- 查询 tags 数组包含 "postgresql" 的记录
SELECT * FROM my_table WHERE data @> '{"tags": ["postgresql"]}';

SQL/JSON Path：虽然也能实现，但语法更复杂，且在某些 PostgreSQL 版本中，其执行计划可能不如 @> 操作符优化得好。
```
-- 功能等价，但写法更繁琐
SELECT * FROM my_table
WHERE jsonb_path_exists(data, '$.tags[*] ? (@ == "postgresql")');
```

🚀 SQL/JSON Path 能做到，但原生操作符难以实现的场景

1. 复杂的数组过滤与条件查询

这是 SQL/JSON Path 的绝对优势领域。当需要对数组中的元素进行复杂的逻辑判断时，原生操作符会变得非常笨拙，甚至无法实现。

场景：查询 items 数组中，存在 price 大于 100 且 quantity 小于 5 的商品。

SQL/JSON Path：可以轻松表达这种复杂的嵌套条件。

-- 查找 items 数组中存在 price > 100 且 quantity < 5 的元素
SELECT * FROM my_table
WHERE jsonb_path_exists(data, '$.items[*] ? (@.price > 100 && @.quantity < 5)');

原生操作符：几乎无法直接实现。你需要先将数组展开成多行（使用 jsonb_array_elements），进行过滤，然后再聚合回去，过程非常繁琐且性能较差。

2. 递归搜索

当你不知道目标字段在 JSON 文档中的确切层级时，SQL/JSON Path 提供了强大的递归搜索能力。

场景：在整个 JSON 文档中查找任意层级的 "error_code" 字段。

SQL/JSON Path：使用 ** 操作符可以一键实现。

-- 递归搜索整个文档，找出所有 error_code 的值
SELECT jsonb_path_query(data, '$**.error_code') FROM my_table;

原生操作符：完全不支持递归搜索。你必须事先知道完整的路径，如 data->'level1'->'level2'->>'error_code'。

功能场景	原生操作符 (`->`, `@>`)	SQL/JSON Path (`$.a.b`)	胜出者
数据修改	支持 (`jsonb_set`)	不支持	原生操作符
简单包含查询	高效 (`@>` + GIN索引)	可用，但稍繁琐	原生操作符
复杂数组过滤	极难实现	原生支持，语法强大	SQL/JSON Path
递归搜索	不支持	原生支持 (`$**`)	SQL/JSON Path
简单路径访问	直观 (`a->'b'->>'c'`)	直观 (`$.a.b.c`)	平手

因此，在实际开发中，最佳实践是结合使用两者：

用原生操作符进行简单的数据读取、更新和高性能包含查询。
用SQL/JSON Path处理复杂的、需要逻辑判断或递归的查询场景。

GIN索引

在 PostgreSQL 中，GIN（Generalized Inverted Index，通用倒排索引） 是一种专门为复杂数据类型设计的索引。

如果说 B-Tree 索引是为了处理“单值”（如数字、字符串）而生的标准工具，那么 GIN 索引就是为了处理“多值”和“复合结构”而生的重型武器。它类似于搜索引擎（如 Elasticsearch）底层的倒排索引机制。

以下是对 GIN 索引作用、原理、用法及最佳实践的详细解读。

一、 GIN 索引的核心作用

GIN 索引主要用于加速包含多个值的列的查询。它最擅长处理以下三种数据类型：

数组 (Arrays)：例如 ['tag1', 'tag2']。
JSON/JSONB：存储半结构化数据的字段。
全文检索 (tsvector)：经过分词处理后的文本向量。

它的核心价值在于： 当你需要查询“某个字段包含特定元素”或“与特定值有交集”时，GIN 能将查询速度从全表扫描（O(N)）提升到毫秒级（O(log N)）。

二、原理通俗解读：什么是“倒排”？

为了理解 GIN，我们需要对比传统的 B-Tree 索引：

B-Tree (正向索引)：
- 逻辑：行号 -> 值
- 场景：查找“第 100 行用户的名字是什么？”非常快。
- 局限：如果名字是 ['张三', '李四']，B-Tree 很难处理“查找所有名字里包含‘张三’的行”。
GIN (倒排索引)：
- 逻辑：值 -> 行号列表 (Posting List)
- 场景：查找“谁的名字里包含‘张三’？”
- 机制：GIN 会把数组或 JSON 拆开，建立类似字典的映射：
  - 关键词 “张三” -> 出现在 [第 1 行, 第 5 行]
  - 关键词 “李四” -> 出现在 [第 2 行, 第 5 行]
- 查询时，直接查“张三”，拿到行号列表，瞬间定位数据。

三、详细用法与 SQL 演示

1. 数组 (Array) 场景

这是 GIN 最直观的应用，常用于标签系统。

场景：商品表 products，有一个 tags 字段存储标签数组。

-- 1. 建表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    tags TEXT[]  -- 文本数组
);

-- 2. 创建 GIN 索引
-- 语法：CREATE INDEX ON 表名 USING GIN (列名);
CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- 3. 常用查询操作符
-- @> (包含): 查询 tags 数组中包含 '电子产品' 的商品
SELECT * FROM products WHERE tags @> ARRAY['电子产品'];

-- && (交集): 查询 tags 数组中包含 '促销' 或 '新品' 任意一个的商品
SELECT * FROM products WHERE tags && ARRAY['促销', '新品'];

2. JSONB 场景

GIN 是处理 JSONB 数据的标配，能极大提升 NoSQL 风格查询的性能。

场景：用户表 users，有一个 profile 字段存储 JSONB 格式的用户画像。

-- 1. 建表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    profile JSONB
);

-- 2. 创建 GIN 索引
-- 方式 A：全字段索引（索引所有键值对，体积大但通用性强）
CREATE INDEX idx_users_profile ON users USING GIN (profile);

-- 方式 B：路径操作符类索引 (jsonb_path_ops)
-- 推荐用于只查询键是否存在或特定路径的场景，索引体积更小，写入更快
CREATE INDEX idx_users_profile_path ON users USING GIN (profile jsonb_path_ops);

-- 3. 常用查询
-- @> (包含): 查询 age 为 25 的用户
SELECT * FROM users WHERE profile @> '{"age": 25}';

-- ? (存在键): 查询包含 'email' 键的用户
SELECT * FROM users WHERE profile ? 'email';

3. 全文检索 (Full-Text Search) 场景

这是 GIN 最经典的用途，配合 tsvector 使用。

场景：文章表 articles，搜索内容。

-- 1. 创建索引 (通常配合 to_tsvector 函数)
-- 注意：这里演示的是表达式索引，直接对函数结果建索引
CREATE INDEX idx_articles_content ON articles USING GIN (to_tsvector('simple', content));

-- 2. 查询
-- @@ (匹配): 搜索包含 '数据库' 和 '优化' 的文章
SELECT * FROM articles 
WHERE to_tsvector('simple', content) @@ to_tsquery('simple', '数据库 & 优化');

四、 GIN 索引的优缺点权衡

在使用 GIN 之前，你必须了解它的代价。它不是免费的午餐。

特性	表现	说明
查询速度	🚀 极快	尤其在处理大量数据的包含查询 (`@>`) 时，比 B-Tree 快几个数量级。
写入/更新速度	🐢 较慢	因为插入一行数据可能需要拆分出多个索引项（如数组有10个元素，就要插10次索引），开销很大。
存储空间	📦 较大	GIN 索引通常比 B-Tree 索引占用更多的磁盘空间。
构建时间	⏳ 较长	创建索引的过程比较耗时。

五、最佳实践与性能调优

1. 利用 `fastupdate` 机制

由于 GIN 写入慢，PostgreSQL 引入了 fastupdate 功能（默认开启）。

原理：新数据先写入一个临时的“待处理列表” (Pending List)，不立即合并到主索引树中。这能显著提高插入速度。
维护：后台进程（Autovacuum）会定期将待处理列表合并到主索引。如果写入极其频繁，待处理列表可能会变大，影响查询性能。
调优：可以通过 gin_pending_list_limit 参数控制列表大小。

2. 选择合适的操作符类

对于 JSONB，如果你只需要查询键的存在性或简单路径，使用 jsonb_path_ops 可以显著减小索引体积并提高写入性能。

-- 推荐：体积小，适合大多数 JSONB 查询
CREATE INDEX idx_data ON my_table USING GIN (data jsonb_path_ops);

3. 批量导入数据时的技巧

如果你要往表中导入海量数据（例如百万级），建议：

先删除 GIN 索引。
执行批量插入。
最后重建 GIN 索引。
- 原因：边插边维护 GIN 索引非常慢，一次性构建索引要快得多。

4. 什么时候不要用 GIN？

单值精确查询：如果列里存的是单纯的整数或字符串（非数组/非JSON），请使用 B-Tree。
范围查询：GIN 不支持范围查询（如 > 100），范围查询请用 B-Tree 或 BRIN。
高频更新的表：如果你的表每秒都有大量更新，且更新字段上有 GIN 索引，数据库负载会非常高。

总结

GIN 索引是 PostgreSQL 处理复杂数据（数组、JSONB、全文搜索）的神器。

口诀：查“包含”用 GIN，查“相等”用 B-Tree。
代价：用 写入性能 和 磁盘空间 换取了强大的 查询能力。

特性	物理复制	逻辑复制
复制内容	数据页的物理变更 (WAL)	数据变更的逻辑操作 (SQL)
从库结构	必须与主库完全一致	可以与主库不同（如独立索引）
从库能否独立创建GIN索引	不可以	可以
主要用途	高可用、灾难恢复、读写分离	选择性复制、跨版本升级、查询优化

可以利用 pgsq 逻辑复制的主从架构，给从库对应的表加GIN索引，可以充分发挥各自的特点。

创建触发器函数

示例代码：

CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER AS $$
BEGIN
    -- 合并标题和内容，转换为中文分词向量
    NEW.search_vector := to_tsvector('chinese', COALESCE(NEW.title, '') || ' ' || COALESCE(NEW.content, ''));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

1. 函数头定义

CREATE OR REPLACE FUNCTION update_search_vector()

CREATE OR REPLACE:
- CREATE: 创建一个新函数。
- OR REPLACE: 这是一个非常实用的子句。它的意思是“如果这个函数名已经存在了，就替换掉旧的，而不是报错”。这在开发阶段非常有用，你可以反复修改函数逻辑而不需要先删除再创建。
FUNCTION: 声明这是一个数据库函数。
update_search_vector(): 这是函数的名字。括号里是空的，说明这个函数不需要传入任何参数。

2. 返回值类型

RETURNS TRIGGER

RETURNS: 声明这个函数执行完后会返回什么东西。
TRIGGER: 这是一个特殊的类型。它告诉数据库：“这个函数是专门给触发器（Trigger）用的”。
- 这种函数必须返回 TRIGGER 类型。
- 虽然代码里最后返回的是 NEW（一条数据记录），但在定义时，类型必须写 TRIGGER。

3. 语言界定符

AS $$

AS: 后面紧跟函数的具体代码内容。
$$: 这是 PostgreSQL 的美元引用符。
- 因为 SQL 语句里经常用到单引号 '，如果函数体里也用单引号，就会冲突。
- $$ 就像一个大括号，把中间所有的内容（包括换行、单引号）都当作纯文本处理，直到遇到结尾的 $$。

4. 函数体开始

BEGIN

标志着 PL/pgSQL 代码块的逻辑开始。

5. 核心逻辑（最关键的一行）

NEW.search_vector := to_tsvector('chinese', COALESCE(NEW.title, '') || ' ' || COALESCE(NEW.content, ''));

这一行代码非常密集，我们把它拆成 5 个部分来看：

A. NEW.search_vector
- NEW: 这是一个伪记录。它代表即将被插入或更新的那一行新数据。
- .search_vector: 访问这一行数据中的 search_vector 字段。
- 含义：我们要修改即将入库的那行数据的 search_vector 列。
B. :=
- 这是 PL/pgSQL 的赋值运算符。它把右边的计算结果赋值给左边的字段。
C. to_tsvector('chinese', ...)
- 这是 PostgreSQL 的内置函数，用于生成全文检索向量。
- 'chinese': 指定分词的配置字典。
  - 注意：PostgreSQL 原生默认不带中文分词。这里写 'chinese'，前提是你必须已经在数据库里安装并配置好了中文分词插件（比如 zhparser 或 pg_jieba），并且把它们注册成了 'chinese' 这个名字。如果没配置，这里会报错或分词不准。
D. COALESCE(NEW.title, '')
- COALESCE(a, b): 这是一个防 NULL 函数。它的意思是：如果 a 不是空，就取 a；如果 a 是 NULL，就取 b。
- NEW.title: 即将入库的那行数据的标题。
- '': 空字符串。
- 作用：如果标题是空的（NULL），就把它当成空字符串处理。因为 NULL 和任何字符串拼接都会变成 NULL，这会导致分词失败，所以必须用 COALESCE 把 NULL 变成空字符串。
E. || ' ' ||
- ||: 字符串连接符。
- ' ': 中间加一个空格。
- 作用：把标题和内容拼起来，中间用空格隔开，防止标题的最后一个字和内容的第一个字粘在一起被误判为一个词。
整句翻译：

把（标题，如果是空则变空串）和（内容，如果是空则变空串）用空格拼起来，扔进 'chinese' 分词器里处理，生成的向量赋值给新数据的 search_vector 字段。

6. 返回数据

RETURN NEW;

RETURN: 结束函数并返回结果。
NEW: 返回刚才修改过的那行数据。
为什么必须返回 NEW？
- 因为这是一个 BEFORE 触发器。数据库在真正写入数据前拦截了你，你修改了 NEW 里的内容（加了搜索向量），然后必须把修改后的 NEW 交还给数据库，数据库才会把这一行真正写进去。如果你不返回 NEW，数据就不会写入。

7. 函数体结束

END;

标志着逻辑结束。注意这里有个分号 ;。

8. 语言界定符闭合

$$

对应开头的 AS $$，表示函数代码文本结束了。

9. 指定语言

LANGUAGE plpgsql;

LANGUAGE: 声明这个函数是用什么语言写的。
plpgsql: 这是 PostgreSQL 的标准过程语言。
;: 整条 SQL 语句结束。

举个栗子

当你执行 INSERT INTO articles (title, content) VALUES ('你好', '世界'); 时：

数据库准备插入数据。
触发器拦截，调用这个函数。
NEW.title 是 '你好'，NEW.content 是 '世界'。
COALESCE 检查没有空值。
|| 把它们拼成 '你好世界'。
to_tsvector 调用中文分词器，把 '你好世界' 变成向量（比如 '你好':1 '世界':2）。
:= 把这个向量塞进 NEW.search_vector。
RETURN NEW 把填好向量的数据交还给数据库。
数据库把完整的数据写入硬盘。

这就是这段代码的每一个字符的精确含义。

触发器函数中的预设变量

我们可以把它们分为两类：一类是像 NEW 一样存取数据的“伪记录”，另一类是获取系统信息的“元数据变量”。

数据类变量（伪记录）

这两个是用来存取数据的，和你刚才问的 NEW 是一对。

变量名	含义	什么时候有值？	什么时候是 NULL？	权限
`OLD`	旧数据行	`UPDATE` (更新前)`DELETE` (删除前)	`INSERT` (插入时没有旧数据)	只读 (你永远不能修改 `OLD`)
`NEW`	新数据行	`INSERT` (插入时)`UPDATE` (更新后)	`DELETE` (删除时没有新数据)	可读写 (仅限 `BEFORE` 触发器)

记忆口诀：

删的时候只有旧 (OLD)，没有新。
插的时候只有新 (NEW)，没有旧。
改的时候新旧 (OLD & NEW) 都有。

系统元数据变量（只读信息）

这些变量不是数据行，而是告诉你“刚才发生了什么”。它们全是只读的，你只能看，不能改。

变量名	数据类型	含义与用途
`TG_OP`	`TEXT`	操作类型。告诉你当前是哪个动作触发的。返回值是字符串：`'INSERT'`, `'UPDATE'`, `'DELETE'`, `'TRUNCATE'`。(常用于 `IF TG_OP = 'UPDATE' THEN ...` 这种判断)
`TG_TABLE_NAME`	`NAME`	表名。触发器所在的那个表的名字。
`TG_TABLE_SCHEMA`	`NAME`	模式名。表所在的 Schema（比如 `public`）。
`TG_WHEN`	`TEXT`	触发时机。返回 `'BEFORE'` 或 `'AFTER'`。
`TG_LEVEL`	`TEXT`	触发级别。返回 `'ROW'` (行级) 或 `'STATEMENT'` (语句级)。
`TG_NAME`	`NAME`	触发器名。当前正在执行的这个触发器的名字。
`TG_NARGS`	`INTEGER`	参数个数。创建触发器时传了几个参数。
`TG_ARGV[]`	`TEXT[]`	参数数组。创建触发器时传的具体参数内容（数组形式）。

实战演示：把所有变量打印出来

我们可以写一个简单的函数，当你更新数据时，把这些“幕后信息”都打印到日志里，你就能直观地看到它们了。

CREATE OR REPLACE FUNCTION debug_trigger_info()
RETURNS TRIGGER AS $$
BEGIN
    -- 1. 打印操作类型 (TG_OP)
    RAISE NOTICE '触发操作: %', TG_OP; 
    
    -- 2. 打印表名 (TG_TABLE_NAME)
    RAISE NOTICE '操作表: %', TG_TABLE_NAME;
    
    -- 3. 打印触发时机 (TG_WHEN)
    RAISE NOTICE '触发时机: %', TG_WHEN;
    
    -- 4. 打印触发级别 (TG_LEVEL)
    RAISE NOTICE '触发级别: %', TG_LEVEL;
    
    -- 5. 访问数据 (OLD 和 NEW)
    -- 如果是更新操作，打印旧ID和新ID
    IF TG_OP = 'UPDATE' THEN
        RAISE NOTICE '旧数据ID: %, 新数据ID: %', OLD.id, NEW.id;
    END IF;
    
    -- 6. 访问参数 (TG_ARGV)
    -- 假设创建触发器时带了参数，这里可以读取
    -- RAISE NOTICE '传入参数: %', TG_ARGV[0];

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

触发器函数中给分词设置权重

CREATE OR REPLACE FUNCTION update_search_vector_optimized()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector := 
        setweight(to_tsvector('chinese_zh', COALESCE(NEW.title, '')), 'A') ||  -- 标题权重 A（最高）
        setweight(to_tsvector('chinese_zh', COALESCE(NEW.content, '')), 'B');  -- 内容权重 B
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

通过引入 setweight 函数，我们人为地给不同来源的关键词贴上了 “重要性标签”。

这背后的实现机制可以分为三个步骤来理解：打标（加权） -> 合并（拼接） -> 算分（排序）。

下面我为你详细拆解这个过程：

1. 核心函数：`setweight`（贴标签）

setweight(vector, weight) 函数的作用是把一个 tsvector（文本向量）里的每一个词都打上指定的标签。PostgreSQL 定义了 4 个等级的标签：‘A’（最高）、‘B’（高）、‘C’（中）、‘D’（低）。

如果不写 setweight，默认是 ‘D’（最低权重）。

执行过程拆解

假设你插入了一篇文章：

标题 (title)：'电池技术'
内容 (content)：'锂电池的充电速度很快'

第一步：处理标题（赋予 A 权重）

setweight(to_tsvector('chinese_zh', '电池技术'), 'A')

分词结果：'电池':1 '技术':2
加权后结果：'电池':1A '技术':2A
解读：注意词后面的 A。这告诉数据库：“这两个词出现在标题里，非常重要！”

第二步：处理内容（赋予 B 权重）

setweight(to_tsvector('chinese_zh', '锂电池的充电速度很快'), 'B')

分词结果：'充电':4 '速度':5 '很快':6 '锂电池':1 (假设去掉了停用词“的”)
加权后结果：'充电':4B '速度':5B '很快':6B '锂电池':1B
解读：注意词后面的 B。这告诉数据库：“这些词只是正文提及，重要性普通。”

2. 操作符：`||`（大融合）

代码中的 || 是连接符。它将上面两部分加权后的向量合并成一个最终的 search_vector。

合并后的最终结果（存入数据库的样子）：

'电池':1A '技术':2A '充电':4B '速度':5B '很快':6B '锂电池':1B

(注：位置编号 1, 2, 4… 在合并后会重新排列，这里仅为示意)

关键点：现在，search_vector 这一列里，不仅存了词，还存了每个词的身份等级。

3. 最终效果：`ts_rank`（算分排序）

这个权重配置只有在**搜索（排序）**的时候才会真正发挥作用。当你执行搜索查询时，PostgreSQL 的排名函数（如 ts_rank 或 ts_rank_cd）会读取这些权重。

默认评分规则（参考）：

匹配到权重 A 的词：加 1.0 分
匹配到权重 B 的词：加 0.4 分
匹配到权重 C 的词：加 0.2 分
匹配到权重 D 的词：加 0.1 分

如果你的搜索 SQL 是这样写的（利用权重排序）：

SELECT title, ts_rank_cd(search_vector, query) AS rank 
FROM articles 
WHERE search_vector @@ to_tsquery('chinese_zh', '电池') 
ORDER BY rank DESC;

PostgreSQL 在计算 rank 时，会发现文章 1 的匹配词带有 A 标签，文章 2 的匹配词带有 B 标签。即使词频相同，带 A 标签的文章排名也会显著高于带 B 标签的文章。

腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

终极指南：Flink SQL连接器版本管理从混乱到有序的升级之路

Apache Flink作为流处理领域的佼佼者，其SQL连接器的版本管理一直是开发者面临的核心挑战。本文将系统讲解Flink SQL连接器版本管理的最佳实践，帮助你轻松应对版本兼容性问题，实现从混乱到有序的升级之旅。## 连接器版本管理的常见痛点 😫在Flink应用开发中，连接器版本管理常常让开发者头疼不已。不同版本的连接器可能导致各种兼容性问题，例如API变更、功能差异甚至运行时错误。

腾讯云开发者社区

Elasticsearch复杂数据类型终极指南：从入门到精通

Elasticsearch作为功能强大的搜索引擎，支持多种复杂数据类型，让开发者能够灵活处理各种结构化和非结构化数据。本文将带你全面了解Elasticsearch中的复杂数据类型，从基础概念到实际应用，助你轻松掌握数据建模的核心技巧。## 内部对象：构建层级化数据结构在Elasticsearch中，对象类型（Object）是最基础的复杂数据类型之一，用于表示具有嵌套关系的数据。例如，我们可

腾讯云开发者社区

如何快速搭建Neon无服务器PostgreSQL：面向初学者的完整指南

Neon是一款革命性的无服务器PostgreSQL解决方案，它通过分离存储和计算层，实现了自动扩缩容、类代码式数据库分支以及零级扩展能力。本指南将帮助你从零开始搭建Neon开发环境，体验这款创新数据库的强大功能。## 准备工作：环境要求与依赖项在开始搭建Neon环境前，请确保你的系统满足以下要求：- Linux操作系统（推荐Ubuntu 20.04+或Debian 11+）- Git

腾讯云开发者社区

所有评论(0)

查看更多评论

古城小栈

@weixin_45541665

已为社区贡献13条内容

懂Mysql，就能让你秒杀PostgreSql

古城小栈

一、数据表： Mysql —> PgSql

1、标识符引号

2、自增主键

1. 底层实现机制不同（最根本的区别）

2. 删除表时的行为（维护性）

3. 控制权的精细度（灵活性）

4. 代码对比

4. SERIAL 的 5大短板

4.1 同步序列值

4.2 缺乏强制约束（安全性短板）

4.3 权限管理的噩梦（运维短板）

4.4 非标准与兼容性（架构短板）

4.5 残留垃圾（清理短板）

💡 建议

3、类型映射

📊 核心数据类型映射对照表

💡 关键差异深度解析

1. 字符串类型：TEXT 的地位反转

2. 布尔值：从模拟到原生

3. 约束升级：从 UNSIGNED 属性 到 规则性约束

4. JSON 支持：JSONB 是杀手锏

5. 枚举类型：全局 vs 局部

📊 综合 示例

🛠️ 转换后的 PostgreSQL 建表语句

4、注释

5、索引

6、默认值

要不要加 默认值

1. 场景一：设计新表（或数据量很小的表）

2. 场景二：老表变更（生产环境，数据量大）

三步走 策略 实践

🎯 场景背景

第一步：添加字段（允许为空，无默认值）

第二步：分批更新历史数据

第三步：设置默认值和非空约束

7、表选项

二、时间类型

🕒 核心类型：选 TIMESTAMP 还是 TIMESTAMPTZ？

最佳实践

🧪 演示准备：创建对比表

📝 场景一：插入数据（在上海时区）

🔍 场景二：查询数据（在上海时区）

🌍 场景三：切换时区查询（模拟纽约用户）

🌏 时区设置：数据库需要设时区吗？

为什么不建议在配置文件中设置时区？

正确的做法：三层 时区 策略

1. 数据库层（保持 UTC）

2. 连接层（可选：设置会话时区）

3. 应用层（推荐：代码处理）

⏱️ 更新时间自动刷新

步骤 1：创建通用函数

步骤 2：绑定触发器

📅 时间范围搜索：千万别用 BETWEEN

❌ 错误写法

✅ 正确写法：左闭右开 [start, end)

🛠️ 常用时间函数

实战：按天/按月统计

实战： 时间类型的 默认值

三、事务

核心概念

🛠️ 基本操作：BEGIN, COMMIT, ROLLBACK

示例：一个成功的转账事务

示例：一个失败回滚的事务

🔄 高级用法：保存点 (Savepoint)

示例：部分回滚

并发控制：事务隔离级别

📚 实战案例：电商订单处理

和 Mysql 的区别

🏗️ 架构层面的差别：引擎 vs 核心

🔨 DDL 事务支持：最致命的区别

场景演示

🚦 默认隔离级别：保守 vs 激进

♻️ MVCC 实现机制：Undo Log vs 多版本

四、索引

核心差异概览

默认索引结构：B+ 树 vs B 树

MySQL (聚簇索引)

PostgreSQL (堆表结构)

3. 约束升级：从 UNSIGNED 属性到规则性约束

📊 综合示例

要不要加默认值

三步走策略实践

正确的做法：三层时区策略

2. `连接层`（可选：设置会话时区）

✅ 正确写法：左闭右开 `[start, end)`

实战：时间类型的默认值

1、修改默认的本地链接

常见配置优化

1. 修改核心配置文件 `postgresql.conf`

2. 配置访问控制文件 `pg_hba.conf`

2. 使用 `pg_basebackup` 从主库拉取数据

七.二、主从复制——临危受命：从升主

八、用户、权限管理

PostgreSql 的授权解读

1. 模式（Schema ）

3. 模式授权命令解读

4. 发号机（sequence：n.序列 v. 排序）

5. 发号机授权命令解读

6. 更改时空符印（`默认权限`）规则

7. 谁来更改时空符印规则

8. 全部权限有哪些？