當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL的进阶实战篇

發布時間：2025/3/21 数据库 33 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL的进阶实战篇小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

關聯文章:

MySQL的初次見面禮基礎實戰篇

MySQL的進階實戰篇

本篇上一篇博文MySQL的初次見面禮基礎實戰篇的延續，是mysql的進階內容的記錄，本篇主要知識點如下：

進階實戰篇

進階實戰篇
- mysql視圖機制
  - 什么是視圖
  - 視圖創建與使用
  - 視圖的本質
- 高效索引
  - 使用索引的理由
  - 索引的創建及其基本類型
    - 單列索引
    - 復合索引
    - 唯一索引
    - 主鍵索引
    - 索引的設計
- 存儲過程
  - 為什么需要存儲過程
  - 存儲過程的創建與使用
    - 輸入輸出參數類型
    - 刪除存儲過程
    - 查看存儲過程的狀態
    - 查看存儲過程的創建語句
    - 存儲過程的流程控制語句
    - 定義變量
    - 使用DECLARE定義局部變量
    - 了解其他類型的變量
    - 使用DECLARE 定義條件和處理程序
    - 構建復雜的存儲過程案例
- 存儲函數
  - 創建存儲函數
  - 刪除存儲函數
  - 存儲過程與存儲函數的區別
- 觸發器
  - 觸發器的概念及其語法
  - 創建觸發器
  - 查看觸發器
  - 刪除觸發器
- 游標
- 事務處理
  - 事務的概念
  - 事務控制流程實戰
  - 鎖以及事務處理分離水平隔離級別
  - 事務原理概要

mysql視圖機制

什么是視圖

視圖是一張虛擬的表，為什么是虛擬呢？因為視圖與數據庫中存在的表不太一樣，前面我們創建的4張表都是包含數據的，如用戶信息，訂單信息等，而視圖則是不包含數據的，下面通過一個例子來演示視圖,下面的sql是查詢王五的所有訂單情況，需要關聯到orders表、orderdetail表、items表、user表

mysql> select u.username , o.number , tm.name as itemsName , tm.price , od.items_num from -> (-> (orders as o inner join orderdetail as od on o.id = od.orders_id ) -> inner join items as tm on od.items_id = tm.id -> )-> inner join user as u on o.user_id = u.id-> where username='王五'; +----------+---------+-------------+---------+-----------+ | username | number | name | price | items_num | +----------+---------+-------------+---------+-----------+ | 王五 | 1000010 | MacBook Air | 8298.9 | 1 | | 王五 | 1000010 | MacBook Pro | 10982.0 | 3 | | 王五 | 1000011 | 背包 | 200.0 | 4 | | 王五 | 1000011 | MacBook Pro | 10982.0 | 3 | +----------+---------+-------------+---------+-----------+ 4 rows in set (0.01 sec)

顯然數據已如期查詢出來了，但是我們發現任何需要這個數據的人都必須了解相關聯的表結構，并且需要知道如何創建查詢和對表進行聯結，為了檢索其他用戶的相同數據必須修改Where條件并帶上一大段關聯查詢的sql語句。是的，每次這樣的操作確實挺麻煩的，假如現在可以把這個除了where條件外的sql查詢出來的數據包裝成一個名為user_order_data的虛擬表，就可以使用以下方式檢索出數據了。

select * from user_order_data where username='王五';

按這樣的方式每次查詢不同的用戶只需修改where條件即可也不同再寫那段看起有點惡心的長sql了，而事實上user_order_data就是一張視圖表，也可稱為虛擬表，而這就是視圖最顯著的作用了。

視圖創建與使用

了解完什么是視圖后，我們先來看看如何創建視圖和使用視圖,使用以下語法：

CREATE VIEW 視圖名(列名,...) AS SELECT 語句

現在我們使用前面關聯查詢的orders表、orderdetail表、items表、user表來創建視圖user_order_data

-- 創建視圖虛擬表user_order_data mysql> create view user_order_data(username,number,itemname,price,items_num) as select u.username , o.number , tm.name , tm.price , od.items_num from -> (-> (orders as o inner join orderdetail as od on o.id = od.orders_id ) -> inner join items as tm on od.items_id = tm.id -> )-> inner join user as u on o.user_id = u.id; Query OK, 0 rows affected (0.01 sec)-- 使用視圖 mysql> select * from user_order_data; +----------+---------+-------------+---------+-----------+ | username | number | itemname | price | items_num | +----------+---------+-------------+---------+-----------+ | 王五 | 1000010 | MacBook Air | 8298.9 | 1 | | 王五 | 1000010 | MacBook Pro | 10982.0 | 3 | | 王五 | 1000011 | 背包 | 200.0 | 4 | | 王五 | 1000011 | MacBook Pro | 10982.0 | 3 | +----------+---------+-------------+---------+-----------+ 4 rows in set (0.01 sec)

可以看出除了在select語句前面加上create view user_order_data as外，其他幾乎沒變化。在使用視圖user_order_data時，跟使用數據庫表沒啥區別，因此以后需要查詢指定用戶或者所有用戶的訂單情況時，就不用編寫長巴巴的一段sql了，還是蠻簡潔的。除了上述的方式，還可以將視圖虛擬表的字段別名移動到查詢字段后面：

CREATE OR REPLACE VIEW user_order_dataASSELECTu.username as username, o.number as number , tm.name as name , tm.price as price , od.items_num as items_numFROM((orders as o INNER JOIN orderdetail as od ON o.id = od.orders_id ) INNER JOIN items as tm ON od.items_id = tm.id )INNER JOIN user as u ON o.user_id = u.id

注意這里使用了CREATE OR REPLACE VIEW語句，意思就是不存在就創建，存在就替換。如果想刪除視圖可以使用以下語法：

DROP VIEW 視圖名稱

在使用視圖的過程還有些需要注意的點，如下

與創建表一樣，創建視圖的名稱必須唯一
創建視圖的個數并沒限制，但是如果一張視圖嵌套或者關聯的表過多，同樣會引發性能問題，在實際生產環節中部署時務必進行必要的性能檢測。
在過濾條件數據時如果在創建視圖的sql語句中存在where的條件語句，而在使用該視圖的語句中也存在where條件語句時，這兩個where條件語句會自動組合
order by 可以在視圖中使用，但如果從該視圖檢索數據的select語句中也含有order by ，那么該視圖中的order by 將被覆蓋。
視圖中不能使用索引，也不能使用觸發器(索引和觸發器后面會分析)
使用可以和普通的表一起使用，編輯一條聯結視圖和普通表的sql語句是允許的。

關于使用視圖對數據的進行更新(增刪改)，因為視圖本身并沒有數據，所以這些操作都是直接作用到普通表中的，但也并非所有的視圖都可以進行更新操作,如視圖中存在分組(group by)、聯結、子查詢、并(unoin)、聚合函數(sum/count等)、計算字段、DISTINCT等都不能對視圖進行更新操作，因此我們前面的例子也是不能進行更新操作的，事實上，視圖更多的是用于數據檢索而更新，因此對于更新也沒有必要進行過多闡述。

視圖的本質

到此對于視圖的創建和使用都比較清晰了，現在準備進一步認識視圖的本質，前面我們反復說過，視圖是一張虛擬表，是不帶任何數據的，每次查詢時只是從普通表中動態地獲取數據并組合，只不過外表看起來像一張表罷了。其原理通過下圖便一目了然：

事實上有些時候視圖還會被用于限制用戶對普通表的查詢操作，對于這類用戶只賦予對應視圖的select操作權限，僅讓他們只能讀取特定的行或列的數據。這樣我們也就不用直接使用數據庫的權限設置限制行列的讀取，同時也避免了權限細化的麻煩。

高效索引

使用索引的理由

由于mysql在默認情況下，表中的數據記錄是沒有順序可言的，也就是說在數據檢索過程中，符合條件的數據存儲在哪里，我們是完全不知情的，如果使用select語句進行查詢，數據庫會從第一條記錄開始檢索，即使找到第一條符合條件的數據，數據庫的搜索也并不會因此而停止，畢竟符合條件的數據可能并不止一條，也就是說此時檢索會把表中的數據全部檢索一遍才結束，這樣的檢索方式也稱為全表掃描。但假設表中存在上百上千萬條數據呢？這樣的檢索效率就十分低了，為了解決這個問題，索引的概念就誕生了，索引是為檢索而存在的。如一些書的末尾一般會提供專門附錄索引，指明了某個關鍵字在正文中的出現的頁碼位置或章節的位置，這樣只要找到對應頁面就能找到要搜索的內容了，數據庫的索引也是類似這樣的原理，通過創建某個字段或者多個字段的索引，在搜索該字段時就可以根據對應的索引進行快速檢索出相應內容而無需全表掃描了。

索引的創建及其基本類型

MySQL 索引可以分為單列索引、復合索引、唯一索引、主鍵索引等。下面分別介紹

單列索引

單列索引，也稱為普通索引，單列索引是最基本的索引，它沒有任何限制，創建一個單列索引，語法如下:

CREATE INDEX index_name ON tbl_name(index_col_name);

其中index_name為索引的名稱，可以自定義，tbl_name則指明要創建索引的表，而index_col_name指明表中那一個列要創建索引。當然我們也可以通過修改表結構的方式添加索引：

ALTER TABLE tbl_name ADD INDEX index_name ON (index_col_name);

還可在創建表時直接指定：

