Nest 使用笔记

第七章：MySQL 基础——从 learnhub 的真实表结构读懂列类型、字符集、索引与外键

打开 learnhub 的 Init 迁移，从真实 CREATE TABLE 讲透 utf8mb4 字符集、自增主键、UNIQUE 索引、联合主键、外键，再到连接配置里 charset/timezone 的为什么。每个结论都在 learnhub 的 schema 里指得到，顺带说清什么时候该选 MySQL、什么时候该让位给 Mongo 或 Redis、为什么高并发系统反而会拆掉外键。

2026年7月19日

MySQL
SQL
数据层

数据要落盘才叫真正的后端。这一章用 learnhub 的真实表结构（10 张表、一整套 CREATE TABLE）讲透 MySQL 的基础：列怎么定义、字符集为什么必须是 utf8mb4、自增主键和联合主键怎么选、UNIQUE 索引怎么读、外键买得到什么又付出什么。和「写个 student 表跑四条 SQL」的入门教程不同，这一章所有结论都在 learnhub 的 Init 迁移文件里指得到，外加一段老老实实的决策对比：核心数据什么时候该选 MySQL，什么时候该让位给 Redis 或 Mongo。TypeORM 怎么把这些表接到 Nest 里，是下一章的事。

先搞懂：MySQL 是什么 / 为什么需要 / 企业级怎么用

MySQL 是什么：最常用的关系型数据库（RDBMS）。数据按「表」存，每张表是行和列的二维结构，行 = 一条记录，列 = 一个字段。表和表之间靠「外键」或「关联表」建立关系——learnhub 里 post.authorId 指向 user.id，就是这种引用。用 SQL 语言操作，支持 ACID 事务（一组操作要么全成功、要么全回滚），数据写磁盘，重启不丢。

为什么需要它：判断一类数据该放哪里，看三件事——要持久吗、结构固定吗、改动要多表一致吗。learnhub 的 user、post、comment 全放 MySQL，因为账号丢了不能忍（持久）、用户都有用户名/密码/邮箱（结构固定）、发帖绑标签要「帖子入库 + 中间表入库」一起成功（多表一致，事务）。反例：浏览量这种高频计数如果每次都 UPDATE post，会把那一行锁热——learnhub 把它先攒 Redis 里定时刷（第十三章细讲）；聊天消息、行为日志这种弱结构、海量写的，learnhub 用 MongoDB（第十八章细讲）。三句话决策：

核心、要持久、要事务一致 → MySQL；
高频临时、丢了能接受 → Redis（缓存/排行榜/限流）；
弱结构、海量写、嵌套深 → MongoDB（行为日志/消息流）。

企业级怎么用：主从读写分离扛并发、单表千万级以上分库分表、应用侧用连接池（mysql2 的 createPool）、慢查询靠索引和 EXPLAIN 调优、每天定时备份 binlog。这些不是这一章能讲完的（深入见 MySQL 指南），但 schema 设计错了，主从分库分表都救不回来——所以这一章先把表设计讲对。

这一章你会做出什么

打开 learnhub 的 Init 迁移，把 10 张表的真实 CREATE TABLE 逐条读懂。
在真实 schema 里吃透列类型（int / varchar / text / tinyint / datetime(6)）、NOT NULL 与默认值、utf8mb4 字符集。
搞懂两类索引：UNIQUE INDEX（唯一约束）和联合主键（中间表 post_tag 的 postId + tagId）。
看懂外键的 ALTER TABLE ... ADD CONSTRAINT FOREIGN KEY——它换来什么、为什么高并发系统反而会拆掉它。
理清 MySQL 连接配置里的 charset: 'utf8mb4' 和 timezone: '+08:00'。

第一步：先做决策——MySQL 不是唯一答案

进 SQL 之前先回答一个问题：为什么 learnhub 用 MySQL，不用 MongoDB？——因为它的数据是高度关系化的。一个帖子有作者（user）、有评论（comment，评论又有作者）、有标签（tag，多对多）、有收藏（多对多），这些实体之间是密集的引用关系，关系型数据库的 JOIN 和外键正是为这种场景设计的。MongoDB 是文档型存储，一条文档自包含、嵌套深的时候很顺，但要做「查所有 admin 用户发的、带某标签的帖子」这种跨实体查询就费劲。

