数据库字段怎么设计-主键、状态、时间与约束

1435597771 ·

表结构定下来之后，很多人会松一口气，觉得差不多就行了。真正让系统在半年后变得难以维护的，往往是设计字段层面当时觉得“差不多就行”的决定。

字段并不是给值存下来就行，他决定了业务事实如何被记录，如何被查询，如何被约束以及未来如何演进。一个设计不当的字段，后面会以“状态判断越来越复杂”，“金额对不上”，“唯一性靠代码硬保证”的方式持续收利息。

本篇文章我们不谈实体怎么拆分，只聚焦一件事：当表结构定下来，每个字段应该怎么设计

字段在表达什么？应该放在哪里？

字段设计的核心目标只有一个：准确表达业务事实 ，而不是能存进去就行

看几个常见的错误：

quantity VARCHAR(20) 
status INT
amount FLOAT

这些字段设计都能给值存进数据库中，但是这么做会带来问题：

  • 数量用字符串，后续在代码中使用它都需要转换，容易出错。若是有人给数量单位一并存进去更是完蛋

  • status在数据库中用INT或者TINYINT是常用的方法，但是必须要在代码层面用枚举类型联合声明使用。否则单独使用INT则是魔法数字，没人看的懂。

  • 金额用FLOAT，因为FLOAT是近似浮点数，在float计算的时候会产生精度误差，0.1+0.2不等于0.3的问题会变成真实的事故。

字段设计首先要问的不是这个值在数据库中怎么存，而是问这个值在业务上是什么？是否为空？是否有明确的取值范围？是否需要参与计算？是否会作为查询或关联条件？把这些问题想清楚，类型和约束自然就出来了。

字段到底该放在那张表？

我们用订单来举例子，很多时候数据明明都和订单有关系，却不能全部塞进order表中。看下面几个字段

order.user_id
order_item.quantity
payment_record.amount_cent
inventory.stock

真正的判断标准不是和谁有关系，而是这个值在什么业务粒度下才能被唯一确定

  • user_id 在订单级别下确定，表明这个订单属于哪个用户

  • quantity 必须绑定某个sku，粒度是订单明细。否则一个订单有多个商品时，一个订单必须要创建相同的多个记录才能为每个sku描述数量。

  • amount_cent属于一次支付行为，支付可能对应多次尝试或者退款冲正。

  • stock是商品本身的属性，跟某一次订单无关

如果把quantity硬塞order，当订单包含多个SKU时，只能用数组存数据。字段归属的错误，本质上就是对业务可粒度的误判。

因此在实际设计的时候，可以问一下自己：“这个值变化时，影响的是哪一层业务对象”

身份与唯一性：技术主键、业务键、复合约束

几乎所有的核心业务表都会同时出现这两种键

id BIGINT PRIMARY KEY
order_no VARCHAR（32） UNIQUE

在我最初设计的时候，我经常会疑问，既然order_no已经唯一了，为什么还需要id？

后面我才知道，他们代表的身份不同：

  • id:是技术主键。他的唯一职责是给数据库内部一个稳定、高效、永不变化的身份标识。它适合做外键引用、适合做聚簇索引、适合在分布式环境下永雪花算法生成。业务通常不关心他的具体值

  • order_no:是业务唯一键。他是给人看的、给外部系统对接的。它可能包含日期、业务线表示、甚至在极端情况下允许被人工修改。

两者可以，而且应该同时存在。

如果只用业务唯一键做主键，会带来几个真实的痛点：

那么当业务号生成规则一变，所有外键都要更改；业务号通常较长，作为聚簇索引时二级索引会更臃肿；在分库分表场景下，业务号和唯一性保证会变得复杂。

技术主键解决“数据库如何高效识别一行”，业务唯一键解决“业务如何识别”。千万不要混淆两者。

我们下面说复合约束，用订单明细举例子：
CREATE TABLE order_item (
  id         BIGINT PRIMARY KEY,
  order_id   BIGINT NOT NULL,
  sku_id     BIGINT NOT NULL,
  quantity   INT NOT NULL
);

只建 PRIMARY KEY的人会觉得唯一性已经有了，但这是错的。

id保证的是这行在数据库里是唯一的，但是业务规则通常要求：同一个订单里，同一个sku只能出现一次。

那么就必须要加上业务唯一键：

UNIQUE KEY uk_order_sku (order_id, sku_id)

如果没有，那么并发下单、重复提交都可能导致同一个订单出现多行相同的SKU。事后只能靠数据清洗去修。

这揭示了一个更本质的区分：数据库记录唯一性和业务规则的唯一性是两回事。

状态怎么设计才不会越写越乱

状态是字段设计里最容易失控的地方。典型的错误是把所有可能的状态塞进一个字段：

PENDING_PAYMENT
PAYMENT_FAILED
PAID
SHIPPED
REFUNDING
REFUNDED
PARTIAL_REFUNDED

这种设计乍一看能用，但是一旦出现"已支付但正在退款"“已发货但部分退款”，“支付失败后重新支付成功”等场景。状态会直接变一团浆糊。

正确的做法是按业务维护拆分状态。一个订单至少涉及三个相对独立的维度：

  • 订单主状态(CREATED → PAID → SHIPPED → COMPLETED / CANCELLED)

  • 支付状态（UNPAID → PAID / FAILED）

  • 退款状态（NONE → REFUNDING → REFUNDED / PARTIAL_REFUNDED）

判断状态是否要拆分的标准很实用：如果两个状态可以同时成立，他们就属于不同的维度，应该拆开

和状态相关的另一个常见的选择是Boolean。
is_waiting
is_paid
is_shipped
is_completed
is_cancelled

