PHP小码农

Appearance

CRUD 增删改查 ​

本节讲解对表数据的四类基本操作：增（INSERT）、查（SELECT）、改（UPDATE）、删（DELETE），统称 CRUD（Create / Read / Update / Delete）。学完本节，你将能用 SQL 完成日常业务中绝大多数数据操作，并掌握 RETURNING、UPSERT 等 PostgreSQL 特色能力。

前置知识

阅读本节前，建议先了解：创建表与插入数据

基础概念 ​

CRUD 四个字母分别对应 SQL 的四类语句，其中 SELECT 属于 DQL（Data Query Language），其余三个属于 DML（Data Manipulation Language）。

操作英文SQL 语句作用是否修改数据
增CreateINSERT向表中新增行是
查ReadSELECT从表中读取行否
改UpdateUPDATE修改已存在的行是
删DeleteDELETE删除已存在的行是

几个关键术语：

  • 影响行数（Affected Rows）：INSERT/UPDATE/DELETE 执行后，PostgreSQL 会返回本次操作实际影响的行数。写作 UPDATE 3 表示更新了 3 行；如果实际是 UPDATE 0，说明没有行匹配条件——这往往意味着条件写错了，而非"操作成功"。
  • RETURNING 子句：PostgreSQL 特有（MySQL 无此能力）的扩展，让 INSERT/UPDATE/DELETE 在修改数据的同时返回被修改行的列值，避免"改完再查一遍"的两次往返。
  • UPSERT：INSERT ... ON CONFLICT，即"存在则更新，不存在则插入"，用于实现幂等写入。
  • 原子性（Atomicity）：单条 DML 语句本身是原子的——要么全部行生效，要么一行都不生效（如中途违反约束则整条语句回滚）。注意这与事务的原子性是两个层级。
  • CTID：PostgreSQL 内部用于定位物理行版本的隐藏系统列。UPDATE 实际上并不"原地修改"，而是写入新版本行并将旧版本标记为过期（这正是 MVCC，多版本并发控制 的实现方式），因此 UPDATE 会让行大小与表体积增长。

语法与用法 ​

四种语句的最小骨架：

sql
-- 增：插入一行
INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2);

-- 查：读取行
SELECT 列1, 列2 FROM 表名 WHERE 条件;

-- 改：更新行
UPDATE 表名 SET 列1 = 新值 WHERE 条件;

-- 删：删除行
DELETE FROM 表名 WHERE 条件;

四者都可以配合 RETURNING 使用：

sql
INSERT INTO 表名 (列1) VALUES (值1) RETURNING *;

UPDATE 表名 SET 列1 = 新值 WHERE 条件 RETURNING 列1, 列2;

DELETE FROM 表名 WHERE 条件 RETURNING *;

下面用一张最小的示例表贯穿本节全部代码：

sql
-- 建一张演示用的商品库存表
CREATE TABLE demo_stock (
  sku        TEXT          PRIMARY KEY,           -- 商品编号
  name       TEXT          NOT NULL,              -- 商品名称
  price      NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
  stock      INTEGER       NOT NULL DEFAULT 0 CHECK (stock >= 0),
  tags       TEXT[]        NOT NULL DEFAULT '{}', -- 标签数组
  updated_at TIMESTAMPTZ   NOT NULL DEFAULT now()
);

详细说明 ​

查：SELECT 的基本形态 ​

SELECT 是使用最频繁的语句。它的完整形态包含 WHERE、ORDER BY、LIMIT、GROUP BY 等子句，本节只讲最核心的部分，条件与排序将在下一节展开。

sql
-- 查询所有列（生产环境建议显式列出列名，避免表结构变更导致结果错位）
SELECT * FROM demo_stock;

-- 查询指定列，并给列取别名
SELECT sku, name, price AS unit_price FROM demo_stock;

-- 只查询前 5 行
SELECT sku, name FROM demo_stock LIMIT 5;

-- 加常量列与表达式列
SELECT
  name,
  price,
  price * 1.13 AS price_with_tax   -- 计算列
FROM demo_stock;

SELECT 主要子句一览：

子句作用是否必需
SELECT 列表指定要返回的列或表达式必需
FROM指定数据来源表可选（如 SELECT 1）
WHERE过滤行可选
ORDER BY排序可选
LIMIT / OFFSET限制返回行数与偏移可选

需要注意 SELECT 的逻辑执行顺序与书写顺序不同：

text
书写顺序：SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

这解释了一个常见困惑：为什么 WHERE 里不能使用 SELECT 中定义的列别名（因为 WHERE 先执行），而 ORDER BY 里可以。

