关系模型、数据类型与字符集

数据类型不仅决定存储空间，也决定比较、排序、精度、有效范围和索引行为。关系模型的目标不是把对象字段原样搬进表，而是用键、约束和关系表达长期不变量，并为查询和演进保留清晰语义。

一、学习目标

识别实体、关系、候选键与函数依赖
为整数、金额、时间和文本选择准确类型
理解 utf8mb4 字符集与排序规则
正确处理 NULL 与三值逻辑
判断 JSON 与规范化列的边界

二、实体、关系与范式

customers、products 和 orders 是实体，order_items 连接订单与商品并保存下单时价格。订单项不是简单多对多连接表，因为数量和单价属于该关系。

规范化减少更新异常：客户邮箱只存客户表，订单保存客户外键；商品当前价格与订单成交价分开。反规范化必须说明同步规则和收益，不能因为 JOIN 看起来复杂就复制字段。

每张表先确定候选键，再选择主键。代理 BIGINT 主键便于关联，但 SKU、邮箱等业务唯一性仍需 UNIQUE 约束。

三、整数与金额

计数和主键使用范围合适的整数；UNSIGNED 增加正数范围，但跨数据库兼容性较弱。本系列使用 BIGINT UNSIGNED 主键、INT UNSIGNED 数量。

金额使用定点：

price DECIMAL(12,2) NOT NULL,
quantity INT UNSIGNED NOT NULL,
CONSTRAINT chk_quantity_positive CHECK (quantity > 0)

不要用 FLOAT/DOUBLE 保存货币；二进制浮点无法精确表示许多十进制小数。DECIMAL 仍需定义币种、舍入和最大范围，数据库类型不能代替业务金额模型。

四、日期与时间

DATETIME 保存日历日期时间，不做时区转换；TIMESTAMP 在连接时区与 UTC 间转换且范围不同。跨系统事件通常以 UTC 存储，并在应用边界转换展示。

created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
  ON UPDATE CURRENT_TIMESTAMP(6)

自动更新时间适合记录物理行变化，不一定等于业务事件时间。订单支付时间、取消时间应由明确业务操作写入，而不是复用 updated_at。

五、字符集与排序规则

utf8mb4 覆盖完整 Unicode。排序规则决定大小写、重音和语言比较：

name VARCHAR(200)
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci NOT NULL

标识符、哈希、令牌和区分大小写的外部键常需要二进制或区分大小写排序规则。不要假定两个不同 collation 的列比较总能使用索引；隐式转换可能改变语义和计划。

索引长度按字节受影响，设计长 VARCHAR 联合索引前要评估选择性与大小。

六、NULL 与 JSON

NULL 表示未知或不适用，col = NULL 永远不是 true，应使用 IS NULL。NOT NULL 应作为默认选择，只在领域确实允许缺失时使用 NULL；空字符串、0 和魔法日期不是 NULL 的替代品。

JSON 适合结构稀疏、变化快且不总参与关系约束的附加属性。需要唯一、外键、频繁筛选或聚合的数据应使用普通列。JSON 路径查询可配合生成列和索引，但 schema 责任没有消失。

七、从知识点到工程契约

本篇 SQL 最终要运行在长期存在的 schema、连接和事务中，而不是只在一个临时查询窗口里得到一次正确结果。先写清输入表与行数、会话设置、事务边界、允许的锁、期望结果和失败后的状态，再决定使用约束、查询、索引、存储对象还是运维命令。数据库行为同时受到版本、隔离级别、字符集、统计信息和并发会话影响，示例必须把这些前提显式化。

可以用以下顺序把知识点落到 StudyStore：

在全新数据库按迁移顺序建立最小 schema，保存 SHOW CREATE TABLE 与关键会话变量。
加入一个与“只创建代理主键，却遗漏业务唯一约束”相关的反例，记录错误码、SQLSTATE、事务是否仍可用以及数据是否改变。
为正常、空集、边界值、重复值和并发冲突准备确定性种子，不依赖手工残留数据。
对读查询检查结果集和 EXPLAIN ANALYZE；对写操作同时检查受影响行数、约束、提交与回滚。
最后才讨论性能优化。索引和参数调整必须有执行计划、等待事件或容量数据支持，并保留变更前基线。

审查数据库设计时至少回答四个问题：谁能写，哪条约束保护不变量，事务在哪一层结束，失败后如何恢复。MySQL 能保证声明范围内的事务和持久性，却不会替应用补上缺失约束、幂等键、备份演练或最小权限。只要这些问题没有明确答案，就不要把一次成功执行当成生产方案。

