MySQL 存儲過程&觸發器&事務

来源:https://www.cnblogs.com/lhang/archive/2023/05/15/17400981.html
-Advertisement-
Play Games

存儲過程 概念 存儲過程（Stored Procedure），是為了完成特定功能的SQL語句集。 優點 存儲過程可以理解為shell腳本這類型的命令集輸出工具，但是在底層，存儲過程擁有更多的優點： ==語言的靈活性跟功能性更強==，在原有基礎之上可以插入控制語句、迴圈語句等讓SQL語句的功能更強，能 ...


存儲過程

概念

存儲過程（Stored Procedure），是為了完成特定功能的SQL語句集。

優點

存儲過程可以理解為shell腳本這類型的命令集輸出工具，但是在底層，存儲過程擁有更多的優點：

  • 語言的靈活性跟功能性更強，在原有基礎之上可以插入控制語句、迴圈語句等讓SQL語句的功能更強，能夠完成更複雜的運算跟判斷。
  • 封裝性，存儲過程被創建後，可以在被多次調用，同時可以進行修改，對程式源碼不造成影響。
  • 執行速度快，MySQL的語句在執行過程中會有一個[[MySQL語言的編譯|轉譯過程]]，當資料庫體量到達一定的級別的時候，對性能的影響很大，而使用存儲過程的時候，執行過一次以後，產生的二進位代碼就會被預留在緩存區，不需要再次編譯。
  • 減少網路流量，因為不需要進行轉譯了所以在日常使用中會減少對網路流量的使用。
  • 提高資料庫的完整性和安全性，基於上述的封裝性，可以對用戶的使用許可權進行更好的限制，不需要給到更多的許可權的同時也能夠進行所需的查詢，從而也保護了數據的完整性。

創建&調用 存儲過程

調用語法

CALL 過程名稱（參數1,參數2...）

創建語法

 CREATE PROCEDURE <存儲過程別名> ( [過程參數[,…] ] ) <過程體>

過程參數的格式

[ IN | OUT | INOUT ] <參數名> <類型>

示例