sql
-- 错误！WHERE 执行时别名还不存在
SELECT price * 1.13 AS price_with_tax
FROM demo_stock
WHERE price_with_tax > 100;

-- 正确：重复写表达式，或用子查询/CTE
SELECT price * 1.13 AS price_with_tax
FROM demo_stock
WHERE price * 1.13 > 100;

-- 正确：ORDER BY 可以使用别名
SELECT price * 1.13 AS price_with_tax
FROM demo_stock
ORDER BY price_with_tax DESC;

增：INSERT 的多种形式 ​

sql
-- 单行插入
INSERT INTO demo_stock (sku, name, price, stock)
VALUES ('SKU-2001', '机械键盘', 349.00, 120);

-- 多行插入（一次往返写入多行，性能远优于循环单行插入）
INSERT INTO demo_stock (sku, name, price, stock, tags) VALUES
  ('SKU-2002', '无线鼠标',   129.00,  300, '{peripherals, wireless}'),
  ('SKU-2003', 'USB-C 扩展坞', 199.00,  80, '{accessories}'),
  ('SKU-2004', '显示器支架',  259.00,  45, '{accessories, ergonomic}');

-- 插入并返回服务端生成的列值（默认值、序列、生成列等）
INSERT INTO demo_stock (sku, name, price)
VALUES ('SKU-2005', 'HDMI 线缆', 39.00)
RETURNING sku, name, stock, updated_at;

INSERT ... SELECT 可以把查询结果直接写入表中，适合数据搬迁与批量派生：

sql
-- 建一张归档表
CREATE TABLE demo_stock_archive (LIKE demo_stock INCLUDING ALL);

-- 把库存为 0 的商品搬进归档表，并返回搬运结果
INSERT INTO demo_stock_archive (sku, name, price, stock, tags)
SELECT sku, name, price, stock, tags
FROM demo_stock
WHERE stock = 0
RETURNING sku, name;

INSERT 中几个易错点：

场景行为说明
列名列表省略必须按表定义顺序提供所有列的值不推荐，表结构变更即报错
提供 DEFAULT 关键字该列使用默认值常用于只想覆盖部分列
NULL 与 DEFAULT 不同NULL 是显式写入空值，DEFAULT 是使用默认值若列有 NOT NULL 约束，写 NULL 会报错
主键冲突整条语句失败（除非使用 ON CONFLICT）报错信息为 duplicate key value violates unique constraint

改：UPDATE 与 RETURNING ​

UPDATE 的核心是一定要写 WHERE。忘记 WHERE 会更新全表。

sql
-- 修改单列
UPDATE demo_stock
SET stock = 100
WHERE sku = 'SKU-2001';

-- 修改多列，并使用 RETURNING 拿到更新后的值
UPDATE demo_stock
SET stock      = stock + 50,          -- 基于当前值计算
    price      = 399.00,
    updated_at = now()
WHERE sku = 'SKU-2001'
RETURNING sku, price, stock, updated_at;
sql
-- 准备一张调价快照表
CREATE TABLE demo_price_snapshot (sku TEXT PRIMARY KEY, new_price NUMERIC(10, 2) NOT NULL);
INSERT INTO demo_price_snapshot VALUES ('SKU-2001', 359.00), ('SKU-2002', 139.00);

-- 使用 FROM 引用另一张表的数据批量改价（PostgreSQL 的 UPDATE ... FROM）
UPDATE demo_stock AS s
SET price      = p.new_price,
    updated_at = now()
FROM demo_price_snapshot AS p
WHERE s.sku = p.sku
RETURNING s.sku, s.price;

SET 子句的两个重要规则：

  • 右侧表达式看到的是行的旧值。因此 SET a = b, b = a 可以交换两列的值，右侧的 b 与 a 都指更新前的旧值。
  • 不能在同一条语句中重复设置同一列，例如 SET price = 1, price = 2 会报错。
sql
-- 交换两列的值：右侧全部取旧值，可安全互换
CREATE TABLE demo_pair (id INT PRIMARY KEY, a TEXT, b TEXT);
INSERT INTO demo_pair VALUES (1, '左', '右');

UPDATE demo_pair SET a = b, b = a WHERE id = 1;
SELECT * FROM demo_pair;   -- a = '右'，b = '左'
sql
-- 错误！同一列被设置两次
UPDATE demo_stock SET price = 1.00, price = 2.00 WHERE sku = 'SKU-2001';

小技巧：执行 UPDATE/DELETE 前，先用同一份 WHERE 条件跑一次 SELECT count(*)，确认要影响的行数符合预期，再执行修改。

