字段类型与数据表示

一、数值类型设计

数据库数值类型主要分为三类：

类型	特点	主要关注点
INTEGER / BIGINT	整数，范围内精确	数值范围、溢出
FLOAT / DOUBLE	二进制浮点数，近似表示	精度、舍入误差、范围
DECIMAL / NUMERIC	精确十进制数	精度配置、范围、舍入规则

1. 整数

整数适用于：

ID、计数、数量。
库存。
金额的最小货币单位。
固定单位的时间长度。

整数在可表示范围内能够精确存储和计算，但需要注意溢出。

例如：

text

i8：-128 ~ 127

JavaScript 的 number 即使表示整数，也存在安全整数范围：

typescript

Number.MAX_SAFE_INTEGER
// 9007199254740991

超过安全范围，应考虑使用 bigint 等类型。

2. 二进制浮点数

常见类型：

text

FLOAT / REAL
DOUBLE

二进制浮点数无法精确表示部分十进制小数。

例如：

typescript

0.1 + 0.2
// 0.30000000000000004

这是二进制浮点表示的特性，不是某种编程语言独有的问题。

适用于允许一定误差的计算，例如科学计算、测量数据等。

涉及精确金额计算时，应避免使用普通二进制浮点数。

3. DECIMAL / NUMERIC

用于精确表示有限位十进制数。

PostgreSQL：

sql

price NUMERIC(12, 2)

MySQL：

sql

price DECIMAL(12, 2)

其中：

text

12 → 总共最多 12 位十进制数字
 2 → 小数部分保留 2 位

可表示的最大正数：

text

9,999,999,999.99

使用时仍然需要考虑：

精度和小数位数。
数值范围。
运算舍入规则。

注意：SQLite 的 NUMERIC 类型亲和性不等同于 PostgreSQL 的精确 NUMERIC 类型。

SQLite 不原生提供同等能力的精确十进制数值类型。

二、金额存储设计

金额通常有两种主流存储方案。

1. 整数最小货币单位

例如人民币：

text

19.99 元 → 1999 分

SQLite：

sql

price_cents INTEGER NOT NULL
    CHECK (price_cents >= 0)

优点：

整数加减运算精确。
方便比较、排序和统计。
跨数据库兼容性较好。

注意：

统一规定金额单位。
根据业务选择合适的整数范围。
折扣、税费、除法仍然需要舍入规则。
不同货币的最小单位可能不同。

2. 精确十进制金额

例如：

sql

price DECIMAL(12, 2)

直接存储：

text

19.99

优点：

金额表达直观。
支持精确十进制计算。
不需要在元、分之间反复转换。

注意：数据库中的精确数值，在进入应用程序后仍可能丢失精度。

3. 数据库与编程语言的类型映射

数据库使用：

sql

price NUMERIC(12, 2)

ORM 可能将其映射为：

程序类型	精度情况
`number` / `double`	可能产生二进制浮点误差
`string`	可以保留十进制文本表示
`Decimal`	支持精确十进制运算

不同 ORM、数据库驱动的映射规则可能不同。

例如 TypeScript：

typescript

import Decimal from 'decimal.js'

const price = new Decimal('19.99')
const total = price.mul(3)

console.log(total.toString()) // "59.97"

常见编程语言的精确十进制方案：

语言	方案
TypeScript	decimal.js、Prisma.Decimal
Java	BigDecimal
Python	decimal.Decimal
Rust	rust_decimal 等第三方库

如果 ORM 已经返回 Decimal 对象，通常可以直接使用，不需要再次转换。

核心原则：数据库负责精确存储，应用程序负责精确计算，整个数据处理链路都需要避免不必要的浮点转换。

4. 金额设计检查项

text

存储单位
货币类型
金额范围
小数精度
计算方式
舍入规则
ORM 类型映射
API 传输格式

三、UTC、时区与时间戳

1. UTC

UTC（协调世界时）是全球统一使用的时间基准。

不同地区的当地时间可以通过 UTC 偏移量换算。

text

当地时间 = UTC + 偏移量

例如，同一个瞬间：

地区	时间
UTC	12:00
北京 UTC+8	20:00
东京 UTC+9	21:00

