当前位置： 首页 > article >正文

MySQL补充知识点学习

article 2025/8/27 5:09:52

书接上文：MySQL关系型数据库学习，继续看书补充MySQL知识点学习。

1. 基本概念学习

1.1 游标（Cursor）

MySQL 游标是一种数据库对象，它允许应用程序逐行处理查询结果集，而不是一次性获取所有结果。游标在需要逐行处理数据或执行复杂业务逻辑时非常有用。

游标的主要作用和功能

  1. ​​逐行处理查询结果​​
  • 游标允许应用程序按需获取查询结果的每一行，而不是一次性加载所有数据
  • 特别适合处理大量数据时避免内存溢出
  1. ​​支持复杂的业务逻辑处理​​
  • 允许在结果集上执行复杂的业务逻辑，如条件判断、计算、更新等
  • 可以在处理每一行时执行不同的操作
  1. ​​实现逐行更新或删除​​
  • 可以结合游标对查询结果中的每一行执行更新或删除操作
  • 这在需要基于当前行内容决定如何处理下一行时非常有用
  1. ​​支持存储过程中的流程控制​​
  • 在存储过程中使用游标可以实现更复杂的流程控制
  • 可以结合条件判断、循环等语句实现复杂的业务逻辑
  1. ​​提供灵活的数据访问方式​​
  • 允许应用程序以编程方式控制数据的访问和处理
  • 可以暂停、继续或重新开始数据处理

游标的基本使用步骤

在MySQL中，游标通常与存储过程一起使用，基本使用步骤如下：
1.​​ 声明游标​​：定义要处理的查询
2.​​ 打开游标​​：执行查询并准备结果集
3.​​ 获取数据​​：逐行获取结果集中的数据
4.​​ 处理数据​​：对每一行执行所需的操作
5.​​ 关闭游标​​：释放游标资源

游标使用的具体示例

示例1：基本游标使用

DELIMITER //CREATE PROCEDURE process_employees()
BEGIN-- 1. 声明游标DECLARE emp_cursor CURSOR FOR SELECT id, name, salary FROM employees;-- 2. 声明异常处理变量DECLARE done INT DEFAULT FALSE;DECLARE emp_id INT;DECLARE emp_name VARCHAR(100);DECLARE emp_salary DECIMAL(10,2);-- 3. 声明异常处理程序DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;-- 4. 打开游标OPEN emp_cursor;-- 5. 循环获取数据read_loop: LOOPFETCH emp_cursor INTO emp_id, emp_name, emp_salary;IF done THENLEAVE read_loop;END IF;-- 6. 处理数据（这里只是打印，实际可以执行其他操作）-- 注意：MySQL存储过程中不能直接打印，这里只是示意-- 实际应用中可以执行更新、插入等操作-- SELECT CONCAT('Processing employee: ', emp_name) AS message;END LOOP;-- 7. 关闭游标CLOSE emp_cursor;
END //DELIMITER ;-- 调用存储过程
CALL process_employees();

示例2：结合条件判断和更新

DELIMITER //CREATE PROCEDURE update_salaries()
BEGIN-- 1. 声明游标DECLARE emp_cursor CURSOR FOR SELECT id, name, salary FROM employees WHERE status = 'active';-- 2. 声明异常处理变量DECLARE done INT DEFAULT FALSE;DECLARE emp_id INT;DECLARE emp_name VARCHAR(100);DECLARE emp_salary DECIMAL(10,2);-- 3. 声明异常处理程序DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;-- 4. 打开游标OPEN emp_cursor;-- 5. 循环获取数据read_loop: LOOPFETCH emp_cursor INTO emp_id, emp_name, emp_salary;IF done THENLEAVE read_loop;END IF;-- 6. 处理数据：如果工资低于5000，则增加10%IF emp_salary < 5000 THENUPDATE employees SET salary = salary * 1.1 WHERE id = emp_id;END IF;END LOOP;-- 7. 关闭游标CLOSE emp_cursor;
END //DELIMITER ;-- 调用存储过程
CALL update_salaries();

游标的特点和限制

特点：
1.​​逐行处理​​：可以逐行访问查询结果
2.​​灵活控制​​：可以控制数据处理的流程和逻辑
3.​​支持复杂逻辑​​：可以在处理每一行时执行复杂的业务逻辑
4.​​与存储过程结合​​：通常与存储过程一起使用