但 learnhub 不是只用 MySQL。它把不同特性的数据分给了不同的存储：

MySQL：user / post / comment / tag / role / permission——核心业务数据，要持久、要事务、要 JOIN。
Redis（第十三章）：浏览量计数、排行榜、热点缓存——高频写、丢了能接受。
MongoDB（第十八章）：行为日志（用户点了什么、看了什么）——弱结构、写多读少、嵌套灵活。
Elasticsearch（第二十一章）：帖子全文检索——MySQL 的 LIKE '%关键词%' 不走索引，海量数据下要用专门的搜索引擎。

思考：为什么帖子正文 content 放 MySQL，而行为日志放 Mongo？——帖子要被事务包住（发帖 + 绑标签必须一起成功）、要被 JOIN 查（详情页带作者带标签）；行为日志是一条条独立事件，不需要事务、不需要 JOIN，但写入量极大（用户每点一下就是一条），Mongo 的写性能和灵活 schema 更合适。存什么数据选什么数据库，是后端架构的核心判断之一，不是「全都塞 MySQL」也不是「全都塞 Mongo」。

第二步：起 learnhub 的 MySQL

学表结构前先有个跑着的 MySQL。learnhub 的 docker/docker-compose.yml 里已经配好：

# learnhub/docker/docker-compose.yml
mysql:
  image: mysql:8.0
  container_name: learnhub-mysql
  environment:
    MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD:-learnhub_root_2024}
    MYSQL_DATABASE: ${MYSQL_DATABASE:-learnhub}     # 启动时直接建好 learnhub 库
    MYSQL_USER: ${MYSQL_USER:-learnhub}
    MYSQL_PASSWORD: ${MYSQL_PASSWORD:-learnhub_2024}
    TZ: Asia/Shanghai
  command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
  ports:
    - "3306:3306"
  volumes:
    - mysql-data:/var/lib/mysql                      # 数据持久化到命名卷，重启不丢

一行命令起：

cd learnhub
docker compose -f docker/docker-compose.yml up -d mysql

两个细节值得讲：MYSQL_DATABASE: learnhub 让容器初始化时自动建库，省得手动 CREATE DATABASE；command 里的 --character-set-server=utf8mb4 把服务端默认字符集设成 utf8mb4——这一章后面会反复强调：utf8mb4 而不是 utf8。

第三步：连接配置——charset 和 timezone 的为什么

应用侧连 MySQL 的配置在 learnhub 的 configuration.ts 里，是一份把环境变量组织成嵌套对象的工厂：

// learnhub/src/config/configuration.ts
export default () => ({
  port: parseInt(process.env.PORT || '3000', 10),
  nodeEnv: process.env.NODE_ENV || 'development',
  mysql: {
    host: process.env.MYSQL_HOST || '127.0.0.1',
    port: parseInt(process.env.MYSQL_PORT || '3306', 10),
    username: process.env.MYSQL_USER || 'root',
    password: process.env.MYSQL_PASSWORD || '',
    database: process.env.MYSQL_DATABASE || 'learnhub',
  },
  // redis / mongo / minio / es 等其它配置同理
});

这份配置是给 ConfigService.get('mysql.host') 用的，本身只有连接参数。真正消费它、把 charset 和 timezone 这些连接级选项加上去的，是 app.module.ts 里的 TypeOrmModule.forRootAsync：

// learnhub/src/app.module.ts
TypeOrmModule.forRootAsync({
  imports: [ConfigModule],
  inject: [ConfigService],
  useFactory: (config: ConfigService) => ({
    type: 'mysql',
    host: config.get<string>('mysql.host'),
    port: config.get<number>('mysql.port'),
    username: config.get<string>('mysql.username'),
    password: config.get<string>('mysql.password'),
    database: config.get<string>('mysql.database'),
    autoLoadEntities: true,
    synchronize: false,          // 永远 false，靠 migration
    logging: config.get('nodeEnv') === 'development',
    timezone: '+08:00',          // 东八区
    charset: 'utf8mb4',          // 关键：4 字节 utf8
  }),
}),

