Node系列 · 数据库：数据库设计

数据库设计的核心是：用合理的表结构存储数据，并用约束保证数据不脏不漏。本章覆盖 SQL 三分支、库表操作、主外键、三种表关系、三大范式——掌握这些就能独立设计中等规模业务库。

一、SQL 三分支回顾

分支	用途	关键字
DDL（Data Definition Language）	库 / 表 / 字段结构	`CREATE` / `DROP` / `ALTER`
DML（Data Manipulation Language）	行级操作	`INSERT` / `UPDATE` / `DELETE`
DCL（Data Control Language）	权限	`GRANT` / `REVOKE`

二、管理库

2.1 创建数据库

sql

CREATE DATABASE `test` DEFAULT CHARACTER SET utf8mb4;

反引号 ` 包名是好习惯，避免和关键字冲突
DEFAULT CHARACTER SET utf8mb4 保证默认编码支持中文和 emoji

2.2 删除数据库

sql

DROP DATABASE `test`;

DANGER

DROP 不可逆——所有表和数据一起删。生产环境几乎不会用此命令。

2.3 查看 / 切换数据库

sql

SHOW DATABASES;
USE `test`;
SELECT DATABASE();  -- 当前所在数据库

三、管理表

3.1 创建表

sql

CREATE TABLE `test`.`student` (
  `id`        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `stuno`     VARCHAR(20)  NOT NULL,
  `name`      VARCHAR(50)  NOT NULL,
  `sex`       BIT(1)       NOT NULL DEFAULT b'0',
  `phone`     VARCHAR(20)  DEFAULT NULL,
  `birthday`  DATE         DEFAULT NULL,
  `created_at` DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stuno` (`stuno`),
  KEY `idx_phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

字段类型速查：

类型	用途	示例
`INT` / `BIGINT`	整数	`id INT UNSIGNED`
`VARCHAR(n)`	变长字符串	`name VARCHAR(50)`
`CHAR(n)`	定长字符串	`code CHAR(6)`
`TEXT`	长文本	文章内容
`DECIMAL(m,n)`	精确小数	`price DECIMAL(10,2)`
`DATETIME` / `TIMESTAMP`	日期时间	`created_at TIMESTAMP`
`BIT(1)`	布尔（0/1）	`is_active BIT(1)`
`JSON`	JSON 数据（MySQL 5.7+）	`metadata JSON`

3.2 修改表

加字段、删字段、改字段、改类型——都用 ALTER TABLE：

sql

-- 加字段
ALTER TABLE `student`
  ADD COLUMN `email` VARCHAR(100) NULL AFTER `phone`;

-- 改字段类型
ALTER TABLE `student`
  MODIFY COLUMN `name` VARCHAR(100) NOT NULL;

-- 改字段名 + 类型
ALTER TABLE `student`
  CHANGE COLUMN `phone` `mobile` VARCHAR(20) NULL;

-- 删字段
ALTER TABLE `student`
  DROP COLUMN `birthday`;

-- 改表名
ALTER TABLE `student` RENAME TO `students`;

3.3 删除表

sql

DROP TABLE `student`;

四、主键与外键

4.1 主键（Primary Key）

主键 = 唯一标识一行数据的字段（或字段组合）。

设计原则：

每张表都要有主键（InnoDB 引擎强制要求）
主键值唯一、NOT NULL、永不修改
推荐用自增整数（BIGINT AUTO_INCREMENT）或雪花 ID
不要用业务字段（身份证号、订单号）做主键——业务会变

sql

PRIMARY KEY (`id`)

4.2 外键（Foreign Key）

外键 = 一个表里的字段，引用另一个表的主键。作用：

保证引用完整性（不会引用不存在的行）
自动级联（删除/更新父表记录时联动子表）

sql

CREATE TABLE `score` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `student_id` INT UNSIGNED NOT NULL,
  `subject`    VARCHAR(50)  NOT NULL,
  `score`      DECIMAL(5,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_student` (`student_id`),
  CONSTRAINT `fk_score_student`
    FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

WARNING

生产项目里慎用外键约束。理由：

每次写入都要检查一致性，性能开销
分布式系统下跨库外键无法落地
应用层用事务控制引用完整性更灵活

五、表之间三种关系

5.1 一对一

A 表的一行对应 B 表的一行。常用于"主表 + 扩展表"拆分：

text

user (id, name, email)
user_profile (user_id PK, avatar, bio)  -- user_id 既是主键也是外键

5.2 一对多

A 表的一行对应 B 表的多行。最常见：

text

class (id, name)
  └─ student (id, class_id → class.id)

student.class_id 是外键，引用 class.id。

