切换日光/暗黑模式
072. SQL 构建器：新增、更新、删除与查询
学习目标
这一节开始拆通用模块的 SQL 构建器。
学完后，你应该能理解：
- 通用模块为什么要生成 SQL；
- 新增、更新、删除 SQL 的基本结构；
- 占位符和参数化查询怎么保护安全；
- 查询 SQL 为什么最复杂；
- 去重、格式化、筛选和模糊查询如何落到 SQL。
四类 SQL 构建器
通用模块主要需要四类 SQL：
- 新增 SQL；
- 更新 SQL；
- 删除 SQL；
- 查询 SQL。
前三类相对简单。
查询最复杂。
因为查询要处理字段、关联、筛选、排序、分页、统计和去重。
新增 SQL
新增 SQL 的基本结构是：
sql
INSERT INTO table_name (field_a, field_b)
VALUES (%s, %s)构建时要确定三件事：
- 表名；
- 字段名；
- 字段值。
字段值不要直接拼进 SQL 字符串。
应该用占位符，再把真实值放进参数列表。
字段命名映射
前端传的是驼峰命名。
数据库字段是下划线命名。
所以 SQL 构建前需要字段格式化。
例如：
txt
leaderId -> leader_id通用模块会把字段配置整理成可用结构。
这样生成 SQL 时能知道：
- 前端字段名是什么；
- 数据库字段名是什么；
- SQL 里应该写哪个字段表达式。
主表字段和关联字段
有些字段来自主表。
有些字段来自关联表或子查询。
例如项目表里的负责人名称，可能来自用户表。
这种字段可以查询，但不能直接插入或更新到项目主表。
所以新增和更新时，只处理主表字段。
关联字段、统计字段要跳过。
占位符
参数化查询需要占位符。
不同语言和数据库驱动的占位符不完全一样。
有的使用：
sql
?有的使用：
sql
%s课程项目里使用 %s。
SQL 字符串里放占位符。
真实参数单独传给数据库驱动。
更新 SQL
更新 SQL 的基本结构是：
sql
UPDATE table_name
SET field_a = %s, field_b = %s
WHERE id = %s注意 SET 后面的多个字段用逗号分隔。
不是用 AND。
参数数量要和占位符数量一致。
用于调试的 SQL 字符串和真正执行的 SQL 可能不一样。
真正执行时仍然要走参数化。
数据库类型转换
有时传入值是字符串，但数据库字段是数字。
数据库可能会尝试隐式转换。
例如把 "1" 转成整数 1。
但不要依赖这种行为处理所有情况。
通用模块最好在字段类型层面提前转换和校验。
这样错误更早、更明确。
事务和脏数据
一次执行多条 SQL 时需要事务。
如果某一条失败，前面已经执行的 SQL 应该回滚。
脏数据的来源很多。
例如：
- 手动执行 SQL 改错数据；
- 前端页面字段没处理对；
- 后端没有校验；
- 并发覆盖；
- 测试数据残留。
只要数据和业务预期不一致，就可以把它当成脏数据处理。
删除 SQL
删除通常只允许按 ID 删除。
单个 ID 可以用：
sql
WHERE id = %s批量 ID 可以用：
sql
WHERE id IN (...)删除条件不要开放得太自由。
否则很容易误删大范围数据。
查询 SQL 最复杂
查询构建器要处理更多场景。
例如：
- 字段格式化；
- 日期格式化；
- 关联表字段；
- 字段别名；
- 去重查询；
- 筛选表达式；
- 排序；
- 分页；
- 子查询；
- 聚合字段。
这也是通用模块最核心的一块。
日期格式化
数据库查出来的时间可能是日期对象。
但接口返回给前端时，通常希望是格式化后的字符串。
SQL 里可以使用数据库函数格式化日期。
例如把创建时间转成固定格式的字符串。
这样前端拿到的值更稳定。
关联字段别名
当查询多张表时，字段名可能冲突。
例如主表和用户表都有 name 字段。
这时要给字段加别名。
否则返回结果里会互相覆盖，或者数据库直接报字段歧义。
通用模块会根据表别名和字段配置生成更安全的字段名。
去重查询
有些字段适合做去重。
例如审批状态只有几种值。
可以用去重查询拿到所有出现过的状态。
这常用于前端筛选项。
比如先查出当前表里有哪些省份、哪些状态，再展示为筛选选项。
筛选查询
通用模块要支持多种筛选。
数字和日期常见筛选包括：
- 等于；
- 大于；
- 小于；
- 范围查询。
字符串常见筛选包括：
- 精确匹配；
- 模糊匹配；
- 多值模糊匹配。
模糊查询通常用 LIKE。
% 表示任意字符。
例如：
sql
LIKE '%abc%'表示只要中间包含 abc 就匹配。
直接 SQL 和 ORM 的选择
不是所有写操作都必须先查再改。
如果业务允许弱一致性，直接执行更新 SQL 就可以。
例如普通后台字段被后提交的人覆盖，业务可以接受。
但如果涉及状态判断，就需要先查再决定。
例如用户激活：
- 先查用户是否已经激活；
- 未激活才更新；
- 已激活就不重复处理。
所以项目里可以同时使用 ORM 和通用 SQL。
具体业务逻辑用 ORM 更清晰。
复杂查询和通用列表更适合通用 SQL 构建器。
这一节的重点
这一节开始进入通用模块核心。
你需要记住：
- 新增、更新、删除 SQL 结构简单，但必须参数化；
- 主表字段可以写入，关联字段和统计字段不能直接写入；
- 查询构建器最复杂，要处理字段、关联、筛选、排序和分页；
- 日期、别名、去重、模糊查询都要在 SQL 层设计清楚；
- ORM 和手写 SQL 可以并存，按业务复杂度选择。