数据库设计规范

发表于 2026-06-09 更新于 2026-06-12 分类于 backend

涵盖表设计、字段规范、索引规范、SQL 编写与 MyBatis 使用的数据库通用开发规范

前言

数据库设计是后端开发的基础，规范的表结构和 SQL 编写能显著提升系统性能和可维护性。
本文整理一套简洁通用的数据库开发规范，涵盖表设计、字段规范、索引规范、SQL 编写与 MyBatis 使用。
适用于 MySQL 8.0+ 数据库，其他关系型数据库可按需调整。

表结构

数据表变更保留变更迁移 SQL，放在 sql 文件夹，以库名+version 命名。

表设计

表名使用 snake_case，如 sys_user、order_detail。
表名使用单数形式，避免 users 这种复数写法。
必须包含 id 主键字段，使用 BIGINT 类型自增。
公共字段统一包含 create_time、update_time、create_by、update_by。
逻辑删除字段统一命名为 deleted，值为 0 未删除、1 已删除。
避免使用 level、sort、tid、order 等关键字作为字段名。
字段必须添加注释，说明含义和取值范围。

CREATE TABLE sys_user (
    id          BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    user_name   VARCHAR(50)  NOT NULL                COMMENT '用户名',
    email       VARCHAR(100) DEFAULT NULL            COMMENT '邮箱',
    status      TINYINT      NOT NULL DEFAULT 1      COMMENT '状态：0-禁用 1-启用',
    deleted     TINYINT      NOT NULL DEFAULT 0      COMMENT '是否删除：0-否 1-是',
    create_time DATETIME     NOT NULL                COMMENT '创建时间',
    update_time DATETIME     NOT NULL                COMMENT '更新时间',
    create_by   VARCHAR(50)  DEFAULT NULL            COMMENT '创建人',
    update_by   VARCHAR(50)  DEFAULT NULL            COMMENT '更新人',
    PRIMARY KEY (id),
    UNIQUE KEY uk_user_name (user_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

字段规范

类型	推荐写法	说明
主键	`BIGINT AUTO_INCREMENT`	不使用 UUID 作主键
字符串	`VARCHAR(n)`	按实际需要设长度，不要一律 255
布尔	`TINYINT`	0 表示否，1 表示是
时间	`DATETIME`	不使用 `TIMESTAMP`（存在 2038 问题）
金额	`Long`	单位为分
大文本	`TEXT`	超长内容单独字段或使用 OSS

字段尽量设为 NOT NULL，并提供合理默认值。
单表字段数建议不超过 20 个，超过时考虑拆表。

索引规范

主键索引命名为 pk_表名，唯一索引命名为 uk_字段，普通索引命名为 idx_字段。
单表索引数量不超过 5 个，联合索引字段数不超过 5 个。
遵循最左前缀原则设计联合索引。
区分度低的字段（如 status）不要单独建索引。
频繁更新的字段不要建索引，影响写入性能。

-- 唯一索引
ALTER TABLE sys_user ADD UNIQUE INDEX uk_user_name (user_name);

-- 普通索引
ALTER TABLE sys_user ADD INDEX idx_status (status);

-- 联合索引（遵循最左前缀）
ALTER TABLE order_detail ADD INDEX idx_user_status (user_id, status);

SQL 编写

禁止使用 SELECT *，必须明确列出查询字段。
禁止在 WHERE 条件中对字段使用函数，导致索引失效。
使用 LIMIT 限制返回行数，避免全表扫描。
批量操作单次不超过 500 条，超过时分批处理。
INSERT 语句必须指定字段列表。

-- 推荐
SELECT id, user_name, email, status
FROM sys_user
WHERE status = 1
ORDER BY create_time DESC
LIMIT 20;

-- 不推荐：SELECT *
SELECT * FROM sys_user WHERE status = 1;

-- 不推荐：字段上使用函数
SELECT * FROM sys_user WHERE DATE(create_time) = '2026-06-09';

-- 推荐：用范围查询替代
SELECT id, user_name FROM sys_user
WHERE create_time >= '2026-06-09 00:00:00'
  AND create_time <  '2026-06-10 00:00:00';

MyBatis 规范

Mapper 接口以 Mapper 结尾，XML 文件与接口包路径保持一致。
XML 中的 id 与 Mapper 方法名必须一一对应。
使用 #{} 预编译参数，禁止使用 ${} 拼接 SQL（防 SQL 注入）。
动态 SQL 使用 <if>、<where>、<foreach> 等标签，不要在 Java 代码中拼接 SQL 字符串。
返回单条记录时使用 resultType，复杂映射使用 resultMap。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.project.mapper.UserMapper">

    <resultMap id="userMap" type="com.example.project.model.entity.User">
        <id     property="id"         column="id"/>
        <result property="userName"   column="user_name"/>
        <result property="email"      column="email"/>
        <result property="status"     column="status"/>
        <result property="createTime" column="create_time"/>
    </resultMap>

    <select id="selectByCondition" resultMap="userMap">
        SELECT id, user_name, email, status, create_time
        FROM sys_user
        WHERE deleted = 0
        <if test="userName != null and userName != ''">
            AND user_name LIKE CONCAT('%', #{userName}, '%')
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
        ORDER BY create_time DESC
    </select>

    <insert id="batchInsert">
        INSERT INTO sys_user (user_name, email, status, create_time)
        VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.userName}, #{item.email}, #{item.status}, #{item.createTime})
        </foreach>
    </insert>

</mapper>

事务管理

Service 层方法上使用 @Transactional 注解管理事务。
只读查询添加 @Transactional(readOnly = true)，提升性能。
事务方法必须由外部调用才能生效，同类内部调用不走代理。
避免在事务中执行耗时操作（如 HTTP 调用、文件上传），减少锁持有时间。

@Service
public class OrderServiceImpl implements OrderService {

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void createOrder(OrderDTO dto) {
        // 1. 扣减库存
        inventoryMapper.decreaseStock(dto.getProductId(), dto.getQuantity());
        // 2. 创建订单
        Order order = convertToEntity(dto);
        orderMapper.insert(order);
    }

    @Override
    @Transactional(readOnly = true)
    public OrderVO getOrderById(Long id) {
        Order order = orderMapper.selectById(id);
        return convertToVO(order);
    }
}

分页查询

使用分页插件统一处理，不在 SQL 中手动拼接 LIMIT。

// Controller 层
@GetMapping("/list")
public Result<IPage<UserVO>> list(UserQueryDTO query) {
    Page<User> page = new Page<>(query.getPageNum(), query.getPageSize());
    IPage<UserVO> result = userService.pageQuery(page, query);
    return Result.success(result);
}

// Service 层
@Override
@Transactional(readOnly = true)
public IPage<UserVO> pageQuery(Page<User> page, UserQueryDTO query) {
    return userMapper.selectByCondition(page, query);
}

总结

规范的核心是一致性，团队成员遵守同一套约定，数据库设计才能长期可维护。
建议将本文规范纳入 SQL Review 流程，自动检查代替人工审查。
具体条目可根据团队实际情况调整，关键是达成共识并执行到位。