企业官网建设流程全解析

MySQL知识梳理(6)——数据库对象与设计

作者：没有四次元口袋的蓝胖
日期：2026-06-24
标签：MySQL, 存储过程, 视图, 权限, 设计范式

一、存储过程

1.1 什么是存储过程？

存储过程是预编译的 SQL 代码块，存储在数据库中，可接受参数并返回值。你可以把它理解为数据库端的"函数"。

-- 基本语法DELIMITER//CREATEPROCEDUREproc_name([IN|OUT|INOUT]param_name param_type)BEGIN-- SQL 语句END//DELIMITER;

1.2 优缺点对比

1.3 参数类型详解

DELIMITER//CREATEPROCEDUREmanage_user(INuser_idINT,-- 输入参数，只读OUTtotal_countINT,-- 输出参数INOUTuser_statusVARCHAR(20)-- 输入输出参数)BEGIN-- IN: 外部传入值，过程中可读取-- OUT: 初始值为NULL，可在过程内赋值供外部使用-- INOUT: 既可传入值，过程内也可修改并返回SELECTCOUNT(*)INTOtotal_countFROMusersWHEREstatus=user_status;SETuser_status=CONCAT('processed_',user_status);END//DELIMITER;-- 调用示例CALLmanage_user(1,@count,@status);SELECT@count,@status;

⚡面试坑点：INOUT 参数容易被忽略，很多人以为参数只能是输入或输出，实际上 MySQL 支持既输入又输出的参数。

1.4 创建、调用与删除

-- 创建存储过程CREATEPROCEDUREget_user_info(INuidINT)BEGINSELECT*FROMusersWHEREid=uid;END;-- 调用存储过程CALLget_user_info(10);-- 删除存储过程DROPPROCEDUREIFEXISTSget_user_info;-- 查看存储过程列表SHOWPROCEDURESTATUSLIKE'get_%';

⚡高频面试题：存储过程与预编译SQL有什么区别？

存储过程是一组预编译SQL的集合，保存在数据库端；而普通预编译SQL是单条语句的预编译。存储过程减少了网络往返次数，但业务逻辑耦合在数据库层不利于维护。

二、触发器

2.1 什么是触发器？

触发器是当特定事件发生时自动执行的 SQL 集合。事件可以是 INSERT、UPDATE、DELETE，操作时机可以是 BEFORE 或 AFTER。

CREATETRIGGERtrigger_name {BEFORE|AFTER} {INSERT|UPDATE|DELETE}ONtable_nameFOR EACH ROWBEGIN-- 触发器逻辑END;

2.2 六种触发器类型

2.3 实际应用场景

场景1：自动填充创建/更新时间

CREATETRIGGERtrg_orders_insert BEFOREINSERTONordersFOR EACH ROWBEGINSETNEW.create_time=NOW();SETNEW.status=IFNULL(NEW.status,'pending');END;

场景2：订单创建时自动扣减库存

CREATETRIGGERtrg_order_createAFTERINSERTONordersFOR EACH ROWBEGINUPDATEproductsSETstock=stock-NEW.quantityWHEREid=NEW.product_id;END;

场景3：数据归档（删除用户时保留历史记录）

CREATETRIGGERtrg_user_delete BEFOREDELETEONusersFOR EACH ROWBEGININSERTINTOusers_archive(id,name,email,deleted_at)VALUES(OLD.id,OLD.name,OLD.email,NOW());END;

⚡面试坑点：OLD 和 NEW 关键字是触发器特有的引用方式：

• INSERT 触发器只能使用NEW
• DELETE 触发器只能使用OLD
• UPDATE 触发器两者都可使用

三、视图

3.1 什么是视图？

视图是基于 SQL 查询结果的虚拟表，本身不存储数据，查询时动态生成结果。

-- 创建视图CREATEVIEWv_user_ordersASSELECTu.id,u.name,COUNT(o.id)ASorder_countFROMusers uLEFTJOINorders oONu.id=o.user_idGROUPBYu.id,u.name;-- 使用视图SELECT*FROMv_user_ordersWHEREorder_count>5;

3.2 视图 vs 表 对比

⚡面试高频题：视图的优点有哪些？

1. 简化复杂查询：封装 JOIN、聚合等复杂逻辑
2. 数据安全：隐藏敏感列，只暴露必要字段
3. 逻辑独立性：修改表结构不影响应用层
4. 复用性：一处定义多处使用