5.3 多对多

A 表的一行对应 B 表的多行，反之亦然。必须借助中间表：

text

student (id, name)
course  (id, title)
  └─ student_course (student_id, course_id, score)

student_course 是中间表，student_id 和 course_id 组合为主键（联合主键）。

六、三大设计范式

范式是数据库设计的"卫生标准"。遵守得越严格，数据冗余越少，但查询复杂度越高。

第一范式（1NF）：字段不可分割

sql

-- ❌ 违反 1NF：address 字段可拆分成省市区
name VARCHAR(50), address VARCHAR(200)  -- "广东省深圳市南山区..."

-- ✅ 符合 1NF
name VARCHAR(50),
province VARCHAR(20),
city VARCHAR(20),
district VARCHAR(20)

第二范式（2NF）：非主键列必须依赖整个主键

针对联合主键：

sql

-- ❌ 违反 2NF：student_name 只依赖 student_id，不依赖 subject
PRIMARY KEY (student_id, subject),
student_name VARCHAR(50),  -- 只依赖 student_id
score DECIMAL(5,2)         -- 依赖整个主键

-- ✅ 符合 2NF：拆表
student (id PK, student_name)
score  (student_id, subject, score, PRIMARY KEY (student_id, subject))

第三范式（3NF）：非主键列不能传递依赖

sql

-- ❌ 违反 3NF：class_name 依赖 class_id，class_id 依赖 id（传递）
id INT PRIMARY KEY,
class_id INT,
class_name VARCHAR(50),  -- 依赖 class_id，不是直接依赖 id

-- ✅ 符合 3NF：拆出 class 表
student (id PK, class_id FK)
class   (id PK, class_name)

范式的实际取舍

TIP

不要死守范式。互联网项目经常"反范式"——刻意冗余一些字段（如 student.class_name 直接存在 student 表里）来换取查询性能。范式是基础规范，不是金科玉律。

七、最佳实践

场景	推荐
表设计	每张表加 `id BIGINT AUTO_INCREMENT PRIMARY KEY` + `created_at` / `updated_at`
命名	表名小写复数（`users`），字段名小写下划线（`user_id`）
字段类型	金额用 `DECIMAL` 而非 `FLOAT`；布尔用 `TINYINT(1)` 或 `BIT(1)`
字符集	全部 `utf8mb4`（不要用 `utf8`，那是 MySQL 的 3 字节阉割版）
存储引擎	`InnoDB`（默认，唯一支持事务、外键、行锁）
主键策略	单机用自增；分布式用雪花 ID / UUID
索引	WHERE / JOIN 频繁的字段建索引；避免过度索引（写入会变慢）

八、小结

SQL 三分支：DDL（结构）/ DML（数据）/ DCL（权限）
DDL 操作库 / 表；CREATE / ALTER / DROP
主键保证行唯一；外键保证引用完整（但生产慎用）
表关系三种：一对一、一对多、多对多（多对多必须中间表）
三大范式减少冗余，但实战要权衡查询性能——反范式是常见做法
工程实践：表必带 id + created_at / updated_at、全用 utf8mb4 和 InnoDB

Node系列 · 数据库：数据库设计 ​

一、SQL 三分支回顾 ​

二、管理库 ​

2.1 创建数据库 ​

2.2 删除数据库 ​

2.3 查看 / 切换数据库 ​

三、管理表 ​

3.1 创建表 ​

3.2 修改表 ​

3.3 删除表 ​

四、主键与外键 ​

4.1 主键（Primary Key） ​

4.2 外键（Foreign Key） ​

五、表之间三种关系 ​

5.1 一对一 ​

5.2 一对多 ​

5.3 多对多 ​

六、三大设计范式 ​

第一范式（1NF）：字段不可分割 ​

第二范式（2NF）：非主键列必须依赖整个主键 ​

第三范式（3NF）：非主键列不能传递依赖 ​

范式的实际取舍 ​

七、最佳实践 ​

八、小结 ​

Node系列 · 数据库：数据库设计

一、SQL 三分支回顾

二、管理库

2.1 创建数据库

2.2 删除数据库

2.3 查看 / 切换数据库

三、管理表

3.1 创建表

3.2 修改表

3.3 删除表

四、主键与外键

4.1 主键（Primary Key）

4.2 外键（Foreign Key）

五、表之间三种关系

5.1 一对一

5.2 一对多

5.3 多对多

六、三大设计范式

第一范式（1NF）：字段不可分割

第二范式（2NF）：非主键列必须依赖整个主键

第三范式（3NF）：非主键列不能传递依赖

范式的实际取舍

七、最佳实践

八、小结