一文看懂数据库表设计：从字段类型、主键索引到项目实战

前言在后端开发中数据库表设计是非常基础但也非常重要的一环。很多人刚开始做项目时容易把数据库表设计理解成“把字段列出来就行”。但实际项目中表设计会直接影响后续的查询性能、数据一致性、业务扩展和代码复杂度。比如一个很常见的问题手机号为什么一般用VARCHAR而不是用INT或BIGINT这个问题看起来是在问字段类型其实背后考察的是数据库建模思维字段到底是用来计算的还是用来标识和展示的这篇文章就从表设计的基本思路、字段类型选择、主键设计、索引设计、表关系设计和项目实战几个方面系统梳理数据库表应该怎么设计。一、数据库表设计的核心目标数据库表设计不是单纯建几张表而是要让数据结构能支撑业务长期运行。一个好的表设计通常要满足几个目标数据能准确表达业务 字段类型选择合理 主键和唯一约束清晰 查询性能可接受 后期扩展成本低 数据一致性有保障简单来说表设计要同时考虑业务正确性 查询性能 扩展能力 维护成本如果只为了当前功能随便建表后面业务一变就很容易出现字段含义混乱、重复数据多、索引不好加、接口难改等问题。二、设计数据库表的一般流程做表设计时不建议一上来就直接写 SQL。更合理的流程是1. 理解业务场景 2. 找出核心实体 3. 梳理实体之间的关系 4. 确定字段和字段含义 5. 选择合适的数据类型 6. 设计主键、唯一约束和索引 7. 补充通用字段 8. 根据查询场景优化表结构比如做一个优惠券系统不能直接开始写字段而是要先想清楚有哪些核心对象用户 优惠券 用户领取记录 优惠券订单 商户 活动然后再分析关系一个商户可以创建多个优惠券 一个用户可以领取多张优惠券 一张优惠券可以被多个用户领取 一个用户对同一张券可能只能领取一次这些关系确定后表结构才会比较清晰。三、字段类型怎么选字段类型设计是数据库表设计中最容易被忽略的部分。字段类型不是随便选的要根据字段的业务含义来决定。1. 手机号为什么用 VARCHAR手机号一般不建议用INT或BIGINT而是用VARCHAR。比如phone varchar(20) not null comment 手机号原因有几个。首先手机号不是用来做数学计算的。我们不会对手机号做加减乘除它本质上是一个身份标识而不是数值。其次手机号可能以0开头或者带国家区号。例如86 13800138000 00852 12345678如果用数字类型前导 0、加号、区号格式都不好处理。另外不同国家和地区的手机号长度不同用VARCHAR更方便扩展。所以可以简单记住只要字段是“标识类数据”即使内容看起来是数字也不一定要用数字类型。类似字段还有手机号 身份证号 订单号 银行卡号 邮政编码 学号 工号这些字段通常都更适合用VARCHAR。2. 金额为什么用 DECIMAL金额字段不要用FLOAT或DOUBLE应该用DECIMAL。例如amount decimal(10,2) not null comment 订单金额原因是FLOAT和DOUBLE是浮点数可能存在精度误差。比如金额计算中如果出现0.1 0.2 ! 0.3就可能导致账务问题。所以涉及金额、余额、支付、结算这类字段一般用DECIMAL或者在部分高性能场景中用整数保存分amount_cent bigint not null comment 金额单位分3. 状态字段怎么设计状态字段在业务系统里非常常见比如订单状态、优惠券状态、审批状态。可以这样设计status tinyint not null default 0 comment 状态0未支付1已支付2已取消也可以用字符串status varchar(20) not null comment 订单状态UNPAID/PAID/CANCELLED两种方式各有特点。tinyint的优点是存储小、查询快但可读性差一些。varchar的优点是可读性好和代码枚举更直观但占用空间稍大。实际项目中如果公司有统一规范就按规范来。如果是自己做项目状态值较少时用tinyint比较常见。关键是不要只写status int却不写清楚每个值代表什么。状态字段一定要有注释代码里最好也用枚举统一维护。4. 时间字段怎么设计常见时间字段有create_time datetime not null comment 创建时间, update_time datetime not null comment 更新时间如果业务需要记录删除时间也可以加delete_time datetime null comment 删除时间MySQL 中常用datetime或timestamp。一般业务系统中datetime使用更直观范围也更大。时间字段要注意统一规范比如创建时间 create_time 更新时间 update_time 支付时间 pay_time 取消时间 cancel_time 过期时间 expire_time不要一张表里既有created_at又有create_time命名风格混乱会影响维护。5. 文本字段怎么设计短文本可以用VARCHAR长文本可以用TEXT。比如title varchar(100) not null comment 标题, description varchar(500) default null comment 描述, content text comment 正文内容不要所有字符串都直接用TEXT。因为TEXT不适合频繁作为查询条件也不适合随意建索引。如果字段只是名称、标题、手机号、状态这类短文本用VARCHAR更合适。四、主键怎么设计每张业务表一般都需要一个主键。常见做法是id bigint primary key comment 主键ID主键设计要满足唯一 稳定 不频繁变化 适合关联查询在 Java 后端项目中常见主键方案有数据库自增 ID 雪花算法 ID UUID 业务单号1. 自增 ID自增 ID 简单好用适合单体项目或数据量不大的业务。id bigint auto_increment primary key优点简单 有序 索引性能好缺点分库分表时不方便 容易暴露数据量 多库生成 ID 会冲突2. 雪花算法 ID分布式系统中常用雪花算法生成全局唯一 ID。优点全局唯一 趋势递增 适合分布式系统缺点依赖机器号和时间 实现和维护成本比自增 ID 高很多分布式项目中订单 ID、业务 ID 会使用雪花算法。3. UUIDUUID 也能保证唯一但不太适合作为 MySQL 聚簇索引主键。原因是 UUID 通常无序插入时容易导致索引页频繁分裂影响性能。如果要用 UUID一般更适合作为外部展示 ID而不是数据库主键。五、唯一约束怎么设计唯一约束用于保证某些业务数据不能重复。比如用户表中手机号不能重复unique key uk_phone (phone)优惠券领取表中一个用户对同一张券只能领取一次unique key uk_user_coupon (user_id, coupon_id)唯一约束非常重要因为它是数据库层面的兜底。比如在抢券场景中即使用了 Redis 分布式锁也建议加唯一索引user_id coupon_id 唯一这样即使并发控制出现问题数据库也能防止重复写入。一句话总结分布式锁控制并发过程唯一索引保证最终结果。六、索引怎么设计索引不是越多越好而是要根据查询场景设计。设计索引前先看接口怎么查。比如订单表常见查询根据用户 ID 查询订单列表 根据订单号查询订单详情 根据订单状态筛选订单 根据创建时间排序可以设计unique key uk_order_no (order_no), key idx_user_id (user_id), key idx_status_create_time (status, create_time)1. 哪些字段适合建索引适合建索引的字段经常出现在 where 条件中的字段 经常用于 join 的字段 经常用于 order by 的字段 区分度较高的字段 唯一性约束字段比如user_id order_no phone coupon_id create_time2. 哪些字段不适合建索引不适合建索引的字段区分度很低的字段 很少查询的字段 频繁更新的字段 超长文本字段比如性别字段只有男、女、未知区分度很低单独建索引意义不大。3. 联合索引要考虑最左前缀比如建立联合索引key idx_user_status_time (user_id, status, create_time)它比较适合这些查询where user_id ? where user_id ? and status ? where user_id ? and status ? order by create_time但如果查询条件只有where status ?一般就用不上这个联合索引的完整能力。所以联合索引字段顺序不能随便放要根据查询条件的频率和区分度来设计。七、表关系怎么设计常见表关系有三种一对一 一对多 多对多1. 一对一比如用户表和用户详情表。user user_profile用户表保存登录相关核心字段用户详情表保存头像、简介、性别等扩展信息。这样可以避免主表字段过多。2. 一对多比如一个用户可以有多个订单。user order订单表中保存user_id作为外键逻辑字段。user_id bigint not null comment 用户ID实际互联网项目中不一定会使用数据库物理外键但会在业务层保证关联关系。3. 多对多比如用户和角色是多对多关系。一个用户可以有多个角色一个角色也可以分配给多个用户。通常需要一张中间表user role user_role中间表设计user_id bigint not null comment 用户ID, role_id bigint not null comment 角色ID, unique key uk_user_role (user_id, role_id)八、是否一定要遵守三范式数据库设计中经常会提到三范式。简单理解字段不可再拆 非主键字段依赖主键 不要出现过多传递依赖范式的作用是减少数据冗余保证数据一致性。但在实际项目中不是所有表都要严格追求范式。比如订单表中通常会冗余商品名称、商品快照价格。原因是商品名称后续可能会修改 但历史订单应该保留当时购买时的信息所以订单表中保存商品快照是合理的。可以这样理解核心数据设计尽量规范查询性能和历史快照场景可以适当冗余。九、通用字段怎么设计很多业务表都会有一些通用字段。比如create_time datetime not null comment 创建时间, update_time datetime not null comment 更新时间, create_by bigint default null comment 创建人, update_by bigint default null comment 更新人, deleted tinyint not null default 0 comment 逻辑删除0未删除1已删除如果系统需要审计还可以加version int not null default 0 comment 乐观锁版本号, tenant_id bigint default null comment 租户ID通用字段的作用是方便数据追踪 问题排查 权限隔离 逻辑删除 并发控制但也不要所有表都无脑加字段要结合业务需要。十、项目实战优惠券领取表设计以优惠券系统为例设计一张用户优惠券领取表。业务规则用户可以领取优惠券 一张优惠券可以被多个用户领取 同一个用户对同一张优惠券只能领取一次 领取后有使用状态 需要记录领取时间和使用时间表结构可以这样设计create table user_coupon ( id bigint primary key comment 主键ID, user_id bigint not null comment 用户ID, coupon_id bigint not null comment 优惠券ID, status tinyint not null default 0 comment 状态0未使用1已使用2已过期, receive_time datetime not null comment 领取时间, use_time datetime default null comment 使用时间, order_id bigint default null comment 使用该券的订单ID, create_time datetime not null comment 创建时间, update_time datetime not null comment 更新时间, deleted tinyint not null default 0 comment 逻辑删除0未删除1已删除, unique key uk_user_coupon (user_id, coupon_id), key idx_coupon_id (coupon_id), key idx_user_status (user_id, status) ) comment用户优惠券领取表;这里重点看几个设计点。user_id和coupon_id是核心关联字段用来表示谁领取了哪张券。status表示券当前状态方便查询未使用、已使用、已过期的券。unique key uk_user_coupon (user_id, coupon_id)用来保证一个用户不能重复领取同一张券。idx_user_status (user_id, status)适合查询用户未使用优惠券列表。这个表设计不是随便堆字段而是围绕业务规则来的。十一、数据库表设计常见问题1. 字段类型乱用比如手机号用int金额用double状态字段没有注释。这些问题短期能跑后期很容易出 bug。2. 没有唯一约束比如业务要求一个用户只能领取一次优惠券但数据库没有唯一索引。这样在高并发情况下很容易出现重复数据。3. 索引设计不结合查询很多人建表时随便加索引或者完全不加索引。正确做法是先看业务查询方式再决定索引。4. 表字段过多一张表字段太多会导致维护困难也可能影响查询性能。如果部分字段不是核心字段可以考虑拆成扩展表。5. 过度冗余或完全不冗余冗余太多会导致数据不一致。完全不冗余又可能导致查询复杂、性能差。实际项目中要结合业务权衡。十二、设计数据库表时可以问自己几个问题每设计一张表可以先问自己这张表表示哪个业务对象 这张表的主键是什么 哪些字段必须唯一 哪些字段会经常查询 哪些字段会用于排序或筛选 字段类型是否符合业务含义 是否需要逻辑删除 是否需要创建时间和更新时间 是否存在一对多或多对多关系 是否需要适当冗余来保留历史快照能回答清楚这些问题表设计一般就不会太差。总结数据库表设计的核心不是简单把字段列出来而是把业务规则转成稳定、清晰、可扩展的数据结构。字段类型要看业务含义主键要稳定唯一唯一约束要兜底业务规则索引要服务查询场景表关系要表达真实业务结构。一句话总结好的表设计是业务理解、数据建模和查询性能之间的平衡。