本篇最重要的能力是“识别实体、关系、候选键与函数依赖”。能由主键、唯一键、外键、CHECK 或数据类型表达的规则，应优先落到数据库；需要跨聚合或外部系统判断的规则，再由应用和事务协调。不要用注释或约定替代可执行约束。

八、验证策略与复盘

验证分为 schema、数据、并发和恢复四层。schema 层检查定义与会话基线；数据层断言结果和约束；并发层至少用两个独立连接观察锁与隔离；恢复层在新实例重放迁移、备份与恢复。只看客户端显示“Query OK”不能证明结果正确，更不能证明在另一组数据和并发时仍正确。

建议保存下面的实验记录：

项目	需要记录的证据
版本	`SELECT VERSION()` 与镜像标签
会话	sql_mode、time_zone、事务隔离与字符集
输入	schema 版本、种子行数和参数值
输出	结果集、受影响行数、警告、错误码与 SQLSTATE
性能	执行计划、实际行数、耗时与等待
恢复	COMMIT/ROLLBACK 结果、备份校验和与恢复行数

每组脚本都应能在空数据库从头执行，并通过显式断言退出非零。涉及权限时分别用管理员和最小权限账户连接；涉及锁时设置有限等待，避免测试永久挂起；涉及复制时等待 GTID 状态而不是固定 sleep。清理只删除本次创建的临时容器、网络和数据库。

本篇可以用以下目标做验收：为整数、金额、时间和文本选择准确类型；理解 utf8mb4 字符集与排序规则；正确处理 NULL 与三值逻辑。把每个目标转换为一条可重复 SQL、一个预期错误或一项恢复检查。若优化后结果正确但计划退化，应把执行计划也纳入回归证据。

发布前再从三个方向反向审查：把数据量放大两个数量级，判断扫描、锁和日志是否仍可控；让两个会话以最不利顺序并发，判断约束和事务是否仍保护不变量；让执行在任意一步失败，判断备份、回滚或幂等键能否恢复。只在十几行数据和单连接中成功的 SQL 仍只是功能草稿。

最后把脚本交给一个只知道镜像版本和入口命令的干净环境执行。脚本不得依赖图形客户端自动设置、个人默认数据库或先前会话变量；所有对象名、字符集、事务和预期错误都应明确。对仅用于说明、不可直接执行的片段要标注上下文，避免把省略条件的示意 SQL 当作完整迁移。

还要保存一份“结果为什么可信”的说明：约束证明哪些坏数据无法进入，事务证明哪些变化共同提交，执行计划证明访问了哪些行，权限测试证明哪些身份无法越界，恢复演练证明故障后能重新得到可用状态。这五类证据缺一时，都应在结论旁写明限制。数据库实验的价值不只是得到答案，而是让另一位操作者在不同机器、不同时间仍能重现同一判断。

若结论依赖当前数据分布或配置，应把适用范围写在 SQL 旁，并安排数据增长或版本升级后的复测条件；不要把一次测量永久固化为规则。

九、StudyStore 实验

为 StudyStore 画逻辑模型，给六张表选择主键、业务唯一键、金额、数量、UTC 时间和文本排序规则。写出每个可空列为何允许 NULL，并把无法解释的列改为 NOT NULL 或拆分关系。

完成本节后，不要只保存代码或 SQL。请同时保存执行命令、关键输出和失败案例；学习笔记真正有价值的部分，是能够说明输入、状态变化、输出以及失败后的恢复方式。

十、常见错误

只创建代理主键，却遗漏业务唯一约束
用 FLOAT 保存价格或金额
混用本地时间与 UTC，丢失时区语义
所有文本沿用同一大小写不敏感规则
把需要约束和连接的核心字段塞入 JSON

十一、练习与自测

为邮箱、SKU、名称和令牌分别选择排序规则并解释。
比较 DECIMAL 与 DOUBLE 对 0.1 累加的结果。
写出三个 NULL、空字符串和 0 语义不同的业务例子。
把一个包含重复客户数据的订单表规范化到第三范式。

自测时应在干净的临时目录或临时数据库中重新执行，而不是依赖上一节遗留的状态。如果结果与预期不同，先记录实际输出，再缩小问题范围。

十二、官方资料

版本行为与二手文章不一致时，以本系列固定版本的官方文档、命令输出和可重复测试结果为准。

上一篇：环境、版本与客户端基线下一篇：数据库、表、约束与 DDL 演进