2. 时区与 UTC 偏移量

需要区分：

text

UTC+8

表示固定的时间偏移量。

text

Asia/Shanghai
America/New_York

表示具体地区的时区规则。

部分地区存在夏令时：

text

纽约冬季：UTC-5
纽约夏季：UTC-4

因此：

具体时区不一定永远对应相同的 UTC 偏移量。

涉及未来当地时间规则时，通常应保存 IANA 时区标识，而不是只保存固定偏移量。

3. Unix 时间戳

Unix 时间戳通常表示：

从 1970-01-01 00:00:00 UTC 开始经过的秒数。

例如：

text

1970-01-01 00:00:00 UTC → 0
1970-01-01 00:00:01 UTC → 1

时间戳表示绝对时刻，本身没有时区。

同一个瞬间，无论在北京还是东京，对应的 Unix 时间戳都相同。

常见单位：

text

秒
毫秒
微秒
纳秒

使用时必须明确时间戳单位。

四、数据库时间类型

以 PostgreSQL 为例：

类型	含义	常见用途
DATE	年月日	生日、节假日
TIME	时分秒	每日营业时间
TIMESTAMP	不带时区的日期时间	当地日期时间
TIMESTAMPTZ	确定的绝对时刻	创建、支付、登录时间
INTERVAL	时间间隔	持续时间、日期运算

1. DATE

只关心日期：

text

2026-10-08

适用于生日、节假日等。

2. TIME

只关心一天中的钟表时间：

text

09:00:00

适用于：

每天上班时间。
每天营业时间。
每天固定执行任务的当地时间。

跨地区使用时，通常还需要明确所属时区。

3. TIMESTAMP

表示日期和时间，但不包含时区语义。

text

2026-10-08 20:00:00

在 PostgreSQL 中：

sql

TIMESTAMP WITHOUT TIME ZONE

不会根据输入的时区偏移量进行转换。

4. TIMESTAMPTZ

表示一个确定的绝对时刻。

sql

created_at TIMESTAMPTZ NOT NULL

例如：

text

2026-10-08 20:00:00+08:00

对应：

text

2026-10-08 12:00:00+00:00

两者表示同一个瞬间。

PostgreSQL 的 TIMESTAMPTZ 会根据会话时区显示时间，但不会保存输入时原始的时区名称。

适用于：

订单创建时间。
支付成功时间。
登录时间。
实际发生的业务事件时间。

5. TIMESTAMP 与 TIMESTAMPTZ 的区别

类型	主要语义
TIMESTAMP	某一天的某个当地时间
TIMESTAMPTZ	某个确定的瞬间

注意：不同数据库对 TIMESTAMP、DATETIME 的定义和时区处理方式不完全一致。

五、未来预约与重复时间规则

1. 已发生的事件

例如订单支付成功：

text

paid_at TIMESTAMPTZ

重点是记录确定的时刻。

2. 未来约定的当地时间

例如：

用户预约纽约当地时间 2026-11-01 09:00。

可以保存：

text

appointment
- local_datetime TIMESTAMP
- timezone       TEXT
- appointment_at TIMESTAMPTZ

其中：

local_datetime：原始约定的当地日期时间。
timezone：地区时区规则。
appointment_at：计算后的绝对时刻。

对于未来可能变化的时区规则，需要明确业务上应该保持当地约定时间，还是保持原先计算的绝对时刻。

3. 每天重复的时间规则

例如每天纽约时间 10:00 执行任务：

text

execute_time TIME
timezone     TEXT

存储：

text

10:00:00
America/New_York

不要只保存固定 UTC 时间，因为夏令时可能导致 UTC 对应时间变化。

如果需要快速执行调度，可以增加：

text

next_run_at TIMESTAMPTZ

用于保存下一次实际执行时刻。

六、固定时长与日历周期

时间长度与具体时间点是不同概念。

1. 固定时长

例如：

text

30 秒
90 分钟
3600 秒

可以使用整数表示：

text

duration_seconds INTEGER

适合：

视频时长。
音频时长。
固定秒数的超时设置。

2. 日历周期