sql
-- 先确认影响范围
SELECT count(*) FROM demo_stock WHERE stock = 0;

-- 确认无误后再删除
DELETE FROM demo_stock WHERE stock = 0 RETURNING sku, name;

删：DELETE 与 TRUNCATE ​

DELETE 删除符合条件的行，可以配合 WHERE、USING、RETURNING：

sql
-- 删除指定行
DELETE FROM demo_stock WHERE sku = 'SKU-2005';

-- 删除并返回被删除的内容（便于审计或二次确认）
DELETE FROM demo_stock
WHERE stock < 10
RETURNING sku, name, stock;

-- 使用 USING 引用其他表作为条件
DELETE FROM demo_stock AS s
USING demo_discontinued AS d
WHERE s.sku = d.sku
RETURNING s.sku, s.name;

TRUNCATE 用于一次性清空整张表，它与 DELETE 的差异非常重要：

对比项DELETE FROM tTRUNCATE TABLE t
粒度可按 WHERE 删部分行只能清空全表（或指定分区）
速度逐行删除，产生大量 WAL 与死元组直接换存储文件，极快
是否可回滚可以（事务内）可以（事务内，PostgreSQL 支持）
触发器触发 BEFORE/AFTER DELETE只触发 TRUNCATE 级触发器
自增序列不重置加 RESTART IDENTITY 可重置
外键引用可删除被引用表中未使用的行若被外键引用则报错，需 CASCADE
权限需要 DELETE 权限需要 TRUNCATE 权限（更严格）
sql
-- 清空归档表并重置自增序列
TRUNCATE TABLE demo_stock_archive RESTART IDENTITY;

-- 一并清空有外键引用关系的表
TRUNCATE TABLE demo_order_item, demo_order RESTART IDENTITY CASCADE;

UPSERT：INSERT ... ON CONFLICT ​

UPSERT = Update + Insert。用于"有则更新，无则插入"的幂等写入场景，例如定时同步上游商品数据。

sql
-- PostgreSQL 9.5+：ON CONFLICT 实现 UPSERT
INSERT INTO demo_stock (sku, name, price, stock)
VALUES ('SKU-2001', '机械键盘 Pro', 379.00, 90)
ON CONFLICT (sku)                       -- 冲突判定的列/约束
DO UPDATE SET
  name       = EXCLUDED.name,           -- EXCLUDED 代表本次尝试插入的行
  price      = EXCLUDED.price,
  stock      = EXCLUDED.stock,
  updated_at = now()
RETURNING sku, name, price, stock;

ON CONFLICT 有两种收尾方式：

sql
-- 方式一：冲突时更新
ON CONFLICT (sku) DO UPDATE SET stock = EXCLUDED.stock;

-- 方式二：冲突时静默跳过（常用于导入去重）
INSERT INTO demo_stock (sku, name, price)
VALUES ('SKU-2002', '无线鼠标', 129.00)
ON CONFLICT (sku) DO NOTHING;

EXCLUDED 是一个特殊别名，指代"本次因冲突而未能插入的候选行"。此外，DO UPDATE 的 WHERE 可以进一步筛选哪些冲突才需要更新：

sql
-- 仅当新价格更低时才更新，避免无意义写入
INSERT INTO demo_stock (sku, name, price, stock)
VALUES ('SKU-2003', 'USB-C 扩展坞', 179.00, 100)
ON CONFLICT (sku) DO UPDATE
  SET price      = EXCLUDED.price,
      updated_at = now()
WHERE demo_stock.price > EXCLUDED.price      -- 注意：此处用表名限定旧值
RETURNING sku, price;

区分本次操作到底是插入还是更新，是幂等同步中的常见需求：

sql
-- 技巧：用系统列 xmax 判断。新插入的行 xmax = 0，被更新的行 xmax <> 0
INSERT INTO demo_stock (sku, name, price, stock)
VALUES ('SKU-2001', '机械键盘 Pro', 379.00, 90)
ON CONFLICT (sku) DO UPDATE SET stock = EXCLUDED.stock
RETURNING sku, (xmax = 0) AS inserted;      -- true 表示新插入，false 表示命中更新

实战示例 ​

下面用一段完整流程演示 CRUD 的配合使用。假设 demo_stock 表已建好，我们来模拟"上架 → 调价 → 售出 → 下架"的生命周期。

sql
-- 步骤 1：清理并准备数据
TRUNCATE TABLE demo_stock;
INSERT INTO demo_stock (sku, name, price, stock, tags) VALUES
  ('SKU-2001', '机械键盘',    349.00, 120, '{peripherals}'),
  ('SKU-2002', '无线鼠标',    129.00, 300, '{peripherals, wireless}'),
  ('SKU-2003', 'USB-C 扩展坞', 199.00,  80, '{accessories}');
