数据库操作规范（学习笔记）
本文档基于行业最佳实践，覆盖数据访问层与事务管理的核心规范。
适用于所有涉及数据持久化的后端开发场景。
1. 数据访问层规范
1.1 技术选型边界
ORM 框架（如 MyBatis-Plus）与原生 SQL 并非互斥关系，应根据场景选择最合适的方案：
| 场景 | 推荐方案 | 原因 |
|---|---|---|
| 单表 CRUD | ORM 框架 API（Lambda Query/Update） | 类型安全，自动处理逻辑删除、租户过滤等横切关注点 |
| 多表关联 | 原生 SQL（注解或 XML） | JOIN、子查询、NOT EXISTS 等 ORM 无法优雅表达 |
| 动态分页 | 框架分页插件 + 统一分页请求体 | 避免手动拼接 LIMIT/OFFSET |
| 批量操作 | 框架批量方法 | 分批提交，避免单次 SQL 过长 |
核心原则：ORM 是单表神器，多表关联老老实实写 SQL。两者互补，不是替代关系。
1.2 Lambda 查询规范（单表首选）
优先使用类型安全的 Lambda API，避免硬编码字段名字符串：
// ✅ 推荐：类型安全，字段改名时编译报错mapper.lambdaQuery().eq(Entity::getCode,code).ne(Entity::getId,id).count();// ❌ 避免：硬编码字段名，重构时容易遗漏mapper.selectCount(newQueryWrapper<Entity>().eq("code",code));动态条件拼接：利用第一个 boolean 参数控制是否拼接，避免繁琐的 if-else：
mapper.lambdaQuery().eq(StringUtils.hasText(req.getCode()),Entity::getCode,req.getCode()).eq(req.getEnabled()!=null,Entity::getEnabled,req.getEnabled()).list();嵌套 OR 条件：当需要(A AND B) OR (C AND D)结构时，使用nested嵌套：
mapper.query().and(outer->{for(inti=0;i<conditions.size();i++){if(i>0)outer.or();outer.nested(n->n.eq("field_a",conditions.get(i).getA()).eq("field_b",conditions.get(i).getB()));}}).list();1.3 原生 SQL 规范（多表关联）
当涉及 JOIN、子查询等 ORM 无法表达的操作时，使用原生 SQL：
注解方式（适合简单、参数固定的查询）：
@Select(""" SELECT t1.id, t1.name, t2.code FROM table_a t1 LEFT JOIN table_b t2 ON t1.id = t2.a_id WHERE t1.status = #{status} AND t1.deleted = 0 AND t2.deleted = 0 ORDER BY t1.seq """)List<ResultDto>queryWithJoin(Longstatus);XML 方式（适合复杂、动态条件的查询）：
<selectid="queryWithCondition"resultType="ResultDto">SELECT t1.id, t1.name FROM table_a t1<where>t1.deleted = 0<iftest="name != null and name != ''">AND t1.name LIKE CONCAT('%', #{name}, '%')</if><iftest="status != null">AND t1.status = #{status}</if></where></select>强制规则：
- 原生 SQL 不经过 ORM 的自动逻辑删除，每张表必须手动加
deleted = 0 - 使用参数占位符（
#{param}），禁止拼接 SQL 字符串 - SQL 较长时使用 Text Block（
"""）或 XML 保持可读性 - 明确列出需要的字段，禁止
SELECT *
1.4 SQL 放置位置决策指南
当确定需要写原生 SQL 后，还有一个选择：注解（@Select）还是 XML？
决策矩阵
需要写 SQL？ ├── 参数固定、SQL ≤ 20 行 → @Select 注解 + Text Block ├── 需要动态条件（<if>、<choose>）→ XML Mapper ├── 需要复用 SQL 片段（<sql>）→ XML Mapper ├── 多数据库适配（不同方言）→ XML Mapper + databaseId └── 单表操作 → 不需要 SQL，用 ORM Lambda API三种方式对比
| 维度 | ORM Lambda API | @Select 注解 | XML Mapper |
|---|---|---|---|
| 适用场景 | 单表 CRUD | 简单多表查询 | 复杂/动态查询 |
| 动态条件 | 天然支持 | 需<script>包裹，很丑 | 原生支持<if>/<choose> |
| SQL 复用 | 不支持 | 不支持 | <sql>片段复用 |
| 可读性 | 高（链式调用） | 中（Text Block） | 高（语法高亮） |
| IDE 支持 | 无 SQL 提示 | 部分 IDE 支持 | SQL 插件完整支持 |
| 多数据库适配 | 框架自动处理 | 不支持方言切换 | databaseId原生支持 |
| 维护成本 | 低 | 中 | 中（多一个文件） |
实际建议
- 项目初期 / 小团队：
@Select注解足够，减少文件数量，SQL 和方法挨着看 - SQL 超过 30 行或需要动态条件：迁移到 XML，不要硬撑在注解里
- 需要多数据库兼容：必须用 XML（见 1.5 节）
- 同一 SQL 被多个方法复用：用 XML 的
<sql>片段提取
1.5 多数据库适配规范
当系统需要同时支持多种数据库（如 MySQL + 达梦 + PostgreSQL）时，需要从DDL、SQL 方言、驱动配置三个层面处理。
1.5.1 核心策略：ORM 屏蔽 + 方言隔离
┌─────────────────────────────────────────┐ │ Service / Business │ ├─────────────────────────────────────────┤ │ 单表操作 → ORM Lambda API（无方言差异） │ │ 多表操作 → XML Mapper + databaseId │ ├──────────┬──────────┬───────────────────┤ │ MySQL │ 达梦 │ PostgreSQL ... │ └──────────┴──────────┴───────────────────┘核心思路：
- 单表操作：ORM 框架自动处理方言差异（分页、转义等），开发者无需关心
- 多表 SQL：通过 XML 的
databaseId为不同数据库写不同版本的 SQL - DDL：各数据库独立维护建表脚本，不试图写"通用 DDL"
1.5.2 MyBatis databaseId 机制
配置MybatisPlusConfig或MybatisSqlSessionFactoryBean注册DatabaseIdProvider：
@BeanpublicDatabaseIdProviderdatabaseIdProvider(){VendorDatabaseIdProviderprovider=newVendorDatabaseIdProvider();Propertiesprops=newProperties();props.setProperty("MySQL","mysql");props.setProperty("DM DBMS","dm");// 达梦props.setProperty("PostgreSQL","pg");provider.setProperties(props);returnprovider;}XML 中使用databaseId区分方言：
<!-- MySQL 版本 --><selectid="pageQuery"databaseId="mysql"resultType="ResultDto">SELECT id, name FROM tb_xxx WHERE deleted = 0 LIMIT #{offset}, #{size}</select><!-- 达梦版本 --><selectid="pageQuery"databaseId="dm"resultType="ResultDto">SELECT id, name FROM tb_xxx WHERE deleted = 0 LIMIT #{size} OFFSET #{offset}</select><!-- 无 databaseId 的版本（兜底，当 databaseId 不匹配时使用） --><selectid="pageQuery"resultType="ResultDto">SELECT id, name FROM tb_xxx WHERE deleted = 0</select>匹配优先级：databaseId="mysql"> 无 databaseId（兜底）
1.5.3 常见方言差异与处理
| 差异点 | MySQL | 达梦 | PostgreSQL |
|---|---|---|---|
| 分页 | LIMIT offset, size | LIMIT size OFFSET offset | LIMIT size OFFSET offset |
| 字符串拼接 | CONCAT(a, b) | CONCAT(a, b)或a || b | a || b |
| 日期函数 | NOW() | SYSDATE | NOW() |
| 自增主键 | AUTO_INCREMENT | IDENTITY(1,1) | SERIAL |
| 布尔类型 | TINYINT(1) | BIT | BOOLEAN |
| 反引号/引号 | `column` | "column" | "column" |
| LIKE 通配符 | %/_ | %/_ | %/_ |
处理原则：
- 能用 SQL 标准的语法（如
LIMIT size OFFSET offset、CONCAT()），各数据库通用，不需要分版本 - 实在无法统一的（如日期函数、自增主键），用
databaseId分版本写 - 分页推荐直接用 ORM 框架的分页插件，自动适配方言
1.5.4 DDL 多数据库管理
不要试图写一套通用 DDL，各数据库独立维护：
resources/ └── db/ ├── mysql/ │ └── schema.sql -- MySQL 建表脚本 ├── dm/ │ └── schema.sql -- 达梦建表脚本 └── postgresql/ └── schema.sql -- PostgreSQL 建表脚本DDL 差异示例：
-- MySQLCREATETABLEtb_xxx(idBIGINTNOTNULLAUTO_INCREMENT,nameVARCHAR(128)NOTNULL,enabledTINYINTDEFAULT1,create_timeDATETIMEDEFAULTNULL,PRIMARYKEY(id))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;-- 达梦CREATETABLEtb_xxx(idBIGINTNOTNULLIDENTITY(1,1),nameVARCHAR(128)NOTNULL,enabledBITDEFAULT1,create_timeTIMESTAMPDEFAULTNULL,PRIMARYKEY(id));1.5.5 多数据库最佳实践清单
| 实践 | 说明 |
|---|---|
| ORM 优先 | 单表操作全部走 ORM，天然屏蔽方言差异 |
| SQL 标准化 | 多表 SQL 尽量用标准语法，减少databaseId分支 |
| 独立 DDL | 各数据库独立维护建表脚本，不写"通用 DDL" |
| CI 双跑 | 集成测试至少在两种数据库上各跑一遍 |
| 方言常量 | 通过databaseId或配置项获取当前数据库类型，业务代码中不硬编码方言判断 |
| 避免方言函数 | 如IFNULL()(MySQL) vsNVL()(Oracle/达梦)，统一用COALESCE()(SQL 标准) |
| 连接池配置 | 各数据库使用对应的 Driver 和连接池配置，通过 Spring Profile 切换 |
1.5.6 Spring Profile 切换数据源
# application-mysql.ymlspring:datasource:driver-class-name:com.mysql.cj.jdbc.Driverurl:jdbc:mysql://localhost:3306/mydb?useUnicode=true# application-dm.ymlspring:datasource:driver-class-name:dm.jdbc.driver.DmDriverurl:jdbc:dm://localhost:5236/mydb启动时通过--spring.profiles.active=mysql或dm切换，无需改代码。
1.6 分页查询规范
- 列表查询必须分页，禁止全表加载后内存过滤
- 使用框架提供的统一分页机制，避免手动拼接 LIMIT
- 分页结果统一转换为 DTO，不直接暴露 Entity
// 标准分页查询publicPageResult<XxxDto>page(PageRequestreq){Page<Entity>page=mapper.selectPage(toPage(req),buildQuery(req));returntoPageResult(page,XxxDto.class);}1.7 批量操作规范
| 规则 | 说明 |
|---|---|
| 使用批量 API | insertBatch()/updateBatch()，避免循环单条操作 |
| 控制批次大小 | 默认 1000 条/批，大数据量可自定义（如 500） |
| 事务内分批提交 | 避免单次 SQL 过长导致锁表或内存溢出 |
// ✅ 批量插入mapper.insertBatch(entityList,500);// ❌ 循环单条插入for(Entitye:entityList){mapper.insert(e);// N 次 DB 交互，性能差}2. 事务管理规范
2.1 事务注解策略
| 操作类型 | 注解 | 说明 |
|---|---|---|
| 读操作（查询/分页） | @Transactional(readOnly = true) | 只读事务，数据库可优化（如不获取写锁、跳过 redo log） |
| 写操作（增/删/改） | @Transactional | 默认回滚 RuntimeException |
| 复杂写操作 | @Transactional(rollbackFor = Exception.class) | 显式声明回滚所有异常，防止 checked exception 绕过回滚 |
2.2 核心规则
规则一：事务只加在 Service 层
// ✅ 正确：事务在 Service 层@ServicepublicclassXxxService{@Transactionalpublicvoidadd(XxxDtodto){...}}// ❌ 错误：事务不应加在 Controller 或 Mapper 层@RestControllerpublicclassXxxController{@Transactional// 不该在这里！publicR<String>add(@RequestBodyXxxDtodto){...}}规则二：写操作异常必须 throw，不能用 return
// ✅ 正确：抛异常 → 事务回滚@Transactionalpublicvoidadd(XxxDtodto){if(duplicate){thrownewBusinessException("编码已存在");// 事务正确回滚}mapper.insert(entity);}// ❌ 错误：return 错误码 → 事务不回滚，半截数据落库@TransactionalpublicStringadd(XxxDtodto){if(duplicate){return"编码已存在";// 事务不会回滚！前面的写操作已落库}mapper.insert(entity);return"success";}规则三：禁止在 Controller 层 try-catch 吞掉异常
异常应自然冒泡到全局异常处理器统一处理，Controller 层捕获异常会导致事务失效和错误处理不一致。
规则四：事务方法内不做耗时操作
// ✅ 正确：事务只包裹数据库操作@Transactionalpublicvoidadd(XxxDtodto){mapper.insert(entity);mapper.insertBatch(items);}// 事务外的耗时操作sendMqMessage(event);uploadFile(file);// ❌ 错误：事务内包含远程调用和文件 I/O@Transactionalpublicvoidadd(XxxDtodto){mapper.insert(entity);remoteService.call();// 长事务！锁持有时间过长fileStorage.upload();// 长事务！mapper.insertBatch(items);}2.3 事务失效场景
| 场景 | 原因 | 解决方案 |
|---|---|---|
| 同类方法调用 | this.methodA()调用@Transactional的methodB()，绕过代理 | 注入自身代理，或拆分到不同 Service |
| 异常被 catch | 异常在事务方法内被捕获，Spring 感知不到异常 | 不要 catch，或 catch 后重新 throw |
| 非 public 方法 | Spring AOP 只代理 public 方法 | 事务方法必须为 public |
| 异常类型不匹配 | 默认只回滚 RuntimeException，checked exception 不回滚 | 加rollbackFor = Exception.class |
| 数据库不支持事务 | 如 MySQL MyISAM 引擎 | 统一使用 InnoDB |
2.4 乐观锁
并发更新场景必须使用乐观锁，防止"丢失更新"问题：
// Entity 字段@VersionprivateIntegerversion;// 更新时检查返回值introws=mapper.updateById(entity);if(rows==0){thrownewBusinessException("数据已被他人修改，请刷新后重试");}原理：UPDATE 语句自动追加AND version = #{version}，若返回 0 行说明数据已被他人修改。
2.5 事务传播行为（了解）
| 传播行为 | 含义 | 使用频率 |
|---|---|---|
REQUIRED（默认） | 有事务则加入，无则新建 | 最常用 |
REQUIRES_NEW | 总是新建事务，挂起当前事务 | 日志记录（不论主事务成功失败都要落库） |
SUPPORTS | 有事务则加入，无则非事务执行 | 只读查询 |
NOT_SUPPORTED | 总是非事务执行 | 极少使用 |
NESTED | 嵌套事务，内层回滚不影响外层 | 谨慎使用 |
99% 的场景使用默认的
REQUIRED即可，不要过度设计事务传播。
3. 安全规范
3.1 逻辑删除
- 所有删除操作使用逻辑删除（
deleted字段），禁止物理 DELETE - ORM 框架自动追加
AND deleted = 0（自定义 SQL 需手动添加） deleted字段：0= 未删除，1= 已删除
3.2 防注入
- 使用 ORM 的 Wrapper API 或
#{param}占位符 - 禁止拼接 SQL 字符串（如
"WHERE name = '" + name + "'"） - 分页排序的字段名需白名单校验，不接受前端传入原始 SQL 片段
3.3 数据隔离
- 多租户场景：查询时自动追加租户条件，自定义 SQL 需手动添加
- 权限隔离：敏感数据查询需校验当前用户是否有权访问
4. 性能规范
4.1 查询优化
| 规则 | 说明 |
|---|---|
避免SELECT * | 只查需要的字段，减少网络传输和内存占用 |
| 分页必加 | 列表查询必须分页，禁止全表查询后内存过滤 |
| 索引覆盖 | 高频查询条件字段必须建索引 |
| 避免 N+1 | 关联数据用 JOIN 一次查出，禁止循环内逐条查询 |
| 区分度 | 区分度低的字段（如enabled）不单独建索引 |
4.2 写入优化
| 规则 | 说明 |
|---|---|
| 批量写入 | 多条插入使用批量 API，避免循环单条 insert |
| 控制批次 | 大批量数据分批处理（建议 500~1000 条/批） |
| 索引数量 | 单表索引不超过 5 个，避免写入时索引维护开销过大 |
4.3 连接与资源
- 不在代码中手动管理数据库连接（由连接池托管）
- 事务方法只包含数据库操作，远程调用、文件 I/O 放在事务外
- 长事务必须避免：持有锁时间过长会导致其他请求阻塞