3.3 创建、使用与删除

-- 创建视图（带加密）CREATEALGORITHM=MERGEVIEWv_emp_detailsASSELECTe.name,d.dept_name,e.salaryFROMemployees eJOINdepartments dONe.dept_id=d.id;-- 创建只读视图CREATEVIEWv_emp_readonlyASSELECT*FROMemployeesWITHCHECKOPTION;-- 使用视图SELECT*FROMv_emp_detailsWHEREdept_name='技术部';-- 删除视图DROPVIEWIFEXISTSv_emp_details;-- 查看视图结构DESCRIBEv_emp_details;SHOWCREATEVIEWv_emp_details;

四、视图的增删改

4.1 可更新视图的条件

视图不是"镜中花"，满足以下条件时可以更新数据：

-- ✅ 可更新的简单视图CREATEVIEWv_adult_usersASSELECTid,name,ageFROMusersWHEREage>=18;-- 可以执行 INSERT/UPDATE/DELETEUPDATEv_adult_usersSETage=20WHEREid=1;

可更新的必要条件：

1. 不包含GROUP BY、DISTINCT、HAVING
2. 不包含聚合函数（SUM、COUNT、AVG等）
3. 不包含UNION、UNION ALL
4. FROM 子句不包含子查询
5. 不包含多表连接（单表视图通常可更新）

4.2 WITH CHECK OPTION

这个选项确保通过视图插入或更新的数据对视图可见，防止"窗外数据"的出现。

-- 只允许插入/更新 age >= 18 的数据CREATEVIEWv_adult_usersASSELECTid,name,ageFROMusersWHEREage>=18WITHCHECKOPTION;-- ✅ 成功（age = 25，满足 age >= 18）INSERTINTOv_adult_usersVALUES(1,'张三',25);-- ❌ 失败！违反 CHECK OPTION（age = 16，不满足 age >= 18）INSERTINTOv_adult_usersVALUES(2,'李四',16);

⚡面试高频题：WITH CHECK OPTION 的作用是什么？

防止通过视图插入或更新不符合视图 WHERE 条件的数据。如果没有这个选项，你可以通过视图插入"窗外数据"——即对视图不可见的数据。

4.3 不可更新视图示例

-- ❌ 不可更新：包含聚合函数CREATEVIEWv_user_countASSELECTdepartment,COUNT(*)AScntFROMusersGROUPBYdepartment;-- 无法通过此视图增删改数据-- ❌ 不可更新：包含多表连接CREATEVIEWv_emp_deptASSELECTe.name,d.dept_nameFROMemployees eJOINdepartments dONe.dept_id=d.id;-- MySQL 中多表视图通常不可更新

五、用户创建与授权

5.1 基础操作

-- 创建用户CREATEUSER'username'@'host'IDENTIFIEDBY'password';-- host: localhost（本地）、%（任意主机）、具体IP-- 授权GRANTprivilege_typeONdatabase.tableTO'username'@'host';-- 刷新权限（重要！）FLUSHPRIVILEGES;

5.2 权限类型一览

5.3 三种用户创建示例

-- 示例1：应用开发账号（只能读写指定数据库）CREATEUSER'app_user'@'%'IDENTIFIEDBY'App@2024';GRANTSELECT,INSERT,UPDATE,DELETEONmyapp.*TO'app_user'@'%';-- 示例2：数据分析账号（只读）CREATEUSER'analyst'@'localhost'IDENTIFIEDBY'Ana@2024';GRANTSELECTONmyapp.*TO'analyst'@'localhost';-- 示例3：管理员账号（所有权限）CREATEUSER'dba'@'localhost'IDENTIFIEDBY'DBA@2024';GRANTALLPRIVILEGESON*.*TO'dba'@'localhost'WITHGRANTOPTION;

⚡面试坑点：

• 创建用户后必须FLUSH PRIVILEGES才能生效（除非用 GRANT 自动刷新）
• WITH GRANT OPTION允许该用户授权给其他人，生产环境慎用

六、权限管理

6.1 权限查看与撤销

-- 查看用户所有权限SHOWGRANTSFOR'app_user'@'%';-- 撤销指定权限REVOKEINSERT,UPDATEONmyapp.*FROM'app_user'@'%';-- 撤销所有权限（保留用户）REVOKEALLPRIVILEGES,GRANTOPTIONFROM'app_user'@'%';-- 删除用户DROPUSER'app_user'@'%';-- 修改密码ALTERUSER'app_user'@'%'IDENTIFIEDBY'NewPass@2024';