例如：

text

1 个自然月
1 个自然年

不能简单等同于固定秒数。

因为：

月份天数不同。
存在闰年。
部分地区存在夏令时。

例如：

text

2026-01-31 + 1 个自然月

按照月末截断规则：

text

2026-02-28

但具体计算规则应由业务确定。

3. INTERVAL

PostgreSQL 支持：

sql

INTERVAL '30 days'
INTERVAL '1 month'

两者的业务含义不同。

INTERVAL 支持日期时间运算，但其月、日等组成部分并不都能无条件转换为固定秒数。

4. 套餐周期设计

可以使用：

text

membership_plan
- id
- duration_value
- duration_unit

例如：

duration_value	duration_unit	含义
7	day	7 天
30	day	30 天
1	month	1 个自然月
1	year	1 个自然年

购买后根据业务规则计算实际到期时刻。

text

subscription
- id
- plan_id
- started_at
- expires_at

其中：

text

started_at → 实际开始时刻
expires_at → 实际到期时刻

判断有效期通常采用左闭右开区间：

text

[started_at, expires_at)

即：

text

started_at <= 当前时间 < expires_at

5. 会员续费

对于支持累加有效期的会员：

text

续费起点 = MAX(当前时间, 原到期时间)

然后根据套餐规则计算新的到期时间。

并发续费时，需要防止丢失更新；支付回调还需要进行幂等处理。

七、BOOLEAN 与状态字段

1. BOOLEAN

适用于独立的二元判断。

例如：

text

is_verified
is_active
is_public

取值：

text

true
false

SQLite 通常通过整数 0、1 表示布尔值。

可以使用：

sql

is_active INTEGER NOT NULL DEFAULT 1
    CHECK (is_active IN (0, 1))

2. 枚举状态

如果业务存在多个互斥状态，不适合使用布尔值。

例如：

text

account_status
- active
- frozen
- closed

可以使用字符串或整数编码。

方案	特点
TEXT	可读性好，方便排查
INTEGER	存储紧凑，需要维护编码映射

整数编码不一定比字符串更容易维护。

字段类型应根据业务语义选择，而不是单纯追求存储空间。

八、单一状态与多维状态

1. 单一状态

适用于同一时刻只有一种互斥状态的业务。

例如：

text

article
- id
- status

text

draft → published → archived

2. 多维状态

如果多个状态维度可以独立变化，应考虑拆分。

例如：

text

order
- id
- payment_status
- fulfillment_status
- refund_status

可能同时存在：

text

payment_status     = paid
fulfillment_status = shipped
refund_status      = processing

如果全部使用一个 status 字段，可能产生大量组合状态。

核心原则：能够独立变化的业务状态，应考虑独立建模。

3. 状态字段与独立业务表

如果同一业务对象可以产生多条独立记录，应考虑独立建表。

例如：

text

order (1)
   │
   └── refund (N)

text

refund
- id
- order_id
- amount
- status
- created_at

每笔退款都有独立金额、状态和时间。

不能只依赖订单上的单个 refund_status 保存所有退款过程。

九、状态定义表

如果状态固定且数量较少：

text

status TEXT

通常已经足够。

如果状态需要管理员配置：

新增状态。
修改显示名称。
修改排序。
启用或停用状态。

可以建立独立状态表。

text

ticket_status
- id
- code
- name
- sort_order
- is_active

例如：

id	code	name	is_active
1	pending	待处理	true
2	processing	处理中	true
3	closed	已关闭	false

工单表：

text

ticket
- id
- title
- status_id

关系：

text

ticket.status_id → ticket_status.id

1. code 与 name

text

code → 稳定的业务编码
name → 可修改的显示名称

例如：

text

code = pending
name = 等待受理

推荐对稳定业务编码设置唯一约束：

sql

UNIQUE(code)

2. 停用与删除

text

is_active = false

表示状态不再允许用于新的业务操作，但历史引用可以保留。

注意：

外键只能保证状态存在，不能自动保证状态处于启用状态。

业务程序仍需进行校验。

十、状态流转规则与状态历史

需要区分四个概念。

