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 优缺点对比优点缺点⚡ 减少网络传输一次调用执行多条SQL❌ 调试困难没有可视化调试器✅ 提高执行效率预编译缓存❌ 移植性差MySQL/Oracle语法有差异✅ 代码复用一处定义多处调用❌ 版本管理困难✅ 提高安全性参数化防止SQL注入❌ 业务逻辑分散在DB层1.3 参数类型详解DELIMITER//CREATEPROCEDUREmanage_user(INuser_idINT,-- 输入参数只读OUTtotal_countINT,-- 输出参数INOUTuser_statusVARCHAR(20)-- 输入输出参数)BEGIN-- IN: 外部传入值过程中可读取-- OUT: 初始值为NULL可在过程内赋值供外部使用-- INOUT: 既可传入值过程内也可修改并返回SELECTCOUNT(*)INTOtotal_countFROMusersWHEREstatususer_status;SETuser_statusCONCAT(processed_,user_status);END//DELIMITER;-- 调用示例CALLmanage_user(1,count,status);SELECTcount,status;⚡面试坑点INOUT 参数容易被忽略很多人以为参数只能是输入或输出实际上 MySQL 支持既输入又输出的参数。1.4 创建、调用与删除-- 创建存储过程CREATEPROCEDUREget_user_info(INuidINT)BEGINSELECT*FROMusersWHEREiduid;END;-- 调用存储过程CALLget_user_info(10);-- 删除存储过程DROPPROCEDUREIFEXISTSget_user_info;-- 查看存储过程列表SHOWPROCEDURESTATUSLIKEget_%;⚡高频面试题存储过程与预编译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 六种触发器类型类型触发时机典型场景BEFORE INSERT插入前数据校验、修改待插入值AFTER INSERT插入后关联数据同步、日志记录BEFORE UPDATE更新前变更校验AFTER UPDATE更新后审计追踪BEFORE DELETE删除前关联数据检查AFTER DELETE删除后级联清理、数据归档2.3 实际应用场景场景1自动填充创建/更新时间CREATETRIGGERtrg_orders_insert BEFOREINSERTONordersFOR EACH ROWBEGINSETNEW.create_timeNOW();SETNEW.statusIFNULL(NEW.status,pending);END;场景2订单创建时自动扣减库存CREATETRIGGERtrg_order_createAFTERINSERTONordersFOR EACH ROWBEGINUPDATEproductsSETstockstock-NEW.quantityWHEREidNEW.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 触发器只能使用NEWDELETE 触发器只能使用OLDUPDATE 触发器两者都可使用三、视图3.1 什么是视图视图是基于 SQL 查询结果的虚拟表本身不存储数据查询时动态生成结果。-- 创建视图CREATEVIEWv_user_ordersASSELECTu.id,u.name,COUNT(o.id)ASorder_countFROMusers uLEFTJOINorders oONu.ido.user_idGROUPBYu.id,u.name;-- 使用视图SELECT*FROMv_user_ordersWHEREorder_count5;3.2 视图 vs 表 对比特性视图表存储数据❌ 不存储动态生成✅ 物理存储本质SELECT 语句定义数据结构增删改⚠️ 受限✅ 完全支持可建索引❌ 不能建✅ 可以建执行速度每次查询时执行直接访问⚡面试高频题视图的优点有哪些简化复杂查询封装 JOIN、聚合等复杂逻辑数据安全隐藏敏感列只暴露必要字段逻辑独立性修改表结构不影响应用层复用性一处定义多处使用3.3 创建、使用与删除-- 创建视图带加密CREATEALGORITHMMERGEVIEWv_emp_detailsASSELECTe.name,d.dept_name,e.salaryFROMemployees eJOINdepartments dONe.dept_idd.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,ageFROMusersWHEREage18;-- 可以执行 INSERT/UPDATE/DELETEUPDATEv_adult_usersSETage20WHEREid1;可更新的必要条件不包含GROUP BY、DISTINCT、HAVING不包含聚合函数SUM、COUNT、AVG等不包含UNION、UNION ALLFROM 子句不包含子查询不包含多表连接单表视图通常可更新4.2 WITH CHECK OPTION这个选项确保通过视图插入或更新的数据对视图可见防止窗外数据的出现。-- 只允许插入/更新 age 18 的数据CREATEVIEWv_adult_usersASSELECTid,name,ageFROMusersWHEREage18WITHCHECKOPTION;-- ✅ 成功age 25满足 age 18INSERTINTOv_adult_usersVALUES(1,张三,25);-- ❌ 失败违反 CHECK OPTIONage 16不满足 age 18INSERTINTOv_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_idd.id;-- MySQL 中多表视图通常不可更新五、用户创建与授权5.1 基础操作-- 创建用户CREATEUSERusernamehostIDENTIFIEDBYpassword;-- host: localhost本地、%任意主机、具体IP-- 授权GRANTprivilege_typeONdatabase.tableTOusernamehost;-- 刷新权限重要FLUSHPRIVILEGES;5.2 权限类型一览权限类型说明ALL PRIVILEGES所有权限SELECT, INSERT, UPDATE, DELETE基础DML权限CREATE, DROP, ALTERDDL权限REFERENCES外键权限INDEX索引管理权限EXECUTE执行存储过程/函数5.3 三种用户创建示例-- 示例1应用开发账号只能读写指定数据库CREATEUSERapp_user%IDENTIFIEDBYApp2024;GRANTSELECT,INSERT,UPDATE,DELETEONmyapp.*TOapp_user%;-- 示例2数据分析账号只读CREATEUSERanalystlocalhostIDENTIFIEDBYAna2024;GRANTSELECTONmyapp.*TOanalystlocalhost;-- 示例3管理员账号所有权限CREATEUSERdbalocalhostIDENTIFIEDBYDBA2024;GRANTALLPRIVILEGESON*.*TOdbalocalhostWITHGRANTOPTION;⚡面试坑点创建用户后必须FLUSH PRIVILEGES才能生效除非用 GRANT 自动刷新WITH GRANT OPTION允许该用户授权给其他人生产环境慎用六、权限管理6.1 权限查看与撤销-- 查看用户所有权限SHOWGRANTSFORapp_user%;-- 撤销指定权限REVOKEINSERT,UPDATEONmyapp.*FROMapp_user%;-- 撤销所有权限保留用户REVOKEALLPRIVILEGES,GRANTOPTIONFROMapp_user%;-- 删除用户DROPUSERapp_user%;-- 修改密码ALTERUSERapp_user%IDENTIFIEDBYNewPass2024;6.2 四级权限范围范围语法说明全局级别*.*所有数据库和表数据库级别db_name.*某个数据库的所有对象表级别db_name.table_name某张表列级别授予时指定列名某表的特定列很少用-- 全局所有数据库的所有表GRANTSELECTON*.*TOreader%;-- 数据库myapp库的所有表GRANTALLONmyapp.*TOdeveloperlocalhost;-- 表myapp库的users表GRANTSELECT,UPDATEONmyapp.usersTOsupport%;-- 列很少用GRANTSELECT,UPDATE(id,name)ONmyapp.usersTOlimited_user%;⚡面试高频题如何最小权限原则按需授权只给必要的最小权限集。应用账号通常只需要 SELECT/INSERT/UPDATE/DELETE业务 DBA 需要 DDL 权限管理员才需要 ALL。七、角色管理MySQL 8.07.1 角色基础操作MySQL 8.0 引入了**角色ROLE**概念类似于权限组简化权限管理。-- 创建角色CREATEROLEapp_developer,app_read,app_write;-- 为角色授权GRANTSELECTONmyapp.*TOapp_read;GRANTSELECT,INSERT,UPDATE,DELETEONmyapp.*TOapp_write;GRANTALLONmyapp.*TOapp_developer;-- 为用户分配角色GRANTapp_readTOdev1localhost;GRANTapp_developerTOdbalocalhost;7.2 激活与默认角色-- 设置默认角色用户登录时自动激活SETDEFAULTROLEapp_readFORdev1localhost;-- 手动激活角色SETROLEapp_read;SETROLEapp_write;-- 查看当前会话激活的角色SELECTCURRENT_ROLE();⚡面试坑点MySQL 的角色是激活后才生效的不像 Oracle 自动激活。需要用SET DEFAULT ROLE或每次登录后SET ROLE才能使用角色权限。7.3 撤销与删除-- 从用户撤销角色REVOKEapp_readFROMdev1localhost;-- 删除角色DROPROLEapp_read;-- 角色权限也会被同步撤销八、三大范式8.1 第一范式1NF—— 原子性定义每个列都是不可分割的原子值。反例违反1NFidnamephone1张三13800138000, 010-12345678正例符合1NFidnamephone_mainphone_office1张三1380013800001012345678⚡面试题为什么需要原子性原子性保证数据的最小不可分原则。非原子列难以精确查询、统计和关联。比如查询北京的用户如果地址是北京市朝阳区XX就无法直接筛选。8.2 第二范式2NF—— 消除部分依赖定义在满足1NF的基础上非主键列必须完全依赖于主键不能只依赖主键的一部分。反例违反2NForder_idproduct_idorder_timeproduct_name11002024-01-01手机主键是 (order_id, product_id)但 product_name 只依赖 product_id不依赖 order_id——这是部分依赖。正例符合2NF订单表(订单ID, 订单时间, 客户ID) 商品表(商品ID, 商品名称, 价格) 订单明细表(订单ID, 商品ID, 数量)⚡面试高频题复合主键的场景下才可能违反2NF单一主键的表天然满足2NF。8.3 第三范式3NF—— 消除传递依赖定义在满足2NF的基础上非主键列之间不能存在传递依赖。反例违反3NFstudent_idnamedepartmentdept_phone1张三计算机系010-12345678student_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 适用场景场景说明读多写少频繁查询的字段可冗余避免 JOIN高并发优化减少 JOIN 开销降低锁竞争报表/统计预计算汇总数据定时刷新缓存层将热点数据冗余到业务表9.3 注意事项⚡核心原则反范式必须配套一致性保证机制-- 方案1应用层保证一致性UPDATEcustomersSETname新名称WHEREid?;UPDATEordersSETcustomer_name新名称WHEREcustomer_id?;-- 同步更新冗余-- 方案2触发器保证一致性CREATETRIGGERtrg_sync_nameAFTERUPDATEONcustomersFOR EACH ROWBEGINUPDATEordersSETcustomer_nameNEW.nameWHEREcustomer_idNEW.id;END;-- 方案3定时任务同步-- 每日凌晨执行数据一致性检查和同步⚡面试高频题范式 vs 反范式如何选择原则是先范式再根据性能需求适度反范式。一般业务系统满足3NF即可在核心查询场景如报表、列表页适当冗余。十、表设计原则10.1 六条最佳实践1. 使用逻辑自增主键idBIGINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY优点插入快、占用小、查询快。UUID虽全局唯一但插入性能差。2. 字段设为 NOT NULL DEFAULTstatusTINYINTNOTNULLDEFAULT1,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_active10.2 命名规范速查类型规范示例表名小写下划线单数user, order_detail主键id 或 表名_idid, user_id外键关联表_idclass_id, product_id时间字段_time / _atcreate_time, updated_at状态字段is_/statusis_deleted, status索引前缀idx_ / uk_ / fk_idx_name, uk_email 思维导图速览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 ├── 控制表宽度 ├── 合理外键 ├── 预估数据量 └── 统一命名规范写在最后动手实践每个知识点都亲手敲一遍 SQL比看十遍文档记得牢理解原理面试官更关注为什么比如为什么要用视图、“什么时候反范式”对比记忆视图 vs 表、存储过程 vs 触发器、范式 vs 反范式——对比学习效果翻倍关注版本MySQL 8.0 引入了角色管理、窗口函数等新特性部分面试会考察结合业务学习这些知识时多思考实际业务场景比如电商系统如何设计库存表、“用户表如何避免查询慢”