6.2 四级权限范围

-- 全局：所有数据库的所有表GRANTSELECTON*.*TO'reader'@'%';-- 数据库：myapp库的所有表GRANTALLONmyapp.*TO'developer'@'localhost';-- 表：myapp库的users表GRANTSELECT,UPDATEONmyapp.usersTO'support'@'%';-- 列（很少用）GRANTSELECT,UPDATE(id,name)ONmyapp.usersTO'limited_user'@'%';

⚡面试高频题：如何最小权限原则？

按需授权，只给必要的最小权限集。应用账号通常只需要 SELECT/INSERT/UPDATE/DELETE，业务 DBA 需要 DDL 权限，管理员才需要 ALL。

七、角色管理（MySQL 8.0+）

7.1 角色基础操作

MySQL 8.0 引入了**角色（ROLE）**概念，类似于"权限组"，简化权限管理。

-- 创建角色CREATEROLE'app_developer','app_read','app_write';-- 为角色授权GRANTSELECTONmyapp.*TO'app_read';GRANTSELECT,INSERT,UPDATE,DELETEONmyapp.*TO'app_write';GRANTALLONmyapp.*TO'app_developer';-- 为用户分配角色GRANT'app_read'TO'dev1'@'localhost';GRANT'app_developer'TO'dba'@'localhost';

7.2 激活与默认角色

-- 设置默认角色（用户登录时自动激活）SETDEFAULTROLE'app_read'FOR'dev1'@'localhost';-- 手动激活角色SETROLE'app_read';SETROLE'app_write';-- 查看当前会话激活的角色SELECTCURRENT_ROLE();

⚡面试坑点：MySQL 的角色是"激活后才生效"的，不像 Oracle 自动激活。需要用SET DEFAULT ROLE或每次登录后SET ROLE才能使用角色权限。

7.3 撤销与删除

-- 从用户撤销角色REVOKE'app_read'FROM'dev1'@'localhost';-- 删除角色DROPROLE'app_read';-- 角色权限也会被同步撤销

八、三大范式

8.1 第一范式（1NF）—— 原子性

定义：每个列都是不可分割的原子值。

反例（违反1NF）：

正例（符合1NF）：

⚡面试题：为什么需要原子性？

原子性保证数据的最小不可分原则。非原子列难以精确查询、统计和关联。比如查询"北京的用户"，如果地址是"北京市朝阳区XX"就无法直接筛选。

8.2 第二范式（2NF）—— 消除部分依赖

定义：在满足1NF的基础上，非主键列必须完全依赖于主键，不能只依赖主键的一部分。

反例（违反2NF）：

主键是 (order_id, product_id)，但 product_name 只依赖 product_id，不依赖 order_id——这是部分依赖。

正例（符合2NF）：

订单表(订单ID, 订单时间, 客户ID) 商品表(商品ID, 商品名称, 价格) 订单明细表(订单ID, 商品ID, 数量)

⚡面试高频题：复合主键的场景下才可能违反2NF，单一主键的表天然满足2NF。

8.3 第三范式（3NF）—— 消除传递依赖

定义：在满足2NF的基础上，非主键列之间不能存在传递依赖。

反例（违反3NF）：

student_id → department → dept_phone 存在传递依赖：dept_phone 依赖于 department，而不是直接依赖于 student_id。

正例（符合3NF）：

学生表(学生ID, 姓名, 系别ID) 系别表(系别ID, 系别名称, 联系电话)

⚡面试题：三大范式解决了什么问题？

1NF消除重复数据；2NF消除部分依赖导致的冗余；3NF消除传递依赖。三者共同目标：减少数据冗余、避免更新异常。

九、反范式设计

9.1 什么时候需要反范式？

反范式是为了性能主动引入冗余，以空间换时间。

-- 反范式示例：在订单表中冗余客户名称CREATETABLEorders(idINTPRIMARYKEY,customer_idINT,customer_nameVARCHAR(50),-- 冗余字段，避免JOINorder_timeDATETIME,total_amountDECIMAL(10,2));

9.2 适用场景

9.3 注意事项

⚡核心原则：反范式必须配套一致性保证机制！