数据	职责
`ticket_status`	定义有哪些状态
`ticket_status_transition`	定义允许哪些状态转换
`ticket.status_id`	保存工单当前状态
`ticket_status_history`	保存实际发生过的状态转换

1. 状态流转规则

text

ticket_status_transition
- id
- from_status_id
- to_status_id

例如：

from_status_id	to_status_id	含义
1	2	待处理 → 处理中
2	3	处理中 → 已解决
3	4	已解决 → 已关闭
2	1	处理中 → 待处理

两个状态字段都引用：

text

ticket_status.id

可以设置：

sql

UNIQUE(from_status_id, to_status_id)

防止重复定义同一条流转规则。

如果不同工作流拥有独立规则，唯一范围还需要包含工作流标识。

2. 状态流转校验

例如当前状态：

text

ticket.status_id = 1

目标状态：

text

to_status_id = 3

程序查询是否存在：

text

from_status_id = 1
to_status_id   = 3

如果不存在对应规则，就拒绝状态转换。

注意：

外键负责保证起始状态和目标状态存在。
流转规则表保存允许的转换关系。
业务程序负责根据规则校验操作。

普通外键不会自动检查状态流转是否合法。

如果必须在数据库层强制执行，可以进一步使用触发器等机制。

3. 状态历史记录

text

ticket_status_history
- id
- ticket_id
- from_status_id
- to_status_id
- changed_by
- changed_at

例如：

ticket_id	from_status_id	to_status_id	changed_by
1001	1	2	Alice
1001	2	1	Alice
1001	1	2	Bob
1001	2	3	Bob

一条流转规则可以被多张工单重复使用。

但每次实际发生的状态变化，都需要独立记录历史。

4. 规则与历史的区别

text

transition
→ 允许发生什么

history
→ 实际发生了什么

修改当前状态与写入历史记录通常应该放在同一个事务中，确保原子性。

十一、普通外键与联合外键

1. 普通外键

通过一个字段建立引用关系。

text

article.category_id → category.id

SQLite：

sql

FOREIGN KEY (category_id)
REFERENCES category(id)

保证分类真实存在。

2. 联合外键

通过多个字段的组合建立引用关系。

例如多租户：

text

category
- id
- tenant_id
- name

article
- id
- tenant_id
- category_id

业务要求：

文章只能关联自己所属租户的分类。

联合外键：

sql

FOREIGN KEY (tenant_id, category_id)
REFERENCES category(tenant_id, id)

它要求：

text

文章的 (tenant_id, category_id)

必须匹配：

text

分类的 (tenant_id, id)

3. 联合外键与联合唯一

在 SQLite 中，联合外键引用的父表字段组合必须是主键，或者具有匹配的唯一约束。

例如：

sql

UNIQUE(tenant_id, id)

即使 id 已经是主键，如果联合外键引用 (tenant_id, id)，仍然需要匹配的组合唯一约束。

4. 联合外键与 NULL

SQLite 默认情况下，联合外键的任意组成字段为 NULL 时，不要求匹配父表记录。

例如：

text

employee
- id
- tenant_id      NOT NULL
- department_id  NULL

联合外键：

sql

FOREIGN KEY (tenant_id, department_id)
REFERENCES department(tenant_id, id)

允许：

text

tenant_id = 100
department_id = NULL

表示员工尚未分配部门。

但如果分配了部门，就必须属于相同租户。

联合外键不应机械使用。只有多个字段共同构成引用规则时，才需要考虑。

十二、多租户与唯一性范围

多租户系统允许多个客户或组织使用同一套软件。

常见存储架构：

架构	特点
独立数据库	每个租户使用独立数据库
独立 Schema	同库中隔离表结构
共享表	通过 `tenant_id` 区分数据

共享表示例：

text

article
- id
- tenant_id
- title

注意：

tenant_id 只表达数据归属，不会自动保证数据隔离。

仍需要权限校验、租户范围过滤，必要时使用行级安全策略。

唯一约束的业务范围

系统全局唯一：

sql

UNIQUE(code)

租户内部唯一：

sql

UNIQUE(tenant_id, code)

工作流内部唯一：

sql

UNIQUE(workflow_id, code)