-- 創建表時直接指定 CREATE TABLE `table` ( `id` int(11) NOT NULL AUTO_INCREMENT , `name` varchar(32) NOT NULL , ...... -- 其他字段 PRIMARY KEY (`id`), indexName (name(32)) -- 創建name字段索引 );

下面為user表的username字段創建單列索引：

-- 創建username字段的索引名稱為index_name，這就是基礎的索引創建 mysql> create index index_name on user(username); Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0-- 查看user表存在的索引 \G 代表優化顯示方式 mysql> show index from user \G; *************************** 1. row ***************************Table: user 表名稱Non_unique: 0 索引是否允許重復(1:可重復,0:不可重復)Key_name: PRIMARY 索引名稱Seq_in_index: 1 Column_name: id 表中被創建索引的字段名稱Collation: A 是否排序(A:升序,Null:不排序)Cardinality: 9 Sub_part: NULLPacked: NULLNull: Index_type: BTREE 索引的類型Comment: Index_comment: *************************** 2. row ***************************Table: userNon_unique: 1 索引是否允許重復(1:可重復,0:不可重復)Key_name: index_name 索引名稱Seq_in_index: 1Column_name: username 表中被創建索引的字段名稱Collation: A 是否排序(A:升序,Null:不排序)Cardinality: 9Sub_part: NULLPacked: NULLNull: Index_type: BTREE 索引的類型Comment: Index_comment: 2 rows in set (0.00 sec)

可見user表中的username字段的索引已被創建，在使用show index from user查看user的索引字段時，我們發現id字段也創建了索引，事實上，當user表被創建時，主鍵的定義的字段id就會自動創建索引，這是一種特殊的索引，也稱為叢生索引，而剛才創建的index_name索引屬于單列索引

復合索引

復合索引：復合索引是在多個字段上創建的索引。復合索引遵守“最左前綴”原則，即在查詢條件中使用了復合索引的第一個字段，索引才會被使用。因此，在復合索引中索引列的順序至關重要。創建一個復合索引的語法如下：

-- index_name代表索引名稱，而index_col_name1和index_col_name2為列名，可以多個 CREATE INDEX index_name ON tbl_name(index_col_name1,index_col_name2,...);-- 同樣道理，也可以通過修改表結構的方式添加索引， ALTER TABLE tbl_name ADD INDEX index_name ON (index_col_name1,index_col_name2,...);-- 創建表時直接指定 CREATE TABLE `table` ( `id` int(11) NOT NULL AUTO_INCREMENT , `name` varchar(32) NOT NULL , 'pinyin' varchar(32) , ...... -- 其他字段 PRIMARY KEY (`id`), indexName (name(32),pinyin(32)) );

為了方便演示，為user表添加名稱拼音字段（pinyin）

-- 添加新字段pinyin mysql> alter table user add pinyin varchar(32) after username; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0

現在利用username和pinyin 兩個字段為user表創建復合索引，先刪除之前為username創建的索引，刪除索引語法如下：

DROP INDEX 索引名稱 ON 表名

刪除username的索引

-- 刪除index_name索引 mysql> drop index index_name on user; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 -- 查看user的索引 mysql> show index from user \G; *************************** 1. row ***************************Table: userNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 7Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment: Index_comment: 1 row in set (0.00 sec)

ok,index_name 索引已被刪除，現在聯合username和pinyin創建索引如下：

-- 創建新索引多列組成，index_pinyin為復合索引名稱 mysql> create index index_pinyin on user(username,pinyin); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0-- 這里省略主鍵索引 mysql> show index from user \G; *************************** 2. row ***************************Table: userNon_unique: 1Key_name: index_pinyin 相同的索引名稱Seq_in_index: 1 顯示列的順序Column_name: usernameCollation: ACardinality: 7Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment: Index_comment: *************************** 3. row ***************************Table: userNon_unique: 1Key_name: index_pinyin 相同的索引名稱Seq_in_index: 2 顯示列的順序Column_name: pinyinCollation: ACardinality: 7Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: Index_comment: 3 rows in set (0.00 sec)

像這樣由兩個以上組成的索引稱為復合索引，由于是復合索引因此索引的名稱都相同，注意Seq_in_index代表索引字段的順序，前面我們說過在查詢條件中使用了復合索引的第一個字段（這里指username），索引才會被使用。因此，在復合索引中索引列的順序至關重要。

唯一索引

創建唯一索引必須指定關鍵字UNIQUE，唯一索引和單列索引類似，主要的區別在于，唯一索引限制列的值必須唯一，但允許有空值。對于多個字段，唯一索引規定列值的組合必須唯一。如創建username為唯一索引，那么username的值是不可以重復的，

-- 創建唯一索引 CREATE UNIQUE INDEX index_name ON tbl_name(index_col_name[,...]);-- 添加（通過修改表結構） ALTER TABLE tbl_name ADD UNIQUE INDEX index_name ON (index_col_name[,...]);-- 創建表時直接指定 CREATE TABLE `table` ( `id` int(11) NOT NULL AUTO_INCREMENT , `name` varchar(32) NOT NULL , ...... -- 其他字段 PRIMARY KEY (`id`), UNIQUE indexName (name(32)) );

下面為user表的username字段創建唯一索引：

-- 僅為演示 create unique index idx_name on user(username);

事實上這里講username設置為唯一索引是不合理的，畢竟用戶可能存在相同username，因此在實際生產環節中username是不應該設置為唯一索引的。否則當有相同的名稱插入時，數據庫表將會報錯。

主鍵索引

主鍵索引也稱叢生索引，是一種特殊的唯一索引，不允許有空值。創建主鍵索引語法如下：

ALTER TABLE tbl_name ADD PRIMARY KEY(index_col_name);

一般情況下在創建表時，指明了主鍵時，主鍵索引就已自動創建了，因此無需我們手動創建。

-- 創建表時直接指定 CREATE TABLE `table` ( `id` int(11) NOT NULL AUTO_INCREMENT , ...... -- 其他字段 PRIMARY KEY (`id`), -- 主鍵索引 );

索引的設計

where子句中的列可能最適合做為索引
不要嘗試為性別或者有無這類字段等建立索引(因為類似性別的列，一般只含有“0”和“1”，無論搜索結果如何都會大約得出一半的數據)
如果創建復合索引，要遵守最左前綴法則。即查詢從索引的最左前列開始，并且不跳過索引中的列
不要過度使用索引。每一次的更新，刪除，插入都會維護該表的索引，更多的索引意味著占用更多的空間
使用InnoDB存儲引擎時，記錄(行)默認會按照一定的順序存儲，如果已定義主鍵，則按照主鍵順序存儲，由于普通索引都會保存主鍵的鍵值，因此主鍵應盡可能的選擇較短的數據類型，以便節省存儲空間
不要嘗試在索引列上使用函數。

ok~，關于索引暫且聊到這，由于索引細說起來內容還是相當多，本篇只對索引的主要知識點進行說明，讓我們對索引有個清晰的了解并學會如何去使用索引。

存儲過程

為什么需要存儲過程

迄今為止，我們所使用的大多數SQL語句都針對一個或多個表的單條語句，當需要通過處理流程來達到預期目標時，單條sql語句就很難做到了，這是因為sql語句無法編寫處理流程的語句，所有的sql都只能通過一個個命令執行，比如想循環執行某個SQL語句，對于沒有處理流程的sql顯然是無法實現的，此時就需要通過存儲過程來達到目的了，簡單的理解存儲過程就是數據庫中保存的一系列SQL命令的集合，也就是說通過存儲過程就可以編寫流程語句，如循環操作語句等，下面看看如何使用存儲過程。

存儲過程的創建與使用

可以通過以下語法創建存儲過程：

CREATE PROCEDURE 存儲過程名稱( 參數的種類1 參數1 數據類型1[,參數的種類2 參數2 數據類型2])BEGIN處理內容END

存儲過程的名稱可以自由定義，但不可與存在的函數或存儲過程名稱重復，命名時建議以【sp_】開頭，需要處理的內容則編輯在BEGIN和END之間。參數的種類分3種，分別是IN、OUT、INOUT，其中IN為輸入參數類型，OUT為輸出參數類型，而INOUT既是輸入類型又是輸出類型，下面我們創建一個存儲過程，以達到對user表的用戶名稱進行模糊查詢的目的，存儲過程名稱為sp_search_user：

-- 改變分隔符 DELIMITER // -- 創建存儲過程 mysql> create procedure sp_search_user (in name varchar(20));-> begin-> if name is null or name='' then-> select * from user;-> else -> select * from user where username like name;-> end if;-> end-> // -- 執行sql Query OK, 0 rows affected (0.01 sec)mysql> DELIMITER ; -- 恢復分隔符

其中DELIMITER可以用于改變分隔符，由于存儲過程中流程語句需要使用分號結尾與mysql命令行的sql語句結尾的分號沖突，于是改變分隔符為//，執行完存儲過程后再恢復為分號即可。從存儲過程創建語句中，我們設置一個name的輸出參數語句并在begin與end之間編寫了流程語句，當名稱為空時查詢所有用戶否則按傳入的條件查詢。現在可以使用該存儲過程了，調用語法如下：

CALL 存儲過程名稱(參數，....)

執行已創建存儲函數