这两行得讲清楚：

charset: 'utf8mb4'——MySQL 的 utf8 是个历史遗留的 3 字节截断版，存不下 4 字节的 emoji（笑脸、火箭这类表情符号会被写成 ???）。utf8mb4 才是完整的 UTF-8（mb = most bytes），能存 emoji 和生僻字。learnhub 是社区产品，用户名、评论里都可能带 emoji，所以必须 utf8mb4。注意：连接字符集要和建表字符集一致——Init 迁移建的表继承 command 里 --character-set-server=utf8mb4 的默认字符集，连接侧也是 utf8mb4，两端对齐，否则写入会乱码。
timezone: '+08:00'——让 TypeORM 写入和读取时间时按东八区解释。MySQL 的 datetime 不带时区，应用侧不指定时区，写进去的时间和实际会差 8 小时（服务器走 UTC、业务在东八区）。+08:00 让两端对齐。

至于 forRootAsync 怎么异步拿到 ConfigService、autoLoadEntities 和 synchronize: false 的完整含义，是下一章（第八章）的主线，这里只盯 MySQL 本身强相关的 charset/timezone。

第四步：建表的真实样子——拆开 Init 迁移

这是这一章的核心。learnhub 的所有表不是手写 SQL 建的，是 TypeORM 的 migration:generate 拿 Entity 和空库做 diff 自动生成的，文件叫 Init，里面是一连串 CREATE TABLE。从 user 表看起：

// learnhub/src/migrations/1784362383603-Init.ts
await queryRunner.query(`CREATE TABLE \`user\` (
  \`id\` int NOT NULL AUTO_INCREMENT,
  \`username\` varchar(50) NOT NULL COMMENT '用户名',
  \`password\` varchar(64) NOT NULL COMMENT '密码 MD5',
  \`email\` varchar(100) NULL COMMENT '邮箱',
  \`avatar\` varchar(255) NULL COMMENT '头像 URL（阶段4 上传到 MinIO）',
  \`isFrozen\` tinyint NOT NULL COMMENT '是否冻结：0 否 1 是' DEFAULT '0',
  \`create_time\` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  \`update_time\` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  UNIQUE INDEX \`IDX_78a916df40e02a9deb1c4b75ed\` (\`username\`),
  PRIMARY KEY (\`id\`)
) ENGINE=InnoDB`);

逐列拆：

id int NOT NULL AUTO_INCREMENT + PRIMARY KEY (\id`)：自增整数主键。每插一条自动 +1，NOT NULL+PRIMARY KEY是主键的标配（不能空、不能重复）。为什么用int自增而不是 UUID——自增主键插入时 InnoDB 顺序写、不产生页分裂，性能高；缺点是会暴露增长规模（能反推用户量），分布式系统会换雪花算法（snowflake）。learnhub 单库够用，选最简单的int AUTO_INCREMENT`。
varchar(50) NOT NULL：变长字符串，最大 50 字符。NOT NULL 表示不能是 null。注意：MySQL 里空字符串 '' 和 NULL 是两回事——NOT NULL 只禁止 NULL，不禁止 ''，业务上要不要再校验非空看场景。
varchar(100) NULL（email/avatar）：可空——用户可能没填邮箱。NULL 表示「未知/没有」，和 ''（空字符串）语义不同。设计表时「这个字段一定有吗」决定 NOT NULL 还是 NULL，能 NOT NULL 就 NOT NULL，省掉一堆判空。
tinyint ... DEFAULT '0'：1 字节整数，用来存布尔语义（isFrozen 0/1）。MySQL 也有 boolean 类型，但底层就是 tinyint(1)，learnhub 直接用 tinyint 明确表达「0/1」。
datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)：时间戳，精度 6 位（微秒）。DEFAULT CURRENT_TIMESTAMP(6) 表示插入时不显式给值就用当前时间——这是 TypeORM @CreateDateColumn 映射出来的列，对应实体的 createTime。
datetime(6) ... ON UPDATE CURRENT_TIMESTAMP(6)：更新时自动刷新成当前时间——对应 @UpdateDateColumn 的 updateTime。两条合起来就是「创建时间和更新时间数据库自己维护，代码不用管」。
ENGINE=InnoDB：MySQL 5.5 起的默认引擎，支持事务、行锁、外键。另一个常见引擎 MyISAM 不支持事务和外键，现代项目基本不用。learnhub 全部 InnoDB。