限制：
1.​​性能开销​​：游标会带来额外的性能开销，特别是在处理大量数据时
2.​​内存使用​​：虽然比一次性加载所有数据更节省内存，但仍会占用资源
3.​​只能用于存储过程​​：MySQL中的游标只能在存储过程中使用
4.​​不能直接用于应用程序​​：应用程序不能直接使用MySQL游标，必须通过存储过程间接使用
5.​​锁定问题​​：游标可能会锁定查询结果集中的行，影响并发性能

游标的适用场景

1.​​需要逐行处理大量数据​​：当数据量很大，一次性加载所有数据会导致内存问题时
2.​​需要基于当前行内容决定如何处理下一行​​：如复杂的业务逻辑处理
3.​​需要执行逐行更新或删除​​：根据当前行的内容决定如何处理其他行
4.​​需要实现复杂的流程控制​​：在存储过程中需要复杂的条件判断和循环
5.​​需要与外部系统交互​​：如逐行读取数据并发送到外部系统进行处理

游标与批量处理的比较

在这里插入图片描述

最佳实践

1.​​仅在必要时使用游标​​：优先考虑批量处理，只有在确实需要逐行处理时才使用游标
2.​​优化游标查询​​：确保游标使用的查询是高效的
3.​​限制游标处理的数据量​​：只处理必要的数据，避免不必要的数据处理
4.​​考虑替代方案​​：对于简单的批量操作，考虑使用批量更新或删除语句
5.​​测试性能​​：在生产环境使用前测试游标的性能影响
6.​​及时关闭游标​​：确保在不再需要时关闭游标，释放资源

MySQL游标是一个强大的工具，但应该谨慎使用，因为它会带来性能开销。在大多数情况下，批量处理是更好的选择，只有在确实需要逐行处理复杂逻辑时才使用游标。

1.2 事务（transaction）

将多个操作作为一个整体来处理的功能称为“事务”（transaction）。将开启事务之后的处理结果反馈到数据库的操作称为“提交”（commit），不反映到数据库中而恢复成原来的状态的操作称为“回滚”。

自动提交

默认情况下，也就是不手动开启事务时，MySQL的处理都是直接被提交的。也就是说，所有的操作都会自动执行commit；语句。这种功能被称为“自动提交”（auto commit）。

使用范围

下面这些操作是无法还原的，小伙伴们一定要记住。

  1. drop database
  2. drop table
  3. drop view
  4. alter table

事务的属性

事务有很严格的定义，必须同时满足4个属性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。这4个属性通常又被简称为“ACID”特性。

  1. 原子性：事务作为一个整体来执行，所有操作要么都执行，要么都不执行；
  2. 一致性：事务应确保数据库从一个一致状态转变为另一个一致状态；
  3. 隔离性：当多个事务并发执行时，一个事务的执行不影响其他事务的执行；
  4. 持久性：事务一旦提交，它对数据库的修改应该永久保存在数据库中；

1.3 表的设计原则

介绍一些常用的小技巧，以设计出更好的表，主要包括以下5个方面。

  1. 对于一个表的主键，我们一般是使用自动递增的值，而不是手动插入值；
  2. 如果一个字段只有两种取值，比如“男”或“女”、“是”或“否”，比较好的做法是使用tinyint(1)类型，而不是使用varchar等类型。当然，使用varchar等类型也是没有问题的。
  3. 如果想要保存图片，我们一般不会将图片保存到数据库中，这样会占用大量的空间。一般是将图片上传到服务器，数据库中保存的则是图片的地址（URL）。
  4. 对于一篇文章，数据库一般保存的是包含该文章的HTML代码，也叫作“富文本”。一般我们会使用富文本编辑器编辑内容，然后获取对应的HTML代码，而将该HTML代码保存到数据库中。
  5. 设计表时，应该给所有的表和字段添加对应的注释。这个好习惯一定要养成，这样可以使后期的维护工作更加轻松、简单。

1.4 mysql软删除是什么

MySQL 软删除（Soft Delete）详解

软删除是一种数据管理策略，它不是真正从数据库中物理删除记录，而是通过标记记录为"已删除"状态来保留数据。这与硬删除（直接从数据库中移除记录）形成对比。

为什么使用软删除？
数据恢复：可以轻松恢复误删的数据

审计追踪：保留完整的历史记录用于审计或分析

外键约束：避免因删除记录而破坏外键关系

数据分析：可以分析被删除的数据模式

如何实现软删除

添加"删除标记"列

最常见的方法是添加一个布尔类型的列（如is_deleted）或时间戳列（如deleted_at）：

ALTER TABLE your_table ADD COLUMN is_deleted TINYINT(1) DEFAULT 0;

– 或者

ALTER TABLE your_table ADD COLUMN deleted_at DATETIME NULL;