-- name傳入null值，查詢所有用戶。 mysql> call sp_search_user(null); +----+-----------+--------------+------------+------+--------------------+ | id | username | pinyin | birthday | sex | address | +----+-----------+--------------+------------+------+--------------------+ | 1 | 王五 | wangwu | NULL | 1 | 北京市朝陽區 | | 2 | 張曹宇 | zhangcaoyu | 1990-08-05 | 1 | 廣東省汕頭市 | | 3 | 李達康 | lidakang | 1909-12-12 | 1 | 北京朝陽 | | 10 | 張書記 | zhangshuji | 1999-06-06 | 0 | 北京市朝陽區 | | 16 | 任在明 | renzaiming | 1996-12-01 | 1 | 廣東省廣州市 | | 22 | 陳小明 | chenxiaoming | 1995-05-10 | 0 | 廣東省深圳市 | | 24 | 任傳海 | renchuanhai | 1992-03-08 | 1 | 海南三亞 | +----+-----------+--------------+------------+------+--------------------+ 7 rows in set (0.00 sec)-- 查詢以name以任開頭的用戶 mysql> call sp_search_user('任%'); +----+-----------+-------------+------------+------+--------------------+ | id | username | pinyin | birthday | sex | address | +----+-----------+-------------+------------+------+--------------------+ | 24 | 任傳海 | renchuanhai | 1992-03-08 | 1 | 海南三亞 | | 16 | 任在明 | renzaiming | 1996-12-01 | 1 | 廣東省廣州市 | +----+-----------+-------------+------------+------+--------------------+ 2 rows in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)

輸入輸出參數類型

前面提到了三種輸入輸出的參數類型，IN輸入參數類型，OUT輸出參數類型，而INOUT既是輸入類型又是輸出類型，所謂的IN輸入參數類型就是把要傳遞的參數輸入到存儲過程的內部以便編寫存儲過程流程語句時可以使用。上述演示過的例子就是這種類型。關鍵字OUT則是指明相應參數用來從存儲過程傳出的一個值,也可以理解為存儲過程的返回值，而對于INOUT則是兩者結合體。現在我們創建一個存儲過程，用于返回商品的最大值、最小值和平均值，命名為sp_item_price

DELIMITER // -- 創建存儲過程 mysql> create procedure sp_item_price(out plow decimal(8,2),-> out phigh decimal(8,2),-> out pavg decimal(8,2)-> )-> begin-> select min(price) into plow from items;-> select max(price) into phigh from items;-> select avg(price) into pavg from items;-> end;-> // Query OK, 0 rows affected (0.01 sec) -- 恢復分隔符 DELIMITER ; -- 調用存儲過程 mysql> call sp_item_price(@pricelow,@pricehigh,@priceavg); Query OK, 1 row affected, 1 warning (0.00 sec)-- 查詢執行結果 mysql> select @pricelow ; +-----------+ | @pricelow | +-----------+ | 78.00 | +-----------+ 1 row in set (0.01 sec)mysql> select @pricehigh ; +------------+ | @pricehigh | +------------+ | 10982.00 | +------------+ 1 row in set (0.00 sec)mysql> select @priceavg ; +-----------+ | @priceavg | +-----------+ | 3773.65 | +-----------+ 1 row in set (0.00 sec)

正如我們所看到的，創建sp_item_price時，使用了3個out參數，在存儲過程內部將會把執行結果分別存入這個三個變量中，存入關鍵字使用的是into，完成存儲過程創建后，使用call sp_item_price(@pricelow,@pricehigh,@priceavg);調用sp_item_price，傳入用于存儲返回值的3個變量，注意mysql的用戶變量必須以@開頭，名稱可自定義，但不能重復，調用完成后語句并沒有顯示任何數據，因為這些數據都存入了@pricelow,@pricehigh,@priceavg 三個變量中，我們可以使用select操作符查詢這些變量。這里有點要明白的，上述存儲過程中使用對輸出變量的賦值語句：

SELECT 列名1，... INTO 變量名1，... FROM 表名 WHERE 語句等...

請注意如果檢索出多個列名，與之對應的賦值變量也必須有多個。ok，對輸入輸出參數類型有了清晰的了解后，為了加深理解，我們創建一個同時存在輸入輸出參數的存儲過程，輸入訂單號，計算該訂單的訂單總價，名稱為sp_order_sum_price

DELIMITER // -- 創建存儲過程 mysql> create procedure sp_order_sum_price(in number int ,out ptotal decimal(8,2)) -> begin -> select sum(price * items_num) from items inner join orderdetail as od on items.id = od.items_id -> where od.orders_id=number -> into ptotal; --放到語句后面也可行-> end;-> // Query OK, 0 rows affected (0.01 sec)-- 恢復分隔符 DELIMITER ;-- 執行存儲過程 mysql> call sp_order_sum_price(3,@priceTotal)； Query OK, 1 row affected, 1 warning (0.00 sec)-- 查詢結果 mysql> select @priceTotal； +-------------+ | @priceTotal | +-------------+ | 41244.90 | +-------------+ 1 row in set (0.00 sec)

刪除存儲過程

如果創建存儲過程有誤，可以刪除后重新創建，當然也可以修改，不過感覺不如刪除重建來得更簡單，刪除存儲過程可以使用以下語句

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

查看存儲過程的狀態

SHOW PROCEDURE STATUS [LIKE 'pattern']

如查看的sp_order_sum_price狀態

-- 查看的sp_order_sum_price狀態 mysql> SHOW PROCEDURE STATUS LIKE 'sp_order_sum_price' \G; *************************** 1. row ***************************Db: webshopName: sp_order_sum_priceType: PROCEDUREDefiner: root@localhostModified: 2017-04-17 09:38:52Created: 2017-04-17 09:38:52Security_type: DEFINERComment: character_set_client: utf8 collation_connection: utf8_general_ciDatabase Collation: utf8_general_ci 1 row in set (0.00 sec)

查看存儲過程的創建語句

查看已創建的存儲過程，可以使用以下語法

SHOW CREATE PROCEDURE 存儲過程名;

如下查看前面已創建的sp_order_sum_price存儲過程：

