数据库基础

字数

2700 字

预计阅读

12 分钟

对前端开发者来说，数据库是后端最陌生的部分。但它也是最重要的部分：代码写错了可以改，数据写坏了往往就救不回来了。

项目使用 MySQL 8.4 及以上。

表、行、列

可以把一张表想象成一个 Excel 工作表：

id	code	name	sort_no	enabled	created_at	deleted_at
1	dev	开发	10	1	2026-09-27 08:00:00	NULL
2	qa	测试	20	1	2026-09-27 08:00:00	NULL
3	pm	产品	30	0	2026-09-27 08:00:00	2026-09-28 10:00:00

• 表（table）：iam_position，存放一类数据；
• 行（row）：一条记录，也就是一个岗位；
• 列（column）：一个字段。和 Excel 不同的是，每一列的类型都是固定的。

和前端的 JSON 相比，最大的区别是：数据库的结构是严格的。你不能随手往某一行里多塞一个字段，要加字段必须先修改表结构。

常用的列类型

MySQL 类型	用途	对应的 TS 类型
bigint unsigned	id	number
varchar(64)	短文本，最多 64 个字符	string
text	长文本	string
int	整数	number
decimal(10,2)	金额（精确的小数）	number
tinyint(1)	布尔值（0 或 1）	boolean
date	日期	string
datetime(3)	时间（精确到毫秒）	Date
json	JSON	object

金额不要用 float

float 和 double 是近似值，0.1 + 0.2 在数据库里也会出现精度问题。金额一律用 decimal。

NULL 和空字符串

NULL 表示"没有值"，和空字符串 '' 不是一回事。比如备注字段：NULL 是"没填"，'' 是"填了空内容"。定义列时要想清楚这一列允不允许为 NULL。

SQL：和数据库说话的语言

后端通过 SQL 读写数据。你不需要精通，但至少要能看懂这四种语句：

-- 查询
SELECT id, code, name FROM iam_position WHERE enabled = 1 ORDER BY sort_no LIMIT 20;

-- 新增
INSERT INTO iam_position (code, name) VALUES ('ops', '运维');

-- 修改（一定要带 WHERE！）
UPDATE iam_position SET name = '运维工程师' WHERE id = 4;

-- 删除（一定要带 WHERE！）
DELETE FROM iam_position WHERE id = 4;

警告

UPDATE 或 DELETE 忘了写 WHERE，会修改或删除整张表的数据。在数据库客户端里手动执行这类语句之前，一定要再检查一遍。

平时在项目里基本不用手写 SQL，而是用 TypeORM 生成。但调试时你会经常在日志里看到 SQL，所以要能看懂。

实体 Entity：表在代码里的样子

实体是一个 TypeScript 类，它描述了一张表的结构：

// position.entity.ts
@Entity('iam_position')                          // 对应的表名
export class Position extends BaseEntity {       // 继承 id、创建人、创建时间等通用列
  @Column({ length: 64 })
  code: string

  @Column({ name: 'sort_no', type: 'int', default: 0 })   // 数据库里叫 sort_no，代码里叫 sortNo
  sortNo: number

  @Column({ type: 'boolean', default: true })
  enabled: boolean

  @Column({ type: 'varchar', length: 500, nullable: true })
  note: string | null
}

BaseEntity 提供了每张表都有的列：

列	说明
id	主键，自动递增
created_by / created_at	谁、什么时候创建的（自动填写）
updated_by / updated_at	谁、什么时候最后修改的（自动填写）
deleted_at	删除时间，未删除为 NULL

created_by 和 updated_by 是项目自动从当前登录用户中取出并填写的，你不需要手动设置。

QueryBuilder：用代码拼 SQL

const rows = await this.scopedQb('t')          // FROM iam_position t（已经带上数据范围条件）
  .select(['t.id', 't.name'])                  // SELECT t.id, t.name
  .andWhere('t.enabled = :enabled', { enabled: true })   // WHERE t.enabled = ?
  .orderBy('t.sortNo')                         // ORDER BY t.sort_no
  .getMany()                                   // 执行查询，返回实体数组

常用的结尾方法：

方法	返回
getMany()	实体数组
getOne()	一个实体或 null
getCount()	数量
getRawMany()	原始的行数据（用于统计、联表查询）

要用 andWhere，不要用 where

scopedQb() 已经加上了数据范围条件。如果你再调用 .where(...)，会把前面的条件替换掉，数据范围就失效了。所以只能用 andWhere。

SQL 注入

这是后端最经典的安全漏洞，一定要理解：

// ❌ 危险：把用户输入直接拼进 SQL
qb.andWhere(`t.code = '${code}'`)

如果用户在搜索框里输入 ' OR '1'='1，拼出来的 SQL 就变成了：

WHERE t.code = '' OR '1'='1'      -- 永远为真，查出所有数据

更恶意的输入甚至能删除数据。正确的写法是使用参数：

// ✅ 安全：值作为参数单独传给数据库
qb.andWhere('t.code = :code', { code })

使用参数时，数据库会把 code 始终当作一个值，而不会当作 SQL 语句的一部分。项目里有检查脚本，发现字符串拼接 SQL 会直接报错。

索引：让查询变快

没有索引时，数据库要一行一行地找。表里有一百万行，就要看一百万行。

索引就像书的目录：按某一列事先排好序，查找时可以直接跳到对应的位置。

