Node系列 · 数据库数据库设计数据库设计的核心是用合理的表结构存储数据并用约束保证数据不脏不漏。本章覆盖 SQL 三分支、库表操作、主外键、三种表关系、三大范式——掌握这些就能独立设计中等规模业务库。一、SQL 三分支回顾分支用途关键字DDLData Definition Language库 / 表 / 字段结构CREATE/DROP/ALTERDMLData Manipulation Language行级操作INSERT/UPDATE/DELETEDCLData Control Language权限GRANT/REVOKE二、管理库2.1 创建数据库CREATEDATABASEtestDEFAULTCHARACTERSETutf8mb4;反引号包名是好习惯避免和关键字冲突DEFAULT CHARACTER SET utf8mb4保证默认编码支持中文和 emoji2.2 删除数据库DROPDATABASEtest;::: dangerDROP不可逆——所有表和数据一起删。生产环境几乎不会用此命令。:::2.3 查看 / 切换数据库SHOWDATABASES;USEtest;SELECTDATABASE();-- 当前所在数据库三、管理表3.1 创建表CREATETABLEtest.student(idINTUNSIGNEDNOTNULLAUTO_INCREMENT,stunoVARCHAR(20)NOTNULL,nameVARCHAR(50)NOTNULL,sexBIT(1)NOTNULLDEFAULTb0,phoneVARCHAR(20)DEFAULTNULL,birthdayDATEDEFAULTNULL,created_atDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP,PRIMARYKEY(id),UNIQUEKEYuk_stuno(stuno),KEYidx_phone(phone))ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT学生表;字段类型速查类型用途示例INT/BIGINT整数id INT UNSIGNEDVARCHAR(n)变长字符串name VARCHAR(50)CHAR(n)定长字符串code CHAR(6)TEXT长文本文章内容DECIMAL(m,n)精确小数price DECIMAL(10,2)DATETIME/TIMESTAMP日期时间created_at TIMESTAMPBIT(1)布尔0/1is_active BIT(1)JSONJSON 数据MySQL 5.7metadata JSON3.2 修改表加字段、删字段、改字段、改类型——都用ALTER TABLE-- 加字段ALTERTABLEstudentADDCOLUMNemailVARCHAR(100)NULLAFTERphone;-- 改字段类型ALTERTABLEstudentMODIFYCOLUMNnameVARCHAR(100)NOTNULL;-- 改字段名 类型ALTERTABLEstudentCHANGECOLUMNphonemobileVARCHAR(20)NULL;-- 删字段ALTERTABLEstudentDROPCOLUMNbirthday;-- 改表名ALTERTABLEstudentRENAMETOstudents;3.3 删除表DROPTABLEstudent;四、主键与外键4.1 主键Primary Key主键 唯一标识一行数据的字段或字段组合。设计原则每张表都要有主键InnoDB 引擎强制要求主键值唯一、NOT NULL、永不修改推荐用自增整数BIGINT AUTO_INCREMENT或雪花 ID不要用业务字段身份证号、订单号做主键——业务会变PRIMARYKEY(id)4.2 外键Foreign Key外键 一个表里的字段引用另一个表的主键。作用保证引用完整性不会引用不存在的行自动级联删除/更新父表记录时联动子表CREATETABLEscore(idINTUNSIGNEDNOTNULLAUTO_INCREMENT,student_idINTUNSIGNEDNOTNULL,subjectVARCHAR(50)NOTNULL,scoreDECIMAL(5,2)NOTNULL,PRIMARYKEY(id),KEYidx_student(student_id),CONSTRAINTfk_score_studentFOREIGNKEY(student_id)REFERENCESstudent(id)ONDELETECASCADEONUPDATECASCADE)ENGINEInnoDBDEFAULTCHARSETutf8mb4;::: warning生产项目里慎用外键约束。理由每次写入都要检查一致性性能开销分布式系统下跨库外键无法落地应用层用事务控制引用完整性更灵活:::五、表之间三种关系5.1 一对一A 表的一行对应 B 表的一行。常用于主表 扩展表拆分user (id, name, email) user_profile (user_id PK, avatar, bio) -- user_id 既是主键也是外键5.2 一对多A 表的一行对应 B 表的多行。最常见class (id, name) └─ student (id, class_id → class.id)student.class_id是外键引用class.id。5.3 多对多A 表的一行对应 B 表的多行反之亦然。必须借助中间表student (id, name) course (id, title) └─ student_course (student_id, course_id, score)student_course是中间表student_id和course_id组合为主键联合主键。六、三大设计范式范式是数据库设计的卫生标准。遵守得越严格数据冗余越少但查询复杂度越高。第一范式1NF字段不可分割-- ❌ 违反 1NFaddress 字段可拆分成省市区nameVARCHAR(50),addressVARCHAR(200)-- 广东省深圳市南山区...-- ✅ 符合 1NFnameVARCHAR(50),provinceVARCHAR(20),cityVARCHAR(20),districtVARCHAR(20)第二范式2NF非主键列必须依赖整个主键针对联合主键-- ❌ 违反 2NFstudent_name 只依赖 student_id不依赖 subjectPRIMARYKEY(student_id,subject),student_nameVARCHAR(50),-- 只依赖 student_idscoreDECIMAL(5,2)-- 依赖整个主键-- ✅ 符合 2NF拆表student(id PK,student_name)score(student_id,subject,score,PRIMARYKEY(student_id,subject))第三范式3NF非主键列不能传递依赖-- ❌ 违反 3NFclass_name 依赖 class_idclass_id 依赖 id传递idINTPRIMARYKEY,class_idINT,class_nameVARCHAR(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 KEYcreated_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主键保证行唯一外键保证引用完整但生产慎用表关系三种一对一、一对多、多对多多对多必须中间表三大范式减少冗余但实战要权衡查询性能——反范式是常见做法工程实践表必带idcreated_at/updated_at、全用utf8mb4和InnoDB