text
-- 预期输出
INSERT 0 3
sql
-- 步骤 2：查询现有商品（Read）
SELECT sku, name, price, stock FROM demo_stock ORDER BY sku;
text
 sku      | name         | price  | stock
----------+--------------+--------+-------
 SKU-2001 | 机械键盘      | 349.00 |   120
 SKU-2002 | 无线鼠标      | 129.00 |   300
 SKU-2003 | USB-C 扩展坞  | 199.00 |    80
(3 rows)
sql
-- 步骤 3：新增一个商品，并直接拿到生成的值（Create）
INSERT INTO demo_stock (sku, name, price, stock, tags)
VALUES ('SKU-2004', '显示器支架', 259.00, 45, '{accessories, ergonomic}')
RETURNING sku, name, stock, updated_at;
text
 sku      | name       | stock |          updated_at
----------+------------+-------+-------------------------------
 SKU-2004 | 显示器支架  |    45 | 2026-09-21 10:12:33.482+08
(1 row)
sql
-- 步骤 4：调价 + 补货（Update），返回更新后的完整行
UPDATE demo_stock
SET price      = price * 0.9,        -- 打九折
    stock      = stock + 30,
    updated_at = now()
WHERE sku = 'SKU-2001'
RETURNING sku, name, price, stock;
text
 sku      | name     | price  | stock
----------+----------+--------+-------
 SKU-2001 | 机械键盘  | 314.10 |   150
(1 row)

UPDATE 1
sql
-- 步骤 5：批量 UPSERT，模拟每天同步上游价格与库存（幂等）
INSERT INTO demo_stock (sku, name, price, stock) VALUES
  ('SKU-2001', '机械键盘',    319.00, 150),
  ('SKU-2002', '无线鼠标',    119.00, 260),
  ('SKU-2005', 'HDMI 线缆',    39.00, 500)
ON CONFLICT (sku) DO UPDATE
  SET price      = EXCLUDED.price,
      stock      = EXCLUDED.stock,
      name       = EXCLUDED.name,
      tags       = demo_stock.tags,      -- 保留原有标签，不被覆盖
      updated_at = now()
RETURNING sku, name, price, stock, (xmax = 0) AS is_new;
text
 sku      | name       | price  | stock | is_new
----------+------------+--------+-------+--------
 SKU-2001 | 机械键盘    | 319.00 |   150 | f
 SKU-2002 | 无线鼠标    | 119.00 |   260 | f
 SKU-2005 | HDMI 线缆   |  39.00 |   500 | t
(3 rows)
sql
-- 步骤 6：售出商品，扣减库存（带条件的 Update，防超卖）
UPDATE demo_stock
SET stock = stock - 5
WHERE sku = 'SKU-2002'
  AND stock >= 5                 -- 关键：条件写在 WHERE 里，保证不出现负库存
RETURNING sku, stock;
text
 sku      | stock
----------+-------
 SKU-2002 |   255
(1 row)

UPDATE 1
sql
-- 步骤 7：下架缺货商品（Delete），返回被删除的内容用于记录
DELETE FROM demo_stock
WHERE stock = 0
RETURNING sku, name;
text
 sku | name
-----+------
(0 rows)

DELETE 0
sql
-- 步骤 8：最终核对库存总览（聚合查询）
SELECT count(*) AS sku_count, sum(stock) AS total_stock FROM demo_stock;
text
 sku_count | total_stock
-----------+-------------
         5 |        1030
(1 row)

完整流程中，一条 UPDATE 返回 UPDATE 0 并不代表出错，而代表"没有满足条件的行"。把影响行数当作业务断言使用，是避免"静默失败"的有效手段：

sql
-- 在应用层把影响行数当作断言：期望 1 行，若不是则说明业务状态不符合预期
UPDATE demo_stock SET stock = stock - 5 WHERE sku = 'SKU-2002' AND stock >= 5;
-- 应用层检查：若返回 UPDATE 0，则应抛出"库存不足"业务异常，而不是继续下单

注意事项 ​

  • UPDATE/DELETE 忘记 WHERE 会操作全表。 这是生产事故的高频原因。务必先用 SELECT count(*) 验证，并在必要时开启事务，确认后再提交。
sql
-- 错误！会删除全表数据
DELETE FROM demo_stock;

