MySQL补充知识点学习

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

1. 基本概念学习

1.1 游标（Cursor）

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

游标的主要作用和功能

1. ​​逐行处理查询结果​​

• 游标允许应用程序按需获取查询结果的每一行，而不是一次性加载所有数据
• 特别适合处理大量数据时避免内存溢出

2. ​​支持复杂的业务逻辑处理​​

• 允许在结果集上执行复杂的业务逻辑，如条件判断、计算、更新等
• 可以在处理每一行时执行不同的操作

3. ​​实现逐行更新或删除​​

• 可以结合游标对查询结果中的每一行执行更新或删除操作
• 这在需要基于当前行内容决定如何处理下一行时非常有用

4. ​​支持存储过程中的流程控制​​

• 在存储过程中使用游标可以实现更复杂的流程控制
• 可以结合条件判断、循环等语句实现复杂的业务逻辑

5. ​​提供灵活的数据访问方式​​

• 允许应用程序以编程方式控制数据的访问和处理
• 可以暂停、继续或重新开始数据处理

游标的基本使用步骤

在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列添加索引以提高查询性能

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

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

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

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