这种设计的问题在于：多个布尔值可以任意组合，而真正合法的状态组合通常只有几种。结果就是代码中必须写大量互斥校验，否则数据就会进入非法撞他。

Boolean只适合真正的二元开关，并且未来几乎不会演变形成多状态的场景。一旦某个概念有明确的生命周期演进，就应该用状态字段，而不是多个布尔。 状态字段本身就是在显示声明“合法状态集合”，要比用Boolean稳健。

时间字段：系统时间和业务时间必须分开

几乎每张表都会有：
created_at  DATETIME NOT NULL
updated_at  DATETIME NOT NULL

他们记录的是系统视角的时间：这条记录何时被创建、何时被修改

但业务关心的时间则往往是另一回事：
paid_at
shipped_at
completed_at
cancelled_at
refunded_at

这些是业务时间发生的时间。

不要用updated_at去近似paid_at，这会带来：一次普通的备注修改也会更新updated_at，导致无法准确知道订单支付到底在发生什么时间；对账、时效统计、用户侧展示都会失真。

系统时间由框架或者数据库自动维护即可，业务时间必须在对应业务动作发生时显示写入。而且业务时间一旦被写入，就不应该随意覆盖（除非有明确的修正流程）。

另外值得注意的是时区问题。业务时间建议统一使用UTC存储，在展示层再转换成用户时区，否则跨时区会带来隐患。

NULL、默认值与类型选择

NULL是数据库里语义最模糊的值之一。

它到底代表"未知"、“不适用”还是"尚未发生“。不同的人有不同的理解，而不同的理解就是问题的根源。

常见的错误用法是用NULL表示某种业务状态：

payment_status = NULL   -- 本意是“未支付”

这会带来两个麻烦：一是查询时必须同时处理"UNPAID"和 “IS NULL”，二是NULL在唯一索引、聚合索引、比较运算中都和普通值不同，容易埋坑。

更清晰的做法是：

payment_status = 'UNPAID'   -- 明确的业务状态
paid_at = NULL              -- 表示“支付这个事件尚未发生”

因此使用NULL的原则可以总结为：需要表达明确业务状态时，用具体的枚举值，不要用NULL；需要表达某个业务事件尚未发生时，可以用NULL（尤其是时间字段）。尽量避免让一个字段用NULL承担不同的含义。

默认值用的好是便利，用不好是隐藏的bug

合理的例子是: status DEFAULT 'CREATED'。 订单创建时，状态天然就是CREATED，这是业务上的初始事实。

危险的例子是: total_amount DEFAULT 0 。这会让代码在漏赋值时依然能插入成功，让本该报错的逻辑错误被静默吞掉，数据以错误的初始值存入数据库，事后极难排查。

使用默认值的原则是：DEFAULT 只应该表达业务上天然的初始状态，而不是替业务代码兜底。如果一个字段在业务上必须由上游明确计算后传入，那就应该设置为NOT NULL且不给默认值，让错误尽早暴露。

类型选择最常见的错误思路是：

“VARCHAR最灵活，什么都存字符串吧。” 这会带来持续的成本：每次计算都要转换、逻辑校验全部推给应用层，索引效率下降。

更合理的映射应该直接反应业务语义：

sku_code      VARCHAR(64)     -- 编码
quantity      INT             -- 数量
price_cent    BIGINT          -- 金额，用最小货币单位
weight_gram   INT             -- 重量，统一到克

需要注意的是：金额是字段设计里最不能妥协的地方之一。FLOAT/DOUBLE的问题不是理论问题，而是生产事故的来源。浮点数无法精准表示0.1，累计误差在财务场景下完全不可接受。 DECIMAL是可用的选择，但要明确精度。

我个人在大多数业务里最终采用的是 price_cent BIGINT，全链路统一用“分”作为单位。前端展示时除以 100，后端计算和比较全程用整数。精度问题彻底消失，计算更快，跨系统传输时也不容易因为小数位理解不一致而出问题。比“选哪种数据库类型”更重要的是全链路单位统一。

数据库约束真正在保护什么

最后把最常见的约束放在一起看，它们各自回答不同的问题：

 约束         真正保护的问题          典型场景                   
 PRIMARY KEY  这条记录是谁            给每一行一个稳健身份        
 UNIQUE       什么在业务上不能重复    order_no,手机号，复合业务键 
 NOT NULL     什么必须存在            核心业务字段不允许丢失      
 CHECK        什么范围是合法的        数量>0,状态属于枚举集合     
 FOREIGN KEY  引用的对象是否真实存在  order_id必须对应真实订单    

把约束都放在应用层，在短期会更灵活，长期则会用数据质量持续偿还。约束不是束缚，要通过约束让错误在写入时就被拒绝。

总结

字段设计没有绝对正确的模版，但有清晰的判断习惯：

  1. 字段放在那张表？看他在什么业务粒度下才能被唯一确定

  2. 主键和业务唯一键有什么区别？ 一个解决数据库身份问题，一个解决业务识别问题，职责不同，通常同时存在

  3. 状态为什么要拆维度？因为可以同时成立的状态属于不同的状态机，硬揉合在一起只会让逻辑爆炸

  4. NULL和默认值怎么选？NULL 适合表达“事件尚未发生”，默认值只适合表达天然初始状态，绝不要用来掩盖代码漏洞。

  5. 金额和数量用什么类型？ 优先匹配业务语义，金额建议用最小单位的📄，全链路统一。

  6. 数据库约束到底在保护什么？身份、唯一性、存在性、合法范围、引用完整性。它们是数据只想的最后一道防线。

把"这个字段到底在表达什么业务事实"这个问题问透，后面的类型、约束、归属自然会水到渠成。设计时多想一步，运行时就会少还很多债。