注意：这个迁移是机器生成的——看那些 IDX_78a916df40e02a9deb1c4b75ed 一样的 hash 命名就知道。生成完一定要人眼 review 再 run。机器偶尔会误判，比如你只是改了列注释，它可能生成「DROP 列再 ADD」，跑下去那一列的数据就没了。

再看 post 表，模式一样，但有一列特别关键：

// learnhub/src/migrations/1784362383603-Init.ts
await queryRunner.query(`CREATE TABLE \`post\` (
  \`id\` int NOT NULL AUTO_INCREMENT,
  \`title\` varchar(100) NOT NULL,
  \`content\` text NOT NULL,
  \`view_count\` int NOT NULL COMMENT '浏览量（阶段5 改由 Redis 维护）' DEFAULT '0',
  \`like_count\` int NOT NULL DEFAULT '0',
  \`pinned\` tinyint NOT NULL COMMENT '是否置顶（阶段3 练习：仅 admin）' DEFAULT 0,
  \`create_time\` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  \`update_time\` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  \`authorId\` int NULL,
  PRIMARY KEY (\`id\`)
) ENGINE=InnoDB`);

content 用 text 而不是 varchar(...)——帖子正文长度不定、可能很长，text 最大 64KB，不占行的定长空间。authorId int NULL 是作者外键列，类型和 user.id 对齐（都是 int），这里只声明列，外键约束在另一个 ALTER TABLE 里加（第六步细讲）。

思考：为什么 post 表只放一个 authorId（作者的 id），而不是把 authorName、authorAvatar 都塞进来？——这就是关系数据库的规范化（normalization）。如果把作者信息冗余进帖子表，用户改名时就得更新他名下所有帖子，漏一条就数据不一致。规范化的做法是「作者信息只在 user 表里存一份，post 只存一个指向 user.id 的引用」，改名只改一处，所有帖子自动反映新名字（JOIN 查询时带出）。代价是查详情要多 JOIN 一次 user 表，但这个代价远小于数据不一致——所以 learnhub 选规范化。

第五步：索引——UNIQUE 唯一约束与联合主键

索引是 MySQL 用来加速查询的附加数据结构。没有索引，WHERE username = 'admin' 得从第一行扫到最后一行（全表扫描）；有了索引，B+ 树一查就到。learnhub 的 Init 里有两类索引值得专门讲。

第一类：UNIQUE 唯一索引。user 表的 username 列后面这一行：

// learnhub/src/migrations/1784362383603-Init.ts（user 表内）
UNIQUE INDEX \`IDX_78a916df40e02a9deb1c4b75ed\` (\`username\`),

这条索引干两件事：一是加速按用户名查询（登录时 WHERE username = ?）；二是保证唯一——同样用户名插第二条会直接报错，数据库层面挡住重名。这就是「唯一约束」：业务层校验（注册时先查有没有同名）不可靠——并发下两个请求同时查都查不到、同时插入——唯一可靠的是数据库的 UNIQUE 约束。learnhub 的 tag.name、role.name、permission.name 都是 UNIQUE，道理一样：这些字段天生不该重复。

那个 IDX_78a916df40e02a9deb1c4b75ed 是 TypeORM 自动生成的索引名（hash 命名），是机器 diff 的产物。生产里为了运维友好可以手动起名（比如 uk_user_username），learnhub 没特意改，知道它是 user.username 的唯一索引就行。

第二类：联合主键（复合主键）。多对多关系的中间表是典型场景。learnhub 的 post_tag 连接帖子和标签：

// learnhub/src/migrations/1784362383603-Init.ts
await queryRunner.query(`CREATE TABLE \`post_tag\` (
  \`postId\` int NOT NULL,
  \`tagId\` int NOT NULL,
  INDEX \`IDX_444c1b4f6cd7b632277f557935\` (\`postId\`),
  INDEX \`IDX_346168a19727fca1b1835790a1\` (\`tagId\`),
  PRIMARY KEY (\`postId\`, \`tagId\`)
) ENGINE=InnoDB`);

