MySQL補充知識點學習

書接上文：MySQL關系型數據庫學習，繼續看書補充MySQL知識點學習。

1. 基本概念學習

1.1 游標（Cursor）

MySQL 游標是一種數據庫對象，它允許應用程序逐行處理查詢結果集，而不是一次性獲取所有結果。游標在需要逐行處理數據或執行復雜業務邏輯時非常有用。

游標的主要作用和功能

??逐行處理查詢結果??

游標允許應用程序按需獲取查詢結果的每一行，而不是一次性加載所有數據
特別適合處理大量數據時避免內存溢出

??支持復雜的業務邏輯處理??

允許在結果集上執行復雜的業務邏輯，如條件判斷、計算、更新等
可以在處理每一行時執行不同的操作

??實現逐行更新或刪除??

可以結合游標對查詢結果中的每一行執行更新或刪除操作
這在需要基于當前行內容決定如何處理下一行時非常有用

??支持存儲過程中的流程控制??

在存儲過程中使用游標可以實現更復雜的流程控制
可以結合條件判斷、循環等語句實現復雜的業務邏輯

??提供靈活的數據訪問方式??

允許應用程序以編程方式控制數據的訪問和處理
可以暫停、繼續或重新開始數據處理

游標的基本使用步驟

在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）。

使用范圍

下面這些操作是無法還原的，小伙伴們一定要記住。

drop database
drop table
drop view
alter table

事務的屬性

事務有很嚴格的定義，必須同時滿足4個屬性：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）和持久性（Durability）。這4個屬性通常又被簡稱為“ACID”特性。

原子性：事務作為一個整體來執行，所有操作要么都執行，要么都不執行；
一致性：事務應確保數據庫從一個一致狀態轉變為另一個一致狀態；
隔離性：當多個事務并發執行時，一個事務的執行不影響其他事務的執行；
持久性：事務一旦提交，它對數據庫的修改應該永久保存在數據庫中；

1.3 表的設計原則

介紹一些常用的小技巧，以設計出更好的表，主要包括以下5個方面。

對于一個表的主鍵，我們一般是使用自動遞增的值，而不是手動插入值；
如果一個字段只有兩種取值，比如“男”或“女”、“是”或“否”，比較好的做法是使用tinyint(1)類型，而不是使用varchar等類型。當然，使用varchar等類型也是沒有問題的。
如果想要保存圖片，我們一般不會將圖片保存到數據庫中，這樣會占用大量的空間。一般是將圖片上傳到服務器，數據庫中保存的則是圖片的地址（URL）。
對于一篇文章，數據庫一般保存的是包含該文章的HTML代碼，也叫作“富文本”。一般我們會使用富文本編輯器編輯內容，然后獲取對應的HTML代碼，而將該HTML代碼保存到數據庫中。
設計表時，應該給所有的表和字段添加對應的注釋。這個好習慣一定要養成，這樣可以使后期的維護工作更加輕松、簡單。

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()