更新查询逻辑

在所有查询中需要排除已删除的记录：

– 使用 is_deleted 列

SELECT * FROM your_table WHERE is_deleted = 0;

– 使用 deleted_at 列

SELECT * FROM your_table WHERE deleted_at IS NULL;

实现删除操作

更新记录而不是删除：

– 使用 is_deleted 列

UPDATE your_table SET is_deleted = 1 WHERE id = 123;

– 使用 deleted_at 列

UPDATE your_table SET deleted_at = NOW() WHERE id = 123;

恢复已删除的记录

– 使用 is_deleted 列

UPDATE your_table SET is_deleted = 0 WHERE id = 123;

– 使用 deleted_at 列

UPDATE your_table SET deleted_at = NULL WHERE id = 123;

高级实现方式

使用触发器自动处理

可以创建触发器在删除操作时自动更新标记：

CREATE TRIGGER before_delete_trigger
BEFORE DELETE ON your_table
FOR EACH ROW
BEGINUPDATE your_table SET is_deleted = 1, deleted_at = NOW() WHERE id = OLD.id;-- 取消实际的删除操作SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Use soft delete instead';
END;

不过这种方法不太推荐，因为它会干扰正常的DELETE操作流程。
使用视图过滤已删除记录

创建一个只显示未删除记录的视图：

CREATE VIEW active_records AS
SELECT * FROM your_table WHERE is_deleted = 0;
-- 或者
CREATE VIEW active_records AS
SELECT * FROM your_table WHERE deleted_at IS NULL;

然后应用程序可以查询这个视图而不是原始表。

使用数据库扩展或ORM功能

许多ORM框架（如Laravel的Eloquent、Django ORM等）内置了软删除支持：
Laravel Eloquent:

  // 模型中添加use SoftDeletes;// 查询时会自动排除已删除记录$records = Model::all();// 恢复记录$record->restore();

Django:

    from django.db import modelsclass MyModel(models.Model):# ...is_deleted = models.BooleanField(default=False)class Meta:abstract = Truedef delete(self, using=None, keep_parents=False):self.is_deleted = Trueself.save()

注意事项

索引优化：为is_deleted或deleted_at列添加索引以提高查询性能

备份策略：虽然数据未被物理删除，但仍需定期备份

存储空间：长期积累的"已删除"数据会占用存储空间，可能需要定期归档

查询习惯：开发人员需要养成总是排除已删除记录的习惯

软删除是一种简单有效的数据管理策略，特别适合需要保留历史记录或允许数据恢复的场景。

http://www.lryc.cn/news/2400992.html

相关文章：

  • 《前端面试题：CSS有哪些单位！》
  • [ctfshow web入门] web80
  • 【设计模式-4.5】行为型——迭代器模式
  • C++_核心编程_继承中的对象模型
  • 使用cephadm离线部署reef 18版并配置对接openstack
  • Redis最佳实践——性能优化技巧之缓存预热与淘汰策略
  • 2024年数维杯国际大学生数学建模挑战赛D题城市弹性与可持续发展能力评价解题全过程论文及程序
  • 3D Gaussian splatting 06: 代码阅读-训练参数
  • QT聊天项目DAY13
  • Web3如何重塑数据隐私的未来
  • 【鸿蒙】HarmonyOS NEXT之如何正常加载地图组件
  • 前端框架进化史
  • “轻量应用服务器” vs. “云服务器CVM”：小白入门腾讯云，哪款“云机”更适合你？(场景、配置、价格对比解析)
  • day63—回溯—全排列（LeetCode-46）
  • （二）stm32使用4g模块(移远ec800k)连接mqtt
  • 防火墙iptables项目实战
  • webpack继续学习
  • Scrapy爬虫框架Spiders爬虫脚本使用技巧
  • PowerBI企业运营分析—全动态盈亏平衡分析
  • docker的基本命令
  • 【运维实战】Rsync将一台主Web服务器上的文件和目录同步到另一台备份服务器!
  • 实时通信RTC与传统直播的异同
  • Python-正则表达式（re 模块）
  • AgenticSeek 本地部署教程（Windows 系统）
  • 基于 qiankun + vite + vue3 构建微前端应用实践
  • VR教育：开启教育新时代的钥匙
  • 机器学习：逻辑回归与混淆矩阵
  • 20250602在荣品的PRO-RK3566开发板的Android13下打开HDMI显示
  • 【学习记录】快速上手 PyQt6：设置 Qt Designer、PyUIC 和 PyRCC 在 PyCharm中的应用
  • AI在网络安全领域的应用现状和实践