KEY idx_crm_customer_dept (dept_id)                 -- 普通索引：加快查询
UNIQUE KEY uk_crm_customer_code (code, alive)       -- 唯一索引：加快查询，并且不允许重复

什么时候要加索引：

• 经常出现在 WHERE 中的列（比如 dept_id、user_id、status）；
• 需要唯一的列（比如编码、用户名）。

索引也不是越多越好：每次写入数据时都要同时更新索引，所以很少查询的列不需要加。

事务：要么全成功，要么全失败

经典例子是转账：A 的账户减 100，B 的账户加 100。如果第一步成功了，第二步失败了，100 块钱就凭空消失了。

事务保证一组操作要么全部成功，要么全部撤销：

return this.txHost.withTransaction(async () => {
  await this.repo.update(aId, { balance: () => 'balance - 100' })
  await this.repo.update(bId, { balance: () => 'balance + 100' })
  // 这里任何一行抛出异常，前面的修改都会被撤销（回滚）
})

项目里的 create、update、remove 已经自动在事务中执行。你自己写的方法，只要修改了不止一行数据，就要放进 withTransaction。

事务内调用的其他服务方法，会自动加入同一个事务，不需要手动传递。

锁：防止两个人同时修改

两个管理员同时编辑同一条记录，或者用户快速点了两次按钮，都可能导致数据出错。

lockScopedIds(ids) 会在事务中执行 SELECT … FOR UPDATE：锁住这些行，其他事务要修改它们，必须等当前事务结束。它同时检查这些记录是否都在数据范围内。

所以按 id 修改数据的标准写法是：

return this.txHost.withTransaction(async () => {
  await this.lockScopedIds([id])         // 1. 锁住，并确认在数据范围内（否则 404）
  const row = await this.repo.findOneByOrFail({ id })   // 2. 读出最新数据
  if (!row.enabled) throw new BizError(/* … */)         // 3. 检查业务规则
  await this.repo.update(id, { /* … */ })               // 4. 修改
})

迁移：表结构的版本管理

表结构的修改（建表、加列、加索引）只能通过迁移完成，不能手动在数据库里改。

为什么？因为你的同事、测试服务器、生产服务器，各自都有一个数据库。如果你在自己的库里手动加了一列，其他人的库里没有这一列，代码一部署就会出错。

迁移就是把表结构的每一次修改都写成代码，和源码一起提交到 git：

// apps/server/src/db/migrations/20261001100000-crm-customer-vip.ts
export class CrmCustomerVip20261001100000 implements MigrationInterface {
  name = 'CrmCustomerVip20261001100000'

  async up(q: QueryRunner): Promise<void> {       // 升级：执行修改
    await q.query(`ALTER TABLE crm_customer ADD COLUMN vip_since datetime(3) NULL COMMENT '成为 VIP 的时间'`)
  }

  async down(q: QueryRunner): Promise<void> {     // 回退：撤销修改
    await q.query('ALTER TABLE crm_customer DROP COLUMN vip_since')
  }
}

执行 pnpm db:migrate 时，程序会检查 meta_migrations 表，只执行还没有执行过的迁移。每个环境各自执行一遍，所有数据库的结构就保持一致了。

已经提交的迁移不要再修改

其他环境可能已经执行过这个迁移，修改它不会再次生效。需要再改表结构，就写一个新的迁移。

改完表结构之后，记得同时更新对应的实体类。

逻辑删除

项目中的删除都是逻辑删除：不真正删除数据，而是在 deleted_at 列写入删除时间。

• 查询时 TypeORM 会自动加上 deleted_at IS NULL，已删除的数据对页面不可见；
• 数据还在库里，误删可以恢复，也方便审计；
• 过期数据由定时任务按保留天数清理。

alive 列是做什么的

逻辑删除带来一个问题：岗位编码 dev 被删除之后，数据还在表里。如果编码列上有唯一索引，就再也不能新建编码为 dev 的岗位了。

解决办法是加一个生成列 alive：

alive tinyint AS (IF(deleted_at IS NULL, 1, NULL)) VIRTUAL,
UNIQUE KEY uk_code (code, alive)

• 未删除的行，alive = 1，所以两个未删除的 dev 会冲突；
• 已删除的行，alive = NULL。MySQL 的唯一索引允许多个 NULL，所以不会和新的 dev 冲突。

alive 只在表结构中定义，实体类里不要映射它。

为什么不用外键

外键是数据库自带的"引用检查"：比如用户表的 dept_id 必须是部门表中存在的 id。

项目没有使用外键，因为它和逻辑删除配合不了：部门被逻辑删除后，那一行其实还在，外键会认为它依然有效。所以改由应用层来检查：

• 在实体上声明"谁引用了我"：referencedBy('iam_position', { table: 'iam_user_positions', column: 'position_id' })；
• 删除岗位时，如果还有用户在使用它，返回 409，并提示"数据正在被使用"。

时间和时区

• 数据库统一存储 UTC 时间，也就是零时区的时间；
• 接口返回 2026-09-27T08:00:00.000Z 这种格式（末尾的 Z 表示 UTC）；
• 前端用 dayjs 转换成用户本地的时间显示。

这样无论服务器在哪里、用户在哪个时区，时间都不会出错。

页面历史

最后编辑于 1 天前

查看完整历史

00922b8-docs: native Windows (built-in PowerShell) is the Windows path

8e0bc79-docs: follow the project-domain refactor (v0.11.1)

fc65cfe-docs(backend): backend primer for frontend developers