-- 正确：先确认范围，再删除
SELECT count(*) FROM demo_stock WHERE stock = 0;
DELETE FROM demo_stock WHERE stock = 0;
  • = NULL 永远不成立。 判断 NULL 必须用 IS NULL / IS NOT NULL。WHERE sku = NULL 的结果集恒为空。
sql
-- 错误！结果永远是 0 行
SELECT * FROM demo_stock WHERE tags = NULL;

-- 正确
SELECT * FROM demo_stock WHERE tags IS NULL;
  • UPDATE 是"写新版本 + 标旧版本"，不是原地修改。 因此高频更新的大表会产生表膨胀（Bloat），需要依赖 autovacuum 回收。不要在大表上做无意义的全表更新（例如无条件 SET updated_at = now()）。
sql
-- 错误！这会把全表每一行都写一次新版本，产生大量死元组
UPDATE demo_stock SET updated_at = now();

-- 正确：只更新真正需要变更的行
UPDATE demo_stock SET updated_at = now() WHERE sku = 'SKU-2001';
  • ON CONFLICT 必须能找到唯一索引。 括号里写的列或表达式必须对应一个唯一索引/唯一约束，否则会报 there is no unique or exclusion constraint matching the ON CONFLICT specification。
sql
-- 错误！demo_stock 上只有 sku 是主键，name 上没有唯一索引
INSERT INTO demo_stock (sku, name, price)
VALUES ('SKU-2006', '机械键盘', 349.00)
ON CONFLICT (name) DO NOTHING;
  • ON CONFLICT DO UPDATE 在高并发下可能报序列化错误。 当两个事务同时插入同一冲突键时，可能抛出 could not serialize access due to concurrent update。需要在应用层捕获并重试。
  • DELETE 不会释放磁盘空间。 删除后的空间由 autovacuum 标记为可复用，但文件不会自动缩小。需要收缩时使用 VACUUM FULL（会持有排他锁）或 pg_repack 扩展。
  • TRUNCATE 需要更强的锁。 它会对表加 ACCESS EXCLUSIVE 锁，阻塞所有读写，且若被其他表外键引用则需要 CASCADE。线上清表慎用。
  • SELECT * 在有生成列/大字段的表上会拖慢查询。 生产代码应显式列出所需列。

最佳实践 ​

  1. 所有修改类语句都带 RETURNING。 一次往返完成"写入 + 读取"，既减少网络开销，也让操作结果可被直接验证。这是 PostgreSQL 相对 MySQL 的重要优势。
  2. 用影响行数做业务断言。 UPDATE/DELETE 后检查返回行数，0 应视为异常路径处理，而非成功。
  3. 批量写入优先用多行 VALUES 或 COPY。 循环内的单行 INSERT 是最常见的性能瓶颈。大批量导入使用 COPY 比 INSERT 快一个数量级。
  4. 幂等同步统一用 ON CONFLICT。 定时任务、消息重投、断点续传等场景，用 UPSERT 替代"先查再决定插还是改"，可规避竞态条件。
  5. 条件写进 WHERE，不要写进应用逻辑。 例如扣减库存写成 WHERE stock >= n，让数据库在行锁内完成判断，天然防止超卖。
  6. 大批量 UPDATE/DELETE 分批执行。 每次处理 1000~10000 行并提交，避免长事务持有大量行锁、阻塞 autovacuum、造成主从复制延迟。
sql
-- 分批删除示例（PostgreSQL 13+：使用 CTE + LIMIT）
WITH victim AS (
  SELECT ctid FROM demo_stock WHERE stock = 0 LIMIT 1000
)
DELETE FROM demo_stock s
USING victim v
WHERE s.ctid = v.ctid
RETURNING s.sku;
  1. 先 SELECT 后修改，并用事务包裹。 生产环境的 DML 尽量显式 BEGIN / COMMIT，给自己留一次 ROLLBACK 的机会。
sql
BEGIN;
  UPDATE demo_stock SET price = price * 1.1 WHERE tags @> ARRAY['peripherals'];
  -- 检查影响行数与数据是否符合预期
  SELECT sku, name, price FROM demo_stock WHERE tags @> ARRAY['peripherals'];
  -- 确认无误后提交；否则 ROLLBACK;
COMMIT;
  1. 为 WHERE 条件列建索引。 CRUD 的查询与修改性能强依赖索引。常用作条件的列（外键、状态、时间）应有合适的索引。
sql
-- 为经常作为过滤条件的列建索引
CREATE INDEX idx_demo_stock_stock ON demo_stock (stock);
CREATE INDEX idx_demo_stock_tags  ON demo_stock USING gin (tags);

下一节

继续学习：条件查询与排序

参考链接 ​