-- 方案1：应用层保证一致性UPDATEcustomersSETname='新名称'WHEREid=?;UPDATEordersSETcustomer_name='新名称'WHEREcustomer_id=?;-- 同步更新冗余-- 方案2：触发器保证一致性CREATETRIGGERtrg_sync_nameAFTERUPDATEONcustomersFOR EACH ROWBEGINUPDATEordersSETcustomer_name=NEW.nameWHEREcustomer_id=NEW.id;END;-- 方案3：定时任务同步-- 每日凌晨执行数据一致性检查和同步

⚡面试高频题：范式 vs 反范式如何选择？

原则是先范式，再根据性能需求适度反范式。一般业务系统满足3NF即可，在核心查询场景（如报表、列表页）适当冗余。

十、表设计原则

10.1 六条最佳实践

1. 使用逻辑自增主键

idBIGINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY

优点：插入快、占用小、查询快。UUID虽全局唯一但插入性能差。

2. 字段设为 NOT NULL + DEFAULT

statusTINYINTNOTNULLDEFAULT1,create_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP

避免 NULL 的不确定性，减少程序判断。

3. 控制单表宽度

建议单表字段 ≤ 20-30 个

字段过多考虑垂直拆分，大字段（TEXT/BLOB）单独建表。

4. 合理使用外键

-- 强一致性场景用物理外键FOREIGNKEY(class_id)REFERENCESclass(id)-- 高并发互联网场景用逻辑外键，应用层控制

5. 预估数据量，提前规划

-- 大表考虑分区PARTITIONBYRANGE(create_time)(PARTITIONp2024VALUESLESS THAN('2025-01-01'),PARTITIONp2025VALUESLESS THAN('2026-01-01'),PARTITIONpmaxVALUESLESS THAN MAXVALUE);

6. 统一命名规范

表名单数：user / order / product 字段名下划线：user_name / order_id / create_time 布尔字段前缀 is_/has_/can_：is_deleted / is_active

10.2 命名规范速查

📋 思维导图速览

MySQL核心知识——数据库对象与设计 │ ├── 📦 数据库对象 │ │ │ ├── 存储过程 │ │ ├── 预编译SQL代码块 │ │ ├── 参数类型：IN / OUT / INOUT │ │ └── 适用：批量操作、复杂业务逻辑 │ │ │ ├── 触发器 │ │ ├── 6种类型：BEFORE/AFTER × INSERT/UPDATE/DELETE │ │ ├── OLD/NEW引用 │ │ └── 场景：审计日志、级联更新、数据归档 │ │ │ └── 视图 │ ├── 虚拟表，不存储数据 │ ├── 可更新条件：简单查询、无聚合 │ ├── WITH CHECK OPTION 防窗外数据 │ └── 作用：简化查询、保护数据 │ ├── 🔐 权限管理 │ │ │ ├── 用户与授权 │ │ ├── CREATE USER + GRANT + FLUSH │ │ ├── 4级权限：全局/数据库/表/列 │ │ └── 最小权限原则 │ │ │ └── 角色（MySQL 8.0+） │ ├── CREATE ROLE + GRANT TO │ ├── SET DEFAULT ROLE │ └── 需手动激活 │ └── 🎯 设计范式 │ ├── 三大范式 │ ├── 1NF：列原子性，不可分割 │ ├── 2NF：消除部分依赖（复合主键场景） │ └── 3NF：消除传递依赖 │ ├── 反范式 │ ├── 空间换时间 │ ├── 读多写少场景 │ └── 必须配套一致性机制 │ └── 表设计原则 ├── 逻辑自增主键 ├── NOT NULL + DEFAULT ├── 控制表宽度 ├── 合理外键 ├── 预估数据量 └── 统一命名规范

写在最后

1. 动手实践：每个知识点都亲手敲一遍 SQL，比看十遍文档记得牢

2. 理解原理：面试官更关注"为什么"，比如"为什么要用视图"、“什么时候反范式”

3. 对比记忆：视图 vs 表、存储过程 vs 触发器、范式 vs 反范式——对比学习效果翻倍

4. 关注版本：MySQL 8.0 引入了角色管理、窗口函数等新特性，部分面试会考察

5. 结合业务：学习这些知识时多思考实际业务场景，比如"电商系统如何设计库存表"、“用户表如何避免查询慢”