-- 查看sp_order_sum_price存儲過程 \G代表格式輸出格式 mysql> show create procedure sp_order_sum_price \G ; *************************** 1. row ***************************Procedure: sp_order_sum_price -- 存儲過程名稱sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION-- 存儲過程創建語句 Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_order_sum_price`(in number int ,out ptotal decimal(8,2)) begin select sum(price * items_num) from items inner join orderdetail as od on items.id = od.items_id where od.orders_id=number into ptotal; end character_set_client: utf8 collation_connection: utf8_general_ciDatabase Collation: utf8_general_ci 1 row in set (0.00 sec)

存儲過程的流程控制語句

以下是存儲過程中可以使用的流程控制語句

IF 條件語句

IF 條件語句，事實上與流行編程的條件語句類似，其語法如下：

IF 條件表達式1 THEN條件表達式1為true執行 [ELSEIF 條件表達式2 THEN條件表達式2為true執行][ELSE全部條件為false時執行]END IF；

簡單案例：

mysql> create procedure sp_search_user (in name varchar(20));-> begin-> if name is null or name='' then-> select * from user;-> else -> select * from user where username like name;-> end if;-> end-> // <------執行sql

多分支條件語句

CASE 表達式1WHEN 值1 THEN 表達式=值1時執行命令[WHEN 值N THEN 表達式=值N時執行該語句][ELSE 上述值以外執行該語句] END CASE

簡單案例

mysql > DELIMITER // mysql > CREATE PROCEDURE sp_insert_user (in parameter int) -> begin -> declare var int; -- 聲明變量 -> set var=parameter+1; -- 設置變量值 -> case var -> when 0 then -> insert into user values(11,'高玉蘭','gaoyulan','1999-01-01',0,null); -> when 1 then -> insert into user values(11,'高曉龍','gaoxiaolong','1999-01-01',1,null); -> else -> insert into user values(11,'高余糧','gaoyuliang','1999-01-01',1,null); -> end case; -> end -> // mysql > DELIMITER ;

repeat 循環控制語句

REPEAT 直至條件表達式為True時執行的語句 UNTIL 條件表達式 END REPEAT;

有時需要測試查詢性能，需要準備假數據，使用循環語句來插入是個不錯的選擇，如下向user表中插入2000條數據。

mysql > DELIMITER // mysql > CREATE PROCEDURE sp_insert_user_repeat_2000 () -> begin -> declare n int; -- 聲明變量 -> set n=0; -- 設置變量值 -> repeat -> insert into user values(30+n,concat('高余糧',n),'gaoyuliang','1999-01-01',1,null); -> set n=n+1; -> until n>=2000 end repeat; -- 結束條件 -> end -> // Query OK, 0 rows affected (0.01 sec) mysql > DELIMITER ; -- 執行存儲過程 mysql> call sp_insert_user_repeat_2000(); Query OK, 1 row affected (0.01 sec)

while循環控制語句

while 循環語句與repeat循環控制語句的區別是前者條件不符合一次循環體都不會執行，而后者無論條件是否符合，至少執行一次循環體，這點從前面的案例可以看出來。while循環語句語法如下：

WHILE 條件表達式 DO 系列語句 END WHILE

使用while循環語句實現前面的repeat一樣的功能。

mysql > DELIMITER // mysql > CREATE PROCEDURE sp_insert_user_while_2000 () -> begin -> declare n int; -- 聲明變量 -> set n=0; -- 設置變量值 -> while n < 2000 do -> insert into user values(30+n,concat('高余糧',n),'gaoyuliang','1999-01-01',1,null); -> set n=n+1; -> end while; -> end -> // Query OK, 0 rows affected (0.01 sec) mysql > DELIMITER ;

定義變量

使用DECLARE定義局部變量

在流程語句的分析中，我們在存儲過程中使用變量的聲明與設置，由于這些變量也只能在存儲過程中使用，因此也稱為局部變量，變量的聲明可以使用以下語法：

DECLARE 變量名[,變量名2...] 數據類型(type) [DEFAULT value];-- 定義變量num，數據類型為INT型，默認值為10 DECLARE num INT DEFAULT 10 ;

其中， DECLARE關鍵字是用來聲明變量的；變量名即變量的名稱，這里可以同時定義多個變量；type參數用來指定變量的類型；DEFAULT value子句將變量默認值設置為value，沒有使用DEFAULT子句時，默認值為NULL。聲明后，我們就可以在存儲過程使用該變量，設置變量值可以使用以下語法：

SET 變量名1 = expr [, 變量名2 = expr] ...

其中，SET關鍵字是用來為變量賦值的；expr參數是賦值表達式或某個值。一個SET語句可以同時為多個變量賦值，各個變量的賦值語句之間用逗號隔開。除了這種賦值方式，前面我們還提到過使用SELECT…INTO語句為變量賦值，那也是可行的。

了解其他類型的變量

用戶變量：以”@”開始，形式為”@變量名”，用戶變量跟mysql客戶端是綁定的，設置的變量，只對當前用戶使用的客戶端生效，聲明或者定義用戶變量使用set語句，如 set @var 若沒有指定GLOBAL 或SESSION ，那么默認將會定義用戶變量。
全局變量：定義時，以如下兩種形式出現，set GLOBAL 變量名或者 set @@global.name，對所有客戶端生效。只有具有super權限才可以設置全局變量。如下：
mysql> SET GLOBAL sort_buffer_size=value; mysql> SET @@global.sort_buffer_size=value;
- 1
- 2
會話變量：只對連接的客戶端有效。
mysql> SET SESSION sort_buffer_size=value;
- 1

使用DECLARE 定義條件和處理程序

定義條件和處理程序是事先定義程序執行過程中可能遇到的問題，并且可以在處理程序中定義解決這些問題的辦法，可以簡單理解為異常處理，這種方式可以提前預測可能出現的問題，并提出解決辦法，從而增強程序健壯性，避免程序異常停止。MySQL通過DECLARE關鍵字來定義條件和處理程序。

定義條件

MySQL中可以使用DECLARE關鍵字來定義條件。其基本語法如下：

-- 條件定義語法 DECLARE condition_name CONDITION FOR condition_value -- condition_value的定義格式 SQLSTATE [VALUE] sqlstate_value | mysql_error_code

其中，condition_name表示條件的名稱，condition_value參數表示條件的類型；sqlstate_value參數和mysql_error_code參數都可以表示MySQL的錯誤。如常見的ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146，簡單案例如下：

-- 定義主鍵重復錯誤 -- ERROR 1062 (23000): Duplicate entry '60' for key 'PRIMARY'-- 方法一：使用sqlstate_value DECLARE primary_key_duplicate CONDITION FOR SQLSTATE '23000' ; -- 方法二：使用mysql_error_code DECLARE primary_key_duplicate CONDITION FOR 1062 ;

定義處理程序

前面定義的處理條件，可以在定義處理程序中使用,先了解一下定義語法：

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

handler_type?參數的取值有三種：CONTINUE | EXIT | UNDO。

CONTINUE 表示遇到錯誤不進行處理，繼續向下執行；
EXIT 表示遇到錯誤后馬上退出；
UNDO 表示遇到錯誤后撤回之前的操作，但MySQL中暫時還不支持這種處理方式。
我們需要注意的是，大多數情況下，執行過程中遇到錯誤應該立刻停止執行下面的語句，并且撤回前面的操作。由于MySQL目前并不支持UNDO操作。所以，遇到錯誤時最好執行EXIT操作。如果事先能夠預測錯誤類型，并且進行相應的處理，那么就選擇CONTINUE操作。

condition_value?參數指明錯誤類型，該參數有6個取值。語法如下：

sqlstate_value參數和mysql_error_code參數都可以表示MySQL的錯誤。如常見的ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146。與條件中參數是一樣的。
condition_name是DECLARE定義的條件名稱，就前面定義條件語句
NOT FOUND表示所有以02開頭的sqlstate_value值。
SQLEXCEPTION表示所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值。

sp_statement?參數表示要執行存儲過程或函數語句。

以下定義了如何捕獲和處理異常的簡單例子

-- 捕獲sqlstate_value值。如果遇到sqlstate_value值為42S02，執行CONTINUE操作，并且設置用戶變量info。 DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='CAN NOT FIND'; -- 捕獲mysql_error_code，如果遇到mysql_error_code值為1146，執行CONTINUE操作，并且設置用戶變量info。 DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND'; -- 先定義條件，然后定義處理程序調用 DECLARE can_not_find CONDITION FOR 1146 ; -- 定義處理程序，并使用定義的can_not_find條件 DECLARE CONTINUE HANDLER FOR can_not_find SET @info='CAN NOT FIND'; -- SQLWARNING捕獲所有以01開頭的sqlstate_value值，然后執行EXIT DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR'; -- NOT FOUND捕獲所有以02開頭的sqlstate_value值，然后執行EXIT操作，并且輸出"CAN NOT FIND"信息 DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND'; -- SQLEXCEPTION捕獲所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值，然后執行EXIT操作。 DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

為了加深理解，下面我們編寫一個存儲過程用于添加用戶，借此來了解定義處理程序的作用，如下：

mysql > DELIMITER // mysql> create procedure sp_insert_user()-> begin -> set @n=1; -- 設置用戶變量，用于標識程序運行到哪里一步停止-> insert into user value(60,'小米','xiaomi',null,1,null);-> set @n=2;-> insert into user value(61,'小米2','xiaomi2',null,1,null);-> set @n=3;-> end -> // Query OK, 0 rows affected (0.00 sec) mysql > DELIMITER ; -- 執行存儲過程 mysql> call sp_insert_user(); -- 報錯，因為主鍵60的用戶已存在 ERROR 1062 (23000): Duplicate entry '60' for key 'PRIMARY' -- 查詢標識，顯然在第一步時遇到錯誤就停止了執行 mysql> select @n ; +------+ | @n | +------+ | 1 | +------+ 1 row in set (0.00 sec)

上述程序在執行完set @n=1;后就出錯了，因為出現了重復的主鍵值，也就直接導致后面的程序也無法執行，現在我們編寫一個處理程序，使用存儲過程中即使出現2300錯誤也繼續執行，如下：

mysql> create procedure insert_user_2()-> begin-- 定義條件-> DECLARE primary_key_exist CONDITION SQLSTATE '23000' ; -- 定義處理程序,出現2300錯誤繼續執行，@m用于標識-> DECLARE CONTINUE HANDLER FOR primary_key_exist SET @m = 1000;-> set @n=1;-> insert into user value(60,'小米','xiaomi',null,1,null);-> set @n=2;-> insert into user value(61,'小米2','xiaomi2',null,1,null);-> set @n=3;-> end -> // Query OK, 0 rows affected (0.00 sec) mysql > DELIMITER ; -- 執行，并沒有報錯 mysql> call insert_user_2(); Query OK, 0 rows affected (0.01 sec) -- 查詢標識 mysql> select @n; +------+ | @n | +------+ | 3 | +------+ 1 row in set (0.00 sec)mysql> select @m； +------+ | @m | +------+ | 1000 | +------+ 1 row in set (0.00 sec)

從程序可以看出即使出現主鍵重復錯誤，但由于我們進行捕獲并處理使得整個存儲過程的程序可以執行完成。

構建復雜的存儲過程(案例)

獲取一個訂單的總價，并判斷是否需要營業稅收，案例如下：

DELIMITER // mysql> create procedure sp_ordertotal( in onnumber int,in taxable boolean ,out ototal decimal(8,2) )-> begin-> -- 定義變量：總價-> declare total decimal(8,2);-> -- 定義默認稅收率-> declare taxrate int default 6;-> -- 關聯查詢并計算總價-> select sum( price * items_num) from orderdetail as od -> inner join items as it on it.id=od.items_id-> where od.orders_id = onnumber -> into total; --賦值-> -- 判斷是否需要營業稅收-> if taxable then -> select total + (total/100 * taxrate) into total;-> end if;-> -- 賦值給輸出參數-> select total into ototal;-> end-> // Query OK, 0 rows affected (0.01 sec) DELIMITER ; -- 執行存儲過程 mysql> call sp_ordertotal(3,false,@total)// Query OK, 1 row affected (0.00 sec)-- 查詢總價 mysql> select @total // +----------+ | @total | +----------+ | 41244.90 | +----------+ 1 row in set (0.00 sec)

存儲函數

創建存儲函數

上一篇中，我們列舉不少mysql自帶的函數，但是有些時候自帶函數并不能很好滿足我們的需求，此時就需要自定義存儲函數了，存儲函數與存儲過程有些類似，簡單來說就是封裝一段sql代碼，完成一種特定的功能，并返回結果。其語法如下：

CREATE FUNCTION 函數([參數類型數據類型[,….]]) RETURNS 返回類型 BEGINSQL語句.....RETURN (返回的數據)END

與存儲過程不同的是，存儲函數中不能指定輸出參數(OUT)和輸入輸出參數(INOUT)類型。存儲函數只能指定輸入類型而且不能帶IN。同時存儲函數可以通過RETURN命令將處理的結果返回給調用方。注意必須在參數列表后的RETURNS（?該值的RETURNS多個S，務必留意）命令中預先指定返回值的類型。如下創建一個計算斐波那契數列的函數

-- 創建存儲函數 mysql> create function fn_factorial(num int) returns int-> begin-> declare result int default 1;-> while num > 1 do -> set result = result * num ;-> set num = num -1 ;-> end while;-> return result;-> end-> // Query OK, 0 rows affected (0.01 sec)-- 使用select 執行存儲函數 mysql> select fn_factorial(5); +-----------------+ | fn_factorial(5) | +-----------------+ | 120 | +-----------------+ 1 row in set (0.00 sec)mysql> select fn_factorial(0),fn_factorial(5),fn_factorial(10); +-----------------+-----------------+------------------+ | fn_factorial(0) | fn_factorial(5) | fn_factorial(10) | +-----------------+-----------------+------------------+ | 1 | 120 | 3628800 | +-----------------+-----------------+------------------+ 1 row in set (0.00 sec)

這里命名存儲函數時使用了【fn_】作為開頭，這樣可以更容易區分與【sp_】開頭的存儲過程，從上述語句可以看出前面在存儲過程分析的流程語句也是可以用于存儲函數的，同樣的，DECLARE聲明變量和SET設置變量也可用于存儲函數，當然包括定義異常處理語句也是適應的,請注意執行存儲函數使用的是select關鍵字，可同時執行多個存儲函數，嗯，存儲函數就這樣定義，是不是跟存儲過程很相似呢？但還是有區別的，這點留到后面分析。ok~，為了進一步熟悉存儲函數，下面編寫一個用于向user插入用戶的存儲函數：

-- 創建存儲函數fn_insert_user create function fn_insert_user(name varchar(32),sex char(1)) returns int begin insert into user (username,pinyin,birthday,sex,address) values(name,null,null,sex,null); return LAST_INSERT_ID(); --返回最后插入的ID值 end// Query OK, 0 rows affected (0.00 sec) DELIMITER ; -- 執行存儲函數 mysql> select fn_insert_user('xiaolong',1); +------------------------------+ | fn_insert_user('xiaolong',1) | +------------------------------+ | 101 | +------------------------------+ 1 row in set (0.00 sec)-- 查詢已插入的數據 mysql> select * from user where id = 101; +-----+----------+-------------+----------+------+---------+ | id | username | pinyin | birthday | sex | address | +-----+----------+-------------+----------+------+---------+ | 101 | xiaolong | NULL | NULL | 1 | NULL | +-----+----------+-------------+----------+------+---------+ 1 row in set (0.00 sec)

顯然數據插入成功了，其中 LAST_INSERT_ID()會返回最后插入的ID值，這里我們僅作為演示，因為實際開發中，我們一般更傾向于使用存儲函數執行查詢操作或者是數據的處理操作，對于更新插入刪除這樣的操作，使用較少。

刪除存儲函數

刪除存儲函數使用以下語句，如果需要修改存儲函數，建議直接刪除再重建，直接修改比較麻煩。

DROP FUNCTION [IF EXISTS] fn_name;

存儲過程與存儲函數的區別

關于存儲過程與存儲函數的區別，主要給出以下幾點。

存儲過程可以有多個in,out,inout參數，而存儲函數只有輸入參數類型，而且不能帶in
存儲過程實現的功能要復雜一些；而存儲函數的單一功能性(針對性)更強。
存儲過程可以返回多個值；存儲函數只能有一個返回值。
存儲過程一般獨立的來執行；而存儲函數可以作為其他SQL語句的組成部分來出現。
存儲過程可以調用存儲函數。但函數不能調用存儲過程。

觸發器

觸發器的概念及其語法

觸發器可以簡單理解一種特殊的存儲過程，之前存儲過程的變量定義及流程語句同樣適合觸發器，唯一不同的是我們只需要定義觸發器，而不用手動調用觸發器。從事件觸發的角度來說，觸發器編寫的過程就是觸發事件定義的過程，因為觸發器定義好后會隨著數據庫操作命令的執行而觸發，這些具體的操作是INSERT/UPDATE/DELETE。比如可以在user表中刪除記錄執行后，通過定義一個觸發器把刪除的數據自動添加到歷史表中保存以便以后可以進行其他操作。創建觸發器的語法如下：

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW BEGIN trigger_stmt END

其中：

trigger_name：觸發器名稱，用戶自行指定；
trigger_time：觸發時機，取值為 BEFORE 或 AFTER；
trigger_event：觸發事件，取值為 INSERT、UPDATE 或 DELETE；需要注意的是這些操作命令并不一定嚴格意義上的命令，因為像 LOAD DATA 和 REPLACE 語句也能觸發上述事件。LOAD DATA 語句用于將一個文件裝入到一個數據表中，是一系列的 INSERT 操作。REPLACE 語句類似INSERT 語句，當表中有 primary key 或 unique 索引時，如果插入的數據和原來 primary key 或 unique 索引一致時，會先刪除原來的數據，然后增加一條新數據，也就是說，一條 REPLACE 語句會等價于一條INSERT 語句或者一條 DELETE 語句和上一條 INSERT 語句。
tbl_name：表示在哪張表上建立觸發器；
trigger_stmt：觸發器程序體，可以是一句SQL語句或者流程語句
FOR EACH ROW ：在mysql中屬于固定寫法，指明觸發器以行作為執行單位，也就是當用戶執行刪除命令刪除3條數據，與刪除動作相關的觸發器也會被執行3次。

ok，下面了解一下如何在mysql中定義觸發器

創建觸發器

在日常的數據庫開發中，因業務需求，可能需要在插入更新刪除時留下數據的日志，這時采用觸發器來實現是個非常不錯的選擇，下面我們定義一個用戶刪除事件的觸發器，當用戶被刪除后自動把被刪除的數據添加到用戶歷史表user_history，歷史用戶表結構如下：

觸發器定義語句如下：

DELIMITER // -- 創建觸發器 mysql> create trigger trg_user_history after delete-> on user for each row-> begin-> insert into user_history(uid,name,pinyin,birth,sex,address,updated)-> values(OLD.id,OLD.name,OLD.pinyin,OLD.birth,OLD.sex,OLD.address,NOW());-> end-> // Query OK, 0 rows affected (0.02 sec) DELIMITER ;

上述sql中創建語句的形式與前面的存儲過程或者存儲函數都很類似，這里有點要注意的是，使用OLD/NEW關鍵字可以獲取數據變更前后的記錄，其中OLD用于AFTER時刻，而NEW用于BEFORE時刻的變更。如OLD.name表示從user表刪除的記錄的名稱。INSERT操作一般使用NEW關鍵字，UPDATE操作一般使用NEW和OLD，而DELETE操作一般使用OLD。現在我們從user表刪除一條數據，然后查看user_history表的數據。

-- 刪除user中id為60的用戶數據 mysql> delete from user where id =60; Query OK, 1 row affected (0.00 sec)-- 查看歷史表 mysql> select * from user_history; +----+-----+-------------+------------+---------------------+------+---------+---------------------+ | id | uid | name | pinyin | birth | sex | address | updated | +----+-----+-------------+------------+---------------------+------+---------+---------------------+ | 1 | 60 | 高余糧10 | gaoyuliang | 1999-01-01 00:00:00 | 1 | NULL | 2017-04-21 09:39:23 | +----+-----+-------------+------------+---------------------+------+---------+---------------------+ 1 row in set (0.00 sec)

顯然我們定義的觸發器已生效了。

查看觸發器

如果需要查看定義好的觸發器可以使用以下語句：

SHOW TRIGGERS [FROM schema_name];

其中，schema_name 即 Schema 的名稱，在 MySQL 中 Schema 和 Database 是一樣的，Schema指定為數據庫名即可。

mysql> SHOW TRIGGERS \G; *************************** 1. row ***************************Trigger: trg_user_history --觸發器名稱Event: DELETE --觸發事件Table: user --觸發器作用的表Statement: begin insert into user_history(uid,name,pinyin,birth,sex,address,updated) values(OLD.id,OLD.name,OLD.pinyin,OLD.birth,OLD.sex,OLD.address,NOW()); endTiming: AFTERCreated: 2017-04-21 09:27:56.58sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONDefiner: root@localhost character_set_client: utf8 collation_connection: utf8_general_ciDatabase Collation: utf8_general_ci 1 row in set (0.00 sec)

刪除觸發器

刪除觸發器可以使用以下語句

DROP TRIGGER 觸發器名稱

游標

在前面的分析中可知sql的檢索操作返回的數據幾乎都是以整個集合的形式，也就是說sql善于將多條查詢記錄集中到一起并返回，倘若現在需要一行行地處理查詢的結果，這對于sql語句來說確實是個難題，好在存在一種稱為游標的技術可以解決這個問題，所謂的游標就就是可以將檢索出來的數據集合保存在內存中然后依次取出每條數據進行處理，這樣就解決了sql語句無法進行行記錄處理的難題，游標的讀取圖解如下：

其中有個指針的概念，指針指明了當前行記錄的信息，在游標的處理過程中通過移動指針進行逐行讀取數據。要明白的是，游標一般結合存儲過程或存儲函數或觸發器進行使用，ok~，理解了游標的概念后，看看其定義語法

-- 聲明游標 DECLARE cursor_name CURSOR FOR SELECT 語句;-- 打開游標 OPEN cursor_name;-- 從游標指針中獲取數據 FETCH cursor_name INTO 變量名 [,變量名2,...];--關閉游標 CLOSE cursor_name

在使用游標前需要對其進行聲明，其中cursor_name表示游標名，CURSOR FOR是固定寫法，SELECT 是檢索語句，把檢索出來的數據存放到游標中等待處理。下面我們通過一個案例演示并理解游標的使用

mysql> DELIMITER // -- 創建存儲過程 mysql> create procedure sp_cursor(out result text)-> begin -> declare flag bit default 0;--定義標識變量用于判斷是否退出循環-> declare tmp varchar(20);-- 定義臨時存儲變量-> declare cur cursor for select distinct name from user where id < 20;-- 聲明游標-> declare continue handler for not found set flag = 1; --異常處理并設置flag=1-> open cur; -- 打開游標-> while flag!=1 do -> fetch cur into tmp ; --從游標中取值并存放到tmp中-> if flag !=1 then-> set result = concat_ws(',',result,tmp); --拼接每次獲取的結果-> end if;-> end while;-> close cur; --關閉游標-> end-> //Query OK, 0 rows affected (0.02 sec)mysql> DELIMITER ; --執行 mysql> call sp_user_cursor(@result); Query OK, 0 rows affected (0.00 sec) -- 查詢結果 mysql> select @result; +------------------------------------------------+ | @result | +------------------------------------------------+ | 王五,張曹宇,李達康,張書記,任在明 | +------------------------------------------------+ 1 row in set (0.00 sec)

上述的存儲過程是用于查詢出id小于20的用戶名稱，并拼接成一個以逗號隔開的字符串輸出。我們聲明了一個flag的變量用于標識是否結束while循環，同時也聲明了tmp變量用于存儲每次從游標中獲取的行數據，因為我們定義游標是從user表中查詢name字段的數據，因此只需要一個tmp變量就行了，如果需要查詢user中多個字段，則聲明多個tmp字段并在獲取數據時以fetch cur into tmp [,tmp2,tmp3,...];形式即可，請注意在使用游標前必須先打開，使用open cur;語句，而且只有在打開游標后前面定義的select語句開正式開始執行。循環獲取cur中的數據使用了while流程語句，這里我們還定義了前面分析過的異常處理語句即

declare continue handler for not found set flag = 1; --異常處理并設置flag=1

在發生not found 的異常時將flag設置為1，并通過聲明為continue而讓程序繼續執行。這樣處理的理由是fetch cur into tmp語句執行時，如果游標的指針無法讀取下一行數據時就會拋出NOT FOUND異常，拋出后由已聲明的異常程序處理，并設置flag為1，以此來結束循環，注意拋出異常后程序還會繼續執行，畢竟聲明了continue。所以最后一次判斷if flag !=1 then是必要的。最后執行完成，通過close cur?關閉游標，這樣整個游標的使用就完成了。

事務處理

事務處理是數據庫中的一個大塊頭，涉及到數據的完整性與一致性問題，由于mysql存在多種數據存儲引擎提供給用戶選擇，但不是所有的引擎都支持事務處理，常見的引擎有：MyISAM和InnoDB，MyISAM是默認高速的引擎并不支持事務功能，InnoDB支持行鎖定和事務處理，速度比MyISAM稍慢。事實上前面我們在創建表時都指明存儲引擎為InnoDB，本篇中我們也將采用InnoDB引擎進行分析，畢竟InnoDB是支持事務功能的。

事務的概念

先看一個經典銀行轉賬案例，A向B的銀行卡轉賬1000元，這里分兩個主要事件，一個是A向B轉賬1000，那么A的銀行卡轉賬成功后必須在原來的數額上扣掉1000元，另一個是B收到了A的轉款，B的銀行卡上數額必須增加1000元，這兩個步驟是必須都成功才算轉賬成功，總不能A轉賬B后，A的數額沒有變化而B增加了1000元吧？這樣銀行不得虧死了？因此兩個步驟只要有一個失敗，此次轉賬的結果就是失敗。但我們在執行sql語句時，兩個動作是分兩個語句執行的，萬一執行完一個突然沒電了另外一個沒有執行，那豈不出問題了？此時就需要事務來解決這個問題了，所謂的事物就是保證以上的兩個步驟在同一個環境中執行，只要其中一個失敗，事務就會撤銷之前的操作，回滾的沒轉賬前的狀態，如果兩個都執行成功，那么事務就認為轉成成功了。這就是事務的作用。

對事務有了初步理解后，進一步了解事務的官方概念，事務是DBMS的執行單位。它由有限個數據庫操作語句組成。但不是任意的數據庫操作序列都能成為事務。一般來說，事務是必須滿足4個條件（ACID）

原子性（Autmic）：一個原子事務要么完整執行，要么干脆不執行。也就是說，工作單元中的每項任務都必須正確執行，如果有任一任務執行失敗，則整個事務就會被終止并且此前對數據所作的任何修改都將被撤銷。如果所有任務都被成功執行，事務就會被提交，那么對數據所作的修改將會是永久性的
一致性（Consistency）：一致性代表了底層數據存儲的完整性。它是由事務系統和應用開發人員共同來保證。事務系統通過保證事務的原子性，隔離性和持久性來滿足這一要求; 應用開發人員則需要保證數據庫有適當的約束(主鍵，引用完整性等)，并且工作單元中所實現的業務邏輯不會導致數據的不一致(數據預期所表達的現實業務情況不相一致)。例如，在剛才的AB轉賬過程中，從A賬戶中扣除的金額必須與B賬戶中存入的金額相等。
隔離性（Isolation）：隔離性是指事務必須在不干擾其他事務的前提下獨立執行，也就是說，在事務執行完畢之前，其所訪問的數據不能受系統其他部分的影響。
持久性（Durability）：持久性指明當系統或介質發生故障時，確保已提交事務的更新數據不能丟失，也就意味著一旦事務提交，DBMS保證它對數據庫中數據的改變應該是永久性的，耐得住任何系統故障，持久性可以通過數據庫備份和恢復來保證。

事務控制流程實戰

在使用事務處理可能涉及到以下命令：

-- 聲明事務的開始 BEGIN(或START TRANSACTION);-- 提交整個事務 COMMIT;-- 回滾到事務初始狀態 ROLLBACK;

下面通過刪除user表中的用戶數據，然后再回滾來演示上述命令的作用：

-- 先查看user表中的數據 mysql> select * from user; +----+-----------+--------------+---------------------+------+--------------------+ | id | name | pinyin | birth | sex | address | +----+-----------+--------------+---------------------+------+--------------------+ | 1 | 王五 | wangwu | NULL | 1 | 北京市朝陽區 | | 2 | 張曹宇 | zhangcaoyu | 1990-08-05 00:00:00 | 1 | 廣東省汕頭市 | | 3 | 李達康 | lidakang | 1909-12-12 00:00:00 | 1 | 北京朝陽 | | 10 | 張書記 | zhangshuji | 1999-06-06 00:00:00 | 0 | 北京市朝陽區 | | 16 | 任在明 | renzaiming | 1996-12-01 00:00:00 | 1 | 廣東省廣州市 | | 22 | 陳小明 | chenxiaoming | 1995-05-10 00:00:00 | 0 | 廣東省深圳市 | | 24 | 任傳海 | renchuanhai | 1992-03-08 00:00:00 | 1 | 海南三亞 | +----+-----------+--------------+---------------------+------+--------------------+ 7 rows in set (0.00 sec);-- 開始事務 mysql> begin; Query OK, 0 rows affected (0.00 sec); -- 刪除ID為24的用戶 mysql> delete from user where id =24; Query OK, 1 row affected (0.00 sec);-- 刪除完成后再次查看user表數據，顯然ID為24的數據已被刪除 mysql> select * from user; +----+-----------+--------------+---------------------+------+--------------------+ | id | name | pinyin | birth | sex | address | +----+-----------+--------------+---------------------+------+--------------------+ | 1 | 王五 | wangwu | NULL | 1 | 北京市朝陽區 | | 2 | 張曹宇 | zhangcaoyu | 1990-08-05 00:00:00 | 1 | 廣東省汕頭市 | | 3 | 李達康 | lidakang | 1909-12-12 00:00:00 | 1 | 北京朝陽 | | 10 | 張書記 | zhangshuji | 1999-06-06 00:00:00 | 0 | 北京市朝陽區 | | 16 | 任在明 | renzaiming | 1996-12-01 00:00:00 | 1 | 廣東省廣州市 | | 22 | 陳小明 | chenxiaoming | 1995-05-10 00:00:00 | 0 | 廣東省深圳市 | +----+-----------+--------------+---------------------+------+--------------------+ 6 rows in set (0.00 sec);-- 執行回滾操作rollback mysql> rollback; Query OK, 0 rows affected (0.00 sec)-- 再次查看數據，可見ID為24的用戶數據已恢復 mysql> select * from user; +----+-----------+--------------+---------------------+------+--------------------+ | id | name | pinyin | birth | sex | address | +----+-----------+--------------+---------------------+------+--------------------+ | 1 | 王五 | wangwu | NULL | 1 | 北京市朝陽區 | | 2 | 張曹宇 | zhangcaoyu | 1990-08-05 00:00:00 | 1 | 廣東省汕頭市 | | 3 | 李達康 | lidakang | 1909-12-12 00:00:00 | 1 | 北京朝陽 | | 10 | 張書記 | zhangshuji | 1999-06-06 00:00:00 | 0 | 北京市朝陽區 | | 16 | 任在明 | renzaiming | 1996-12-01 00:00:00 | 1 | 廣東省廣州市 | | 22 | 陳小明 | chenxiaoming | 1995-05-10 00:00:00 | 0 | 廣東省深圳市 | | 24 | 任傳海 | renchuanhai | 1992-03-08 00:00:00 | 1 | 海南三亞 | +----+-----------+--------------+---------------------+------+--------------------+ 7 rows in set (0.00 sec)

從上述一系列操作中，從啟動事務到刪除用戶數據，再到回滾數據，體現了事務控制的過程，這里我們還沒使用COMMIT，如果剛才把rollback改成commit，那么事務就提交了，數據也就真的刪除了。下面我們再次來演示刪除數據的過程，并且這次使用commit提交事務。

-- 先添加一條要刪除數據 mysql> insert into user values(30,'要被刪除的數據',null,null,1,null); Query OK, 1 row affected (0.01 sec); -- 查看數據 mysql> select * from user; +----+-----------------------+--------------+---------------------+------+--------------------+ | id | name | pinyin | birth | sex | address | +----+-----------------------+--------------+---------------------+------+--------------------+ | 1 | 王五 | wangwu | NULL | 1 | 北京市朝陽區 | | 2 | 張曹宇 | zhangcaoyu | 1990-08-05 00:00:00 | 1 | 廣東省汕頭市 | | 3 | 李達康 | lidakang | 1909-12-12 00:00:00 | 1 | 北京朝陽 | | 10 | 張書記 | zhangshuji | 1999-06-06 00:00:00 | 0 | 北京市朝陽區 | | 16 | 任在明 | renzaiming | 1996-12-01 00:00:00 | 1 | 廣東省廣州市 | | 22 | 陳小明 | chenxiaoming | 1995-05-10 00:00:00 | 0 | 廣東省深圳市 | | 24 | 任傳海 | renchuanhai | 1992-03-08 00:00:00 | 1 | 海南三亞 | | 30 | 要被刪除的數據 | NULL | NULL | 1 | NULL | +----+-----------------------+--------------+---------------------+------+--------------------+ 8 rows in set (0.00 sec);-- 開啟新事務 mysql> begin; Query OK, 1 row affected (0.00 sec); -- 刪除數據 mysql> delete from user where id =30; Query OK, 1 row affected (0.00 sec); -- 提交事務 mysql> commit; Query OK, 0 rows affected (0.00 sec); -- 回滾數據 mysql> rollback; Query OK, 0 rows affected (0.00 sec); -- 查看數據 mysql> select * from user; +----+-----------+--------------+---------------------+------+--------------------+ | id | name | pinyin | birth | sex | address | +----+-----------+--------------+---------------------+------+--------------------+ | 1 | 王五 | wangwu | NULL | 1 | 北京市朝陽區 | | 2 | 張曹宇 | zhangcaoyu | 1990-08-05 00:00:00 | 1 | 廣東省汕頭市 | | 3 | 李達康 | lidakang | 1909-12-12 00:00:00 | 1 | 北京朝陽 | | 10 | 張書記 | zhangshuji | 1999-06-06 00:00:00 | 0 | 北京市朝陽區 | | 16 | 任在明 | renzaiming | 1996-12-01 00:00:00 | 1 | 廣東省廣州市 | | 22 | 陳小明 | chenxiaoming | 1995-05-10 00:00:00 | 0 | 廣東省深圳市 | | 24 | 任傳海 | renchuanhai | 1992-03-08 00:00:00 | 1 | 海南三亞 | +----+-----------+--------------+---------------------+------+--------------------+ 7 rows in set (0.00 sec)

可以發現當刪除完數據后，使用commit提交了事務，此時數據就會被真正更新到數據庫了，即使使用rollback回滾也是沒有辦法恢復數據的。ok~，這就是事務控制最簡化的流程，事實上除了上述的回滾到事務的初始狀態外，還可以進行部分回滾，也就是我們可以自己控制事務發生錯誤時回滾到某個點，這需要利用以下命令來執行：

-- 定義保存點（回滾點） SAVEPOINT savepoint_name（名稱）;--回滾到指定保存點 ROLLBACK TO SAVEPOINT savepoint_name（名稱）;

演示案例如下：

-- 開啟事務 mysql> begin; Query OK, 0 rows affected (0.00 sec)mysql> insert into user values(31,'保存點1',null,null,1,null); Query OK, 1 row affected (0.00 sec); -- 創建保存點 mysql> savepoint sp; Query OK, 0 rows affected (0.00 sec)mysql> insert into user values(32,'保存點2',null,null,1,null); Query OK, 1 row affected (0.00 sec)mysql> insert into user values(33,'保存點3',null,null,1,null); Query OK, 1 row affected (0.00 sec)mysql> insert into user values(34,'保存點4',null,null,1,null); Query OK, 1 row affected (0.00 sec)mysql> select * from user; +----+------------+--------------+---------------------+------+--------------------+ | id | name | pinyin | birth | sex | address | +----+------------+--------------+---------------------+------+--------------------+ | 1 | 王五 | wangwu | NULL | 1 | 北京市朝陽區 | | 2 | 張曹宇 | zhangcaoyu | 1990-08-05 00:00:00 | 1 | 廣東省汕頭市 | | 3 | 李達康 | lidakang | 1909-12-12 00:00:00 | 1 | 北京朝陽 | | 10 | 張書記 | zhangshuji | 1999-06-06 00:00:00 | 0 | 北京市朝陽區 | | 16 | 任在明 | renzaiming | 1996-12-01 00:00:00 | 1 | 廣東省廣州市 | | 22 | 陳小明 | chenxiaoming | 1995-05-10 00:00:00 | 0 | 廣東省深圳市 | | 24 | 任傳海 | renchuanhai | 1992-03-08 00:00:00 | 1 | 海南三亞 | | 31 | 保存點1 | NULL | NULL | 1 | NULL | | 32 | 保存點2 | NULL | NULL | 1 | NULL | | 33 | 保存點3 | NULL | NULL | 1 | NULL | | 34 | 保存點4 | NULL | NULL | 1 | NULL | +----+------------+--------------+---------------------+------+--------------------+ 11 rows in set (0.00 sec); -- 回滾到保存點 mysql> rollback to savepoint sp; Query OK, 0 rows affected (0.00 sec); -- 查看數據 mysql> select * from user; +----+------------+--------------+---------------------+------+--------------------+ | id | name | pinyin | birth | sex | address | +----+------------+--------------+---------------------+------+--------------------+ | 1 | 王五 | wangwu | NULL | 1 | 北京市朝陽區 | | 2 | 張曹宇 | zhangcaoyu | 1990-08-05 00:00:00 | 1 | 廣東省汕頭市 | | 3 | 李達康 | lidakang | 1909-12-12 00:00:00 | 1 | 北京朝陽 | | 10 | 張書記 | zhangshuji | 1999-06-06 00:00:00 | 0 | 北京市朝陽區 | | 16 | 任在明 | renzaiming | 1996-12-01 00:00:00 | 1 | 廣東省廣州市 | | 22 | 陳小明 | chenxiaoming | 1995-05-10 00:00:00 | 0 | 廣東省深圳市 | | 24 | 任傳海 | renchuanhai | 1992-03-08 00:00:00 | 1 | 海南三亞 | | 31 | 保存點1 | NULL | NULL | 1 | NULL | +----+------------+--------------+---------------------+------+--------------------+ 8 rows in set (0.00 sec); -- 提交事務 mysql> commit ;

關于commit有點需要知道的，在mysql中每條sql命令都會被自動commit，這種功能稱為自動提交功能，是默認開啟的。前面我們在執行事務使用了begin命令開啟了事務，這時自動提交在事務中就關閉了直到事務被手動commit。當然我們也可以手動控制開啟或者關閉此功能，語法如下：

-- 關閉自動提交功能 SET AUTOCOMMIT=0; -- 開啟自動提交功能 SET AUTOCOMMIT=1;

鎖以及事務處理分離水平(隔離級別)

了解悲觀鎖和樂觀鎖的概念?

悲觀鎖：假設會發生并發沖突，回避一切可能違反數據完整性的操作。
樂觀鎖：假設不會發生并發沖突，只在提交操作時檢查是否違反數據完整性，注意樂觀鎖并不能解決臟讀的問題(關于臟讀稍后解析)。

在一般情況下，悲觀鎖依靠數據庫的鎖機制實現，以保證操作最大程度的排他性和獨占性，因而會導致數據庫性能的大量開銷和并發性很低，特別是對長事務而言，這種開銷往往過于巨大而無法承受。為了解決這樣的問題，樂觀鎖機制便出現了。樂觀鎖，大多情況下是基于數據版本（ Version ）記錄機制實現。何謂數據版本？即為數據增加一個版本標識，在基于數據庫表的版本解決方案中，一般是通過為數據庫表增加一個 “version” 字段來實現。讀取出數據時，將此版本號一同讀出，之后更新時，對此版本號加一。此時，將提交數據的版本數據與數據庫表對應記錄的當前版本信息進行比對，如果提交的數據版本號大于數據庫表當前版本號，則給予更新，否則認為是過期數據。ok~，關于悲觀鎖和樂觀鎖的簡單概念就先了解到這。

mysql中的共享鎖與排他鎖

在mysql中，為了保證數據一致性和防止數據處理沖突，引入了加鎖和解鎖的技術，這樣可以使數據庫中特定的數據在使用時不讓其他用戶(進程或事務)操作而為該數據加鎖，直到該數據被處理完成后再進行解鎖。根據使用目的不同把鎖分為共享鎖定(也稱為讀取鎖定)和排他鎖定(寫入鎖定)。

共享鎖定：將對象數據變為只讀形式的鎖定，這樣就允許多方同時讀取一個數據，此時數據將無法修改。
排他鎖定：在對數據進行insert/update/delete時進行鎖定，在此時其他用戶(進程或事務)一律不能讀取數據，從而也保證數據完整性。

以上兩種鎖都屬于悲觀鎖的應用，還有一點，根據鎖定粒度的不同，可分為行鎖定(共享鎖和排他鎖使用應用的就是行鎖定)，表鎖定，數據庫鎖定，可見粒度的不同將影響用戶(進程或事務)對數據操作的并發性，目前mysql支持行鎖定和表鎖定。

事務處理分離水平

事實上，鎖的出現更多的是為了在多個用戶(進程或事務)同時執行更新操作時保證數據的完整性和一致性，但隨之而來的問題是當數據的鎖定時間越長，數據同時運行性也會隨之降低。也就意味著當一個用戶(進程或事務)對數據保存鎖定時，其他用戶(進程或事務)只能等待鎖定解鎖，這樣也就導致并發訪問該數據的同時性較低。所以在多用戶(進程或事務)對數據進行更新或者訪問的同時如何保證數據的完整性和一致性，這樣的情況下需要有一個相對折中的妥協，因為并不是頻繁鎖定數據或者極致提供同時運行性就是合理的，為了描述這個問題數據庫中引入分離水平(有些地方稱為隔離級別)的概念來確定事務處理之間的相互影響程度。其規則描述：分離水平越高，數據的完整性也就越高，但同時運行性下降，相反如果分離水平越低數據完整性越低，同時運行性也就提高了。在典型的應用程序中，多個事務并發運行，經常會操作相同的數據來完成各自的任務，并發雖然是常見的，但可能會導致不同分離水平下發生不同的數據讀取情況，4種分離水平以及可能導致的情景如下：

分離水平讀未提交不可重復讀幻讀

READ UNCOMMITTED	會	會	會
READ COMMITTED	不	會	會
REPEATABLE READ	不	不	會
SERIALIZABLE	不	不	不

??
四種分離水平(隔離級別)

READ_UNCOMMITTED：這是事務最低的分離水平(隔離級別)，它充許別外一個事務可以看到這個事務未提交的數據，會出現臟讀、不可重復讀、幻讀（分離水平最低，并發性能高）
READ_COMMITTED：保證一個事務修改的數據提交后才能被另外一個事務讀取。另外一個事務不能讀取該事務未提交的數據。可以避免臟讀，但會出現不可重復讀、幻讀問題（鎖定正在讀取的行，mysql默認隔離級別）
REPEATABLE_READ：可以防止臟讀、不可重復讀，但會出幻讀（鎖定所讀取的所有行）
SERIALIZABLE：這是花費最高代價但是最可靠的事務分離水平(隔離級別)，事務被處理為順序執行。保證所有的情況不會發生（鎖表,并發性及其低）

讀未提交、不可重復讀，幻讀

讀未提交，也稱臟讀，臟讀發生在一個事務讀取了另一個事務改寫但尚未提交的數據時。如果改寫在稍后被回滾了，那么第一個事務獲取的數據就是無效的。
不可重復讀：不可重復讀發生在一個事務執行相同的查詢兩次或兩次以上，但是每次都得到不同的數據時。這通常是因為另一個并發事務在兩次查詢期間進行了更新。請注意，不可重復讀重點是修改數據導致的(修改數據時排他讀);，例如：在事務1中，客戶管理人員在讀取了張曹宇的生日為1990-08-05,操作并沒有完成
select birth from user where name ='張曹宇' ;
- 1
在事務2中，這時張曹宇自己修改生日為1990-06-05,并提交了事務.
begin; -- 其他操作省略 update user set birth='1990-06-05' where name ='張曹宇' ; commit;
- 1
- 2
- 3
- 4
在事務1中，客戶管理人員再次讀取了張曹宇的生日時，生日變為1990-06-05,從而導致在一個事務中前后兩次讀取的結果并不一致，導致了不可重復讀。
幻讀：幻讀與不可重復讀類似。它發生在一個事務（T1）讀取了幾行數據，接著另一個并發事務（T2）插入了一些數據時。在隨后的查詢中，第一個事務（T1）就會發現多了一些原本不存在的記錄。請注意，幻讀重點是插入或者刪除數據導致的（對滿足條件的數據行集進行鎖定），同樣的道理，在事務1中，客戶管理查詢所有用戶生日在1990-06-05的人只有20個，操作并沒有完成，此時事務2中，剛好有一個新注冊的用戶，其生日也1990-06-05，在事務2中插入新用戶并提交了事務，此時在事務1中再次查詢時，所有用戶生日在1990-06-05的人變為21個了，從也就導致了幻讀。

ok~，在理解了讀未提交、不可重復的、幻讀后，再次看回表格，小結一下，可以發現在分離水平為READ UNCOMMITTED時，將會導致3種情況的出現，因此這樣的分離水平一般是不建議使用的。在分離水平為READ COMMITTED時，不會導致臟讀，但會導致不可重復讀和幻讀，要回避這樣的現象，必須采用分離水平為REPEATABLE READ，這樣就只會導致幻讀，而當分離水平為SERIALIZABLE時，3種現象都不復存在。但請注意這并不意味著所有情況下采用分離水平為SERIALIZABLE都是合理的，就如前面所分析的分離水平越高，數據的完整性也就越高，但同時運行性下降。在大多數情況下，我們會在根據應用的實際情景選擇分離水平為REPEATABLE READ或者READ COMMITTED(MySQL默認的事務分離水平為REPEATABLE READ)，這樣既能一定程度上保證數據的完整性也同時提供了數據的同時運行性，在mysql中我們可以使用以下語法設置事務分離水平

-- 設置當前連接的事務分離水平 SET SESSION TRANSACTION ISOLATION LEVEL 事務分離水平;--設置全部連接（包括新連接）的事務分離水平 SET GLOBAL TRANSACTION ISOLATION LEVEL 事務分離水平;

事務、分離水平、鎖之間的關系

通過上述的分析，我們也理解了事務、鎖和分離水平的概念，但鎖和事務以及分離水平關系如何呢？實際上，事務是解決多條sql執行執行過程的原子性、一致性、隔離性、持久性的整體解決方案，而事務分離水平則是并發控制的整體解決方案，其實際是綜合利用各種類型的鎖來解決并發問題。鎖是數據庫并發控制的內部基礎機制。對應用開發人員來說，只有當事務分離水平無法解決并發問題和需求時，才有必要在語句中手動設置鎖。關于鎖的鎖定，對于UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及數據集加排他鎖（X)；對于普通SELECT語句，InnoDB不會加任何鎖，事務可以通過以下語句顯示給記錄集加共享鎖或排他鎖。請注意InnoDB行鎖是通過給索引上的索引項加鎖來實現的，也就是說，只有通過索引條件檢索數據，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖。

-- 共享鎖（S） SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;-- 排他鎖（X) SELECT * FROM table_name WHERE ... FOR UPDATE;

事務原理概要

最后我們來簡單了解一下事務內部實現的原理概要，事實上事務的處理機制是通過記錄更新日志而實現的，其中與事務處理相關的日志是UNDO日志和REDO日志。

UNDO日志亦稱為回滾端，在進行數據插入、更新、刪除的情景下，保存變更前的數據，原理圖如下：
在表中保存了指向UNDO日志的指針，rollback執行時根據這個指針來獲取舊數據并覆蓋到表中，rollback執行完成后或者commit后UNDO日志將被刪除。UNDO還有另外一種作用，當A用戶正在更新數據時，還沒提交，而B用戶也需要使用該數據，這時不可能讓B讀取未提交的數據，因此會將存在UNDO表中的數據提供給B用戶。這就是事務回滾的簡單模型。
REDO日志主要是事務提交后由于錯誤或者斷電停機等原因使數據無法更新到數據庫中時，REDO日志將提供數據恢復作用。其原理是通過數據庫中的一段緩沖的數據先實時更新到REDO日志再更新到數據庫，也就是說平常的更新操作并非一步執行到位的，而是首選更新到REDO日志中，再更新到數據庫文件的。所以REDO日志才能用戶故障數據的恢復。

ok~，關于事務的原理先關注這么多，到此本篇也就完結。

from:?https://blog.csdn.net/javazejian/article/details/69857949

總結

以上是生活随笔為你收集整理的MySQL的进阶实战篇的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： Grafana + Zabbix ---
下一篇：深入理解Java类型信息(Class对象