PRIMARY KEY (\postId`, `tagId`)是**联合主键**——两列合起来当主键。含义是：(postId=1, tagId=2) 和 (postId=1, tagId=3) 是两条不同的合法记录，但 (postId=1, tagId=2) 不能出现两次。这正好对应「一个帖子可以打多个标签、一个标签可以被多个帖子用、但同一对组合不重复」的多对多语义。中间表不需要自己的id自增列——联合主键已经够当唯一标识了，加id` 反而浪费。

另外两个 INDEX IDX_xxx (postId) 和 INDEX IDX_xxx (tagId) 是普通（非唯一）索引，专门加速「按 postId 查这个帖子的所有标签」和「按 tagId 查这个标签下的所有帖子」——多对多关系两个方向都要能查，所以两个方向各加一个索引。learnhub 的 post_favorite_user（收藏）、user_role（用户-角色）、role_permission（角色-权限）都是同样的联合主键结构，模式一致。

第六步：外键——换来什么、付出什么

外键（Foreign Key）是把两个表的引用关系强制起来的约束。learnhub 的 Init 在所有 CREATE TABLE 之后，单独用一批 ALTER TABLE ... ADD CONSTRAINT FOREIGN KEY 加外键：

// learnhub/src/migrations/1784362383603-Init.ts
await queryRunner.query(`ALTER TABLE \`post\` ADD CONSTRAINT \`FK_c6fb082a3114f35d0cc27c518e0\`
  FOREIGN KEY (\`authorId\`) REFERENCES \`user\`(\`id\`)
  ON DELETE NO ACTION ON UPDATE NO ACTION`);

这条 SQL 的意思是：post.authorId 这一列的值，必须在 user.id 里能找到，否则插入或更新直接报错。换来的是引用完整性（referential integrity）——不可能存在一条 authorId = 999 的帖子，但 user 表里没有 id=999 的用户。没有外键的话，应用 bug 写脏了数据（删了用户但他的帖子还在，详情页一 JOIN author 就是空），全靠人肉排查。

ON DELETE NO ACTION 是删除策略——删 user 时，如果还有他的 post，不允许删（报错）。常见的几种策略：

NO ACTION / RESTRICT：禁止删（默认，最安全）。
CASCADE：级联删——删用户把他的帖子也一起删。
SET NULL：把引用置空（外键列必须可空）。

learnhub 里 comment.postId → post.id 用 NO ACTION（删帖子前要先处理它的评论），post_tag.postId → post.id 用 CASCADE（删帖子时中间表自动清理）——策略按业务语义选，不是随手抄。

注意（外键的代价）：外键不是免费的。每次插入或更新带外键的表，MySQL 都要查一次父表确认引用存在；删父记录时要检查子表有没有引用。高并发写入系统（电商订单、社交 feed）往往主动拆掉外键，把引用完整性交给应用层（代码里先查再写、或用事务保证），换取写入吞吐——外键的检查开销在高并发下会成为瓶颈。learnhub 是教学项目，保留外键是为了让你看清关系结构；真做高并发系统时，这是个常见的取舍点，不是非黑即白。

第七步：所有表怎么连起来——一张关系图

把 Init 里 10 张表的引用关系画出来，learnhub 的核心数据模型长这样：

读这张图的方法：单向箭头是「多对一」（箭头指向「一」的一方），双向箭头是「多对多」（靠中间表实现）。comment 的自引用（parentId → comment.id）是实现评论的树形回复——一条评论可以有父评论，父评论也可以有父评论，无限嵌套。

注意：表结构是地基。地基没画好，后面 TypeORM 的 Entity、Service 的查询、Controller 的接口全都会别扭——忘了给 username 加唯一索引，注册接口就得自己加锁防重名；把 content 设计成 varchar(255)，帖子正文一长就截断。这一章看的 10 张表，是后面每一章业务代码的容器。

可选侧栏：绝对零基础——跑一条 SELECT 看看数据

如果你完全没碰过 SQL，下面这条命令让你直观感受「数据库里真的有数据」。这一节是给纯新手的可选补充，主线路径不看也能继续。

learnhub 跑起来并执行过 npm run migration:run 后（建表 + 灌种子），库里已经有数据。进 MySQL 容器跑一条裸 SELECT：

# 进 learnhub 的 MySQL 容器
docker exec -it learnhub-mysql mysql -ulearnhub -plearnhub_2024 learnhub

# 在 mysql> 提示符里执行
mysql> SELECT id, username, create_time FROM user;
+----+----------+---------------------+
| id | username | create_time         |
+----+----------+---------------------+
|  1 | admin    | 2026-07-19 10:00:00 |
+----+----------+---------------------+

mysql> SELECT id, title, authorId FROM post LIMIT 5;

SELECT 列1, 列2 FROM 表名 是最基础的查询，LIMIT 5 限制只取前 5 条。看到行就说明种子数据进库了。完整的 INSERT/UPDATE/DELETE、WHERE/ORDER BY/JOIN 不在这里铺开——learnhub 的真实业务代码里全是 QueryBuilder 生成的 SQL，打开 start:dev 的日志（第八章会讲怎么开）能直接看到，比孤立地背 SQL 语法高效。退出 mysql CLI 是 \q 或 exit。

这一章的成果

在 learnhub 的 Init 迁移里读懂了 10 张真实表：列类型（int / varchar / text / tinyint / datetime(6)）、NOT NULL 与默认值、AUTO_INCREMENT 主键、ENGINE=InnoDB。
理清两类索引：UNIQUE INDEX（用户名/标签名等唯一约束）和联合主键（post_tag、user_role 等中间表的复合主键）。
看懂外键的 ALTER TABLE ADD CONSTRAINT FOREIGN KEY——换来引用完整性，付出写入检查成本，高并发系统会权衡拆掉。
搞懂连接配置里 charset: 'utf8mb4'（不是 utf8，否则存不下 emoji）和 timezone: '+08:00' 的为什么。
有了「核心持久数据放 MySQL、高频临时放 Redis、弱结构海量写放 Mongo」的存储选型判断。

常见问题

utf8 和 utf8mb4 有什么区别：MySQL 的 utf8 是 3 字节截断版，存不下 4 字节的 emoji 和部分生僻字；utf8mb4 才是完整 UTF-8。永远用 utf8mb4。
外键到底该不该加：业务复杂度一般、并发不高 → 加，换引用完整性；高并发写入、分库分表 → 拆掉，应用层保证。learnhub 为了讲清关系结构保留了外键。
主键用自增 int 还是 UUID：单库单机用 int AUTO_INCREMENT 最简单、性能好（顺序写、无页分裂）；分布式或多库合并用雪花算法（snowflake）避免冲突；UUID 索引性能差（随机写），一般不用作主键。
中间表要不要自己的 id：不需要。联合主键 (postId, tagId) 已经唯一标识一行，加 id 是冗余。只有当中间表要挂自己的属性（比如「收藏时间」）时，才考虑加额外字段。
时区不对、时间差 8 小时：连接配置加 timezone: '+08:00'、容器环境变量加 TZ: Asia/Shanghai（learnhub 都加了）。
docker 起的 MySQL 连不上：docker ps 确认在跑；docker exec learnhub-mysql mysqladmin ping -plearnhub_root_2024 看是否就绪（MySQL 首次初始化要几十秒）；端口 3306 没被本机别的 MySQL 占。

下一章把 MySQL 接进 Nest——用 TypeORM 把这一章的表映射成 Entity，在 learnhub 真实的 PostService 里讲透 Repository、分页、事务、404、所有权校验，以及生产必备的 migration 工作流。