[[99-MySQL補充#MySQL分隔符|修改分隔符]]，再寫存儲過程

DELIMITER :

CREATE PROCEDURE SE()
BEGIN
SELECT * FROM learn.books;
END ：

DELIMITER ;
CALL SE();

結束的時候在記得將分隔符修改回預設的。

MySQL變數

定義&調用變數

在MySQL界面中使用 SET 定義變數，用 SELECT 調用變數。

SET @a=1;
SELECT @a;

此處定義的變數為臨時變數，僅該次連接可用，當需要全局長期使用時，需要修改全局變數。

存儲過程與變數

MySQL存儲過程中使用 DECLARE 定義變數，變數僅作用於本次存儲過程內，屬於局部變數。同時需要對變數定義欄位屬性，調用時直接用聲明瞭的變數名調用即可。

變數的傳遞

  1. IN：將變數傳入存儲過程
  2. OUT：將存儲過程內的變數傳出到MySQL變數中
  3. INOUT：變數進入存儲過程又出來到MySQL變數中

示例IN

DELIMITER :

CREATE PROCEDURE getone(in id INT)
BEGIN
SELECT bname,bid FROM books WHERE bid=id;
END :

DELIMITER ;

CALL getone(1)；
  1. 這裡的變數是IN進行傳遞到存儲過程之中，並定義了變數類型為整數。
  2. 這裡將參數傳入存儲過程，所以在調用的時候需要給到傳入參數。

示例OUT

DELIMITER :

CREATE PROCEDURE outone(OUT nu INT)
BEGIN
SET nu=1;
SELECT nu;
END:

DELIMITER ;

CALL outone(@num); 
SELECT @num;

這裡將內部參數傳遞到了外部，並用num進行了接收。

示例INOUT

DELIMITER :
CREATE PROCEDURE inoutone(IN bookid INT,OUT bookname VARCHAR(255))

BEGIN
	SELECT bname into bookname FROM learn.books WHERE bid=bookid;
END:

DELIMITER ;
CALL inoutone(1,@a);

INOUT的參數傳遞需要註意調用跟輸入的對應關係

存儲過程的迴圈

WHILE 迴圈

  • 創建測試表　
    create table pwhile(id int);
  • 在存儲過程中while的結構為

WHILE DO ... END WHILE

  • 示例
DELIMITER :

CREATE PROCEDURE pwhile()
BEGIN
	DECLARE i INT;
	SET i = 0;
	
	WHILE i < 10 DO
		INSERT INTO test VALUES(i) ;
		SET i = i + 1;
	END WHILE;
	
	SELECT * FROM test;
END :

DELIMITER ;
CALL pwhile();

REPEAT迴圈

  • 創建測試表
    `create table ptest(id int
  • REPEAT迴圈結構為

REPEAT...UNTIL...END REPEAT

  • 示例
DELIMITER :

CREATE PROCEDURE arepeat()
BEGIN
	DECLARE i INT;
	SET i = 0;
	
	REPEAT
		INSERT INTO ptest VALUES(i) ;
		SET i = i + 1;
	UNTIL i > 10 END REPEAT;
	
	SELECT * FROM ptest;
END :

DELIMITER ;
CALL arepeat();

loop迴圈

  • 創建測試表　
    cerate table ltest
  • loop迴圈結構　

lp:loop ... if ... end if ... end loop

  • 示例　
DELIMITER :

CREATE PROCEDURE tloop()
BEGIN
	DECLARE i INT;
	SET i = 0;
	
LP:LOOP
	INSERT INTO ltest VALUES(i) ;
	SET i = i + 1;
	IF i > 10 THEN
	LEAVE LP
END LOOP

	SELECT * FROM ltest;
END :

DELIMITER ;
CALL tloop();

LP:LOOP 是對LOOP取了一個別名為LP

查詢&刪除 存儲過程

指定庫名稱查詢SELECT

SELECT name FROM mysql.proc WHERE db='[資料庫名]' AND type='procedure';

指定存儲過程的名稱查詢SHOW

SHOW procedure STATUS LIKE '[存儲過程名稱]' \G

刪除存儲過程DROP

DROP PROCEDURE IF EXISTS [存儲過程名稱];

MySQL觸發器 TRIGRRER

概念

觸發器是一個特殊的存儲過程，區別點在於存儲過程需要使用CALL語句來調用，觸發器的執行不需要，也不需要手動啟動，只需要一個預定義事件就會被MySQL自動調用。

場景

主要用於保護數據，尤其是多表相互鏈接的時候，觸發器能夠讓被鏈接的表之間保持一致性。

預定義事件

即是指觸發觸發器的場景，能夠觸發觸發器的事件，有且只有 INSERT/UPDATE/DELETE 操作時才能觸發。

觸發器種類

根據預定義事件的分類，觸發也分為以下三種：　

  1. INSERT 觸發器
  2. UPDATE 觸發器
  3. DELETE 觸發器
    此三類觸發器可指定在對應命令執行前或後激活觸發器。

創建觸發器

語法

CREATE TRIGGER <觸發器名稱> 
<觸發時機 AFTER|BEFORE>
<觸發事件 UPDATE|DELETE|INSERT> 
ON <需觸發的表 路徑> FOR EACH ROW <觸發器主體>;

補充點

  1. 同一個表中的不能有觸發時機和觸發事件相同的觸發器
  2. 觸發器關聯的表要有永久性，不能將觸發器與臨時表、視圖關聯。
  3. FOR EACH ROW：行級觸發
  4. 觸發器主體：指的是觸發器激活時執行的語句，要執行多個語句時，可以使用BEGIN END複合語句結構。

查看觸發器

SHOW TRIGGERS;

觸發器中NEW和OLD

關於NEW和OLD，可以理解為一個特殊的表，定位了發生變化的數據類型。

在INSER中

NEW 用來表示將要（BEFORE）或已經（AFTER）插入的新數據；
image.png

在UPDATE中

NEW 用來表示將要（BEFORE）或已經（AFTER）插入的新數據；
image.png

在DELETE中

OLD 用來表示將要或已經被刪除的原數據；
image.png

創建觸發器

簡單觸發器

觸發器的創建過程其實也是一個存儲過程的創建

DELIMITER :

CREATE TRIGGER delcategory AFTER DELETE ON category FOR EACH ROW
	
	DELETE FROM books WHERE btypeid=3;
	:

DELIMITER ;

image.png

NEW/OLD 的觸發器

OLD 示例

當category表發生刪除操作後，books表也會根據btypeid進行刪除操作

DELIMITER :

CREATE TRIGGER cbook AFTER DELETE 
ON category FOR EACH ROW

	BEGIN
	DELETE FROM books WHERE books.btypeid=old.btypeid;
	END :

DELIMITER ;

old.btypeid 表示觸發器所在表category中發生刪除的btypeid

NEW 示例

阻止對students表的更新操作，但刪除跟插入不受影響。

DELIMITER :

CREATE TRIGGER ubook BEFORE UPDATE
ON students FOR EACH ROW

	BEGIN
	
	IF old.name is NOT NULL THEN
		SET new.name=old.name,new.id=old.id,new.age=old.age;
	END IF
	
	END :
	
DELIMITER ;

IF old.name is NOT NULL 如果 old表中name欄位不為空　　

  • 此處new.name 是一個並不存在的值，而 old.name 是一個只讀的數據存在於原數據中，即是將發生更新前的數據賦值給到了old.name
利用Pessimistic Lock的觸發器
CREATE TRIGGER tr_MyTrigger ON tablename FOR UPDATE AS 

UPDATE table1 SET column1 = data1
INNER JOIN deleted d ON table1.id = d.id 

UPDATE table2 SET column2 = data2 
INNER JOIN deleted d ON table2.id = d.id 

UPDATE table3 SET column3 = data3 
INNER JOIN deleted d ON table3.id = d.id

/* etc. */ GO
說明
  1. FOR UPDATE AS UPDATE 是一種在MySQL資料庫中實現悲觀鎖（Pessimistic Lock）的技術，它可以保證同一個記錄多線程下同時訪問時不會產生更新併發問題。
  2. /* etc. */ GO 是在SQL中常用的一個命令，它用於告訴SQL伺服器要啟動對SQL腳本的執行。

事務

概念

  • MySQL資料庫事務（datebase transaction）:MySQL事務是用來保證資料庫數據一致性和完整性的一種機制。　
  • 事務可以讓用戶將一系列的SQL語句保存在一個組中，並這些SQL語句作為一個單一的邏輯工作單元來執行。　
  • MySQL事務由4個指令定義：BEGIN開始事務；COMMIT提交事務；ROLLBACK回滾事務；SAVEPOINT設置一個事務保存點。
  • MySQL為每個會話維護了一個隱形的transaction id，以及一個當前正在執行的transaction的id以及一個超時時間，這些都將決定當前正在工作的事務是否需要提交或回滾。
  • 僅支持INNODB和BDB兩種存儲引擎。

事務的特性ACID

原子性（Autmic）

指的是在事務操作的不可分割，僅有0和1，執行全部成功或者全部失敗，沒有部分成功部分失敗。

一致性（Consistency）

指的是在事務開始的前後，整個資料庫的一致性不受影響，數據完整性不受影響。

隔離性（Isolation）

指的是事務的執行是並行且獨立的，在事務完成之後才會將結果進行發佈，整個過程中彼此是不可見的，避免事務的一個混亂。

持久性（Durability）

指的是事務執行完成後，所存儲的數據應該存儲在資料庫中，即使系統發送故障數據本身不受影響。

創建一個簡單事務

創建一個提交的

SELECT bname FROM books WHERE bid=1 OR bid=2;

SET AUTOCOMMIT=0;
DELIMITER :

START TRANSACTION;
	UPDATE books SET bname="cc" WHERE bid=1;
	UPDATE books SET bname="dd" WHERE bid=2;
	COMMIT;
:

DELIMITER ;

SET AUTOCOMMIT=0; 關閉自動提交事務
COMMIT; 提交事務

測試回滾

回滾命令：rollback

當引擎不符合時無法回滾

SHOW CREATE TABLE books\G
SELECT bname WHERE bid=1 or bid=2;
ROLLBACK;
SELECT bname WHERE bid=1 or bid=2;

image.png

修改引擎後重新提交事務

引擎符合時

ALTER TABLE books ENGINE=INNODB;
SHOW CREATE TABLE books\G
SELECT bname WHERE bid=1 or bid=2;

DELIMITER :

START TRANSACTION;
	UPDATE books SET bname="cc" WHERE bid=1;
	UPDATE books SET bname="dd" WHERE bid=2;
:
DELIMITER ;


ROLLBACK;
SELECT bname WHERE bid=1 or bid=2;

回滾成功　
　
image.png


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • K8S單Master集群安裝（Containerd）
    原創文檔編寫不易，未經許可請勿轉載。文檔中有疑問的可以郵件聯繫我。 郵箱：[email protected] 文章基於CentOS 7.8系統使用Containerdr作為容器運行時通過kubeadm指導搭建k8s單機master集群，使用calico作為k8s集群的網路插件。K8S官方在1.24版本 ...
  • ClickHouse筆記: Ubuntu/Centos下的安裝, 配置和用戶管理
    ClickHouse 屬於 OLAP 資料庫, 與 OLTP (Transaction Process) 相比, 註重數據分析, 重點在查詢的性能. 在業務系統中, 往往使用 OLTP 資料庫做業務數據存儲, 用 OLAP 資料庫做查詢分析, 在一些場景下ClickHouse可以取代ES(Elast... ...
  • Redis實戰解讀-初識Redis&Redis基本數據類型
    一.初識Redis 1.什麼是Redis ​ Redis是一個速度非常快的非關係型資料庫(non-relational database)，它可以存儲鍵(key)與五種不同類型的值的映射(mapping)，可以將存儲在記憶體的鍵值對數據持久化到磁碟，可以使用複製特性來擴展讀性能，也可以採用客戶端分片來... ...
  • 06~12-Esp8266物聯網晶元的使用（一）-part02/03-ESP8266開發環境、編程舉例
    上一章主要作了晶元介紹，這一章主要作對開發環境的介紹。 認識Arduino Arduino是一款便捷靈活、方便上手的開源電子原型平臺。包含硬體（各種型號的Arduino板）和軟體（ArduinoIDE）。它構建於開放原始碼simple I/O介面版，並且具有使用類似Java、C語言的Processi ...
  • Redis數據結構二之SDS和雙向鏈表
    本文首發於公眾號：Hunter後端 原文鏈接：Redis數據結構二之SDS和雙向鏈表 這一篇筆記介紹一下 SDS（simple dynamic string）和雙向鏈表。 以下是本篇筆記目錄： SDS 常數複雜度獲取字元串長度 杜絕緩衝區溢出 減少修改字元串帶來的記憶體重分配次數 二進位安全 相容C字 ...
  • Apache Arrow DataFusion原理與架構
    本篇主要介紹了一種使用Rust語言編寫的查詢引擎——DataFusion，其使用了基於Arrow格式的記憶體模型，結合Rust語言本身的優勢，達成了非常優秀的性能指標 DataFusion是一個查詢引擎而非資料庫，因此其本身不具備存儲數據的能力。但正因為不依賴底層存儲的格式，使其成為了一個靈活可擴展的 ...
  • MySQL的varchar存儲原理：InnoDB記錄存儲結構
    摘要：varchar(M) 能存多少個字元，為什麼提示最大16383？innodb怎麼知道varchar真正有多長？記錄為NULL，innodb如何處理？某個列數據占用的位元組數非常多怎麼辦？影響每行實際可用空間的因素有哪些？本篇圍繞innodb預設行格式dynamic來說說原理。 本文分享自華為雲社 ...
  • 架構師日記-從資料庫發展歷程到數據結構設計探析
    本文針對數據存儲相關名詞概念進行瞭解釋，重點介紹了資料庫技術的發展史。為了豐富文章的可讀性以及實用性，又從數據結構設計層面進行了部分技術實戰能力的外延擴展，闡述了拉鏈表，位運算，環形隊列等相關數據結構在軟體開發領域的應用，希望本文給你帶來收穫。 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類