核心问题是：

这个字段应该在什么业务范围内唯一？

十三、JSON 字段设计

JSON 适合保存结构灵活的业务数据。

例如：

text

user
- id
- username
- preferences JSON

json

{
  "theme": "dark",
  "fontSize": 16,
  "notifications": {
    "email": true,
    "push": false
  }
}

1. JSON 适用场景

数据结构经常变化。
配置项较多。
主要整体读取和修改。
不需要复杂关联。
不经常参与查询、统计和排序。

例如：

text

用户界面偏好
灵活的扩展配置
部分商品扩展属性

2. 普通字段适用场景

如果字段：

经常参与筛选、排序和统计。
需要独立索引。
需要严格唯一性约束。
需要外键关联。
是稳定且重要的业务属性。

通常优先考虑普通字段。

3. JSON 与关系表

假设文章具有多个标签：

text

article
tag
article_tag

如果标签需要：

被多篇文章复用。
统一改名。
独立管理。
按标签查询文章。
防止重复关联。

应优先使用独立标签表和关联表，而不是直接保存 JSON 数组。

4. 普通字段与 JSON 混合设计

例如商品表：

text

product
- id
- name
- price
- attributes JSON

其中：

text

name、price
→ 稳定的核心业务字段

attributes
→ 不同商品类型的扩展属性

手机：

json

{
  "screenSize": 6.7,
  "batteryCapacity": 5000
}

衣服：

json

{
  "material": "cotton",
  "washingInstructions": "hand wash"
}

这种设计适合扩展属性主要用于展示、很少参与查询的场景。

如果某个 JSON 属性逐渐成为重要查询条件，可以考虑：

提取为普通字段。
使用数据库支持的 JSON 索引。
建立独立的属性模型。

5. JSON 的限制

JSON 可以查询、索引和更新，但不同数据库支持程度不同。

尤其需要注意：

JSON 内部字段仍然需要业务校验。
类型和结构可能不一致。
唯一约束、外键关联通常不如普通关系字段直接。
不适合用来替代明确的一对多、多对多关系。

核心原则：JSON 适合灵活的结构化数据，关系表适合明确的实体、关系和约束。

十四、字段类型设计速查

业务需求	推荐类型或设计
普通整数	INTEGER
大范围整数	BIGINT
允许近似值的小数	FLOAT / DOUBLE
精确金额	DECIMAL / NUMERIC 或整数最小单位
是否启用	BOOLEAN
多种互斥状态	TEXT / INTEGER 枚举
可配置状态	独立状态定义表
状态变化历史	独立历史表
状态流转规则	独立流转规则表
生日	DATE
每天固定时间	TIME
当地日期时间	TIMESTAMP
确定的业务事件时刻	TIMESTAMPTZ
精确媒体时长	整数秒或毫秒
日历周期	INTERVAL 或数值 + 单位
用户界面偏好	JSON
商品灵活扩展属性	JSON 或独立属性模型
可复用标签	标签表 + 关联表
多字段共同决定引用是否合法	联合外键

十五、核心设计原则

设计字段类型时，重点判断：

数值是否要求精确？
决定使用整数、浮点数还是 Decimal。
数值最大可能达到多少？
决定类型范围与精度配置。
时间表示的是日期、钟表时间、确定时刻还是持续长度？
决定使用 DATE、TIME、TIMESTAMP、TIMESTAMPTZ 或其他时长表示。
时间是否受到地区时区和夏令时影响？
决定是否需要保存时区标识及当地时间规则。
字段表达的是二元判断还是多种状态？
决定使用 BOOLEAN 或枚举状态。
多个状态能否独立变化？
决定是否需要拆分状态维度。
业务是否需要保留状态变化历史？
决定是否需要独立历史表。
状态本身是否需要配置和管理？
决定是否需要独立状态定义表。
数据是否需要跨记录关联、查询和约束？
决定使用普通字段、JSON 还是独立关系表。
多个字段是否共同决定引用关系是否合法？
决定使用普通外键还是联合外键。
字段是否属于某个特定业务范围？
决定唯一性和引用完整性的约束范围。
数据是原始业务事实，还是可以计算的衍生数据？
优先保证原始事实完整，再根据性能需求考虑冗余存储。

最终原则：数据库字段类型与数据结构，应根据业务语义、查询需求、数据完整性和未来维护成本共同决定，而不是仅根据数据表面上是什么类型来选择。

字段类型与数据表示 ​

一、数值类型设计 ​

1. 整数 ​

2. 二进制浮点数 ​

3. DECIMAL / NUMERIC ​

二、金额存储设计 ​

1. 整数最小货币单位 ​

2. 精确十进制金额 ​

3. 数据库与编程语言的类型映射 ​

4. 金额设计检查项 ​

三、UTC、时区与时间戳 ​

1. UTC ​

2. 时区与 UTC 偏移量 ​

3. Unix 时间戳 ​

四、数据库时间类型 ​

1. DATE ​

2. TIME ​

3. TIMESTAMP ​

4. TIMESTAMPTZ ​

5. TIMESTAMP 与 TIMESTAMPTZ 的区别 ​

五、未来预约与重复时间规则 ​

1. 已发生的事件 ​

2. 未来约定的当地时间 ​

3. 每天重复的时间规则 ​

六、固定时长与日历周期 ​

1. 固定时长 ​

2. 日历周期 ​

3. INTERVAL ​

4. 套餐周期设计 ​

5. 会员续费 ​

七、BOOLEAN 与状态字段 ​

1. BOOLEAN ​

2. 枚举状态 ​

八、单一状态与多维状态 ​

1. 单一状态 ​

2. 多维状态 ​

3. 状态字段与独立业务表 ​

九、状态定义表 ​

1. code 与 name ​

2. 停用与删除 ​

十、状态流转规则与状态历史 ​

1. 状态流转规则 ​

2. 状态流转校验 ​

3. 状态历史记录 ​

4. 规则与历史的区别 ​

十一、普通外键与联合外键 ​

1. 普通外键 ​

2. 联合外键 ​

3. 联合外键与联合唯一 ​

4. 联合外键与 NULL ​

十二、多租户与唯一性范围 ​

唯一约束的业务范围 ​

十三、JSON 字段设计 ​

1. JSON 适用场景 ​

2. 普通字段适用场景 ​

3. JSON 与关系表 ​

4. 普通字段与 JSON 混合设计 ​

5. JSON 的限制 ​

十四、字段类型设计速查 ​

十五、核心设计原则 ​

字段类型与数据表示

一、数值类型设计

1. 整数

2. 二进制浮点数

3. DECIMAL / NUMERIC

二、金额存储设计

1. 整数最小货币单位

2. 精确十进制金额

3. 数据库与编程语言的类型映射

4. 金额设计检查项

三、UTC、时区与时间戳

1. UTC

2. 时区与 UTC 偏移量

3. Unix 时间戳

四、数据库时间类型

1. DATE

2. TIME

3. TIMESTAMP

4. TIMESTAMPTZ

5. TIMESTAMP 与 TIMESTAMPTZ 的区别

五、未来预约与重复时间规则

1. 已发生的事件

2. 未来约定的当地时间

3. 每天重复的时间规则

六、固定时长与日历周期

1. 固定时长

2. 日历周期

3. INTERVAL

4. 套餐周期设计

5. 会员续费

七、BOOLEAN 与状态字段

1. BOOLEAN

2. 枚举状态

八、单一状态与多维状态

1. 单一状态

2. 多维状态

3. 状态字段与独立业务表

九、状态定义表

1. code 与 name

2. 停用与删除

十、状态流转规则与状态历史

1. 状态流转规则

2. 状态流转校验

3. 状态历史记录

4. 规则与历史的区别

十一、普通外键与联合外键

1. 普通外键

2. 联合外键

3. 联合外键与联合唯一

4. 联合外键与 NULL

十二、多租户与唯一性范围

唯一约束的业务范围

十三、JSON 字段设计

1. JSON 适用场景

2. 普通字段适用场景

3. JSON 与关系表

4. 普通字段与 JSON 混合设计

5. JSON 的限制

十四、字段类型设计速查

十五、核心设计原则