通過47 張圖帶你 MySQL 進階

我們在 MySQL 入門篇主要介紹了基本的 SQL 命令、資料型別和函數，在區域性以上知識後，你就可以進行 MySQL 的開發工作了，但是如果要成為一個合格的開發人員，你還要具備一些更高階的技能，下面我們就來探討一下 MySQL 都需要哪些高階的技能

MySQL 儲存引擎

儲存引擎概述

資料庫最核心的一點就是用來儲存資料，資料儲存就避免不了和磁碟打交道。那麼資料以哪種方式進行儲存，如何儲存是儲存的關鍵所在。所以儲存引擎就相當於是資料儲存的發動機，來驅動資料在磁碟層面進行儲存。

MySQL 的架構可以按照三層模式來理解

儲存引擎也是 MySQL 的組建，它是一種軟體，它所能做的和支援的功能主要有

• 並行
• 支援事務
• 完整性約束
• 物理儲存
• 支援索引
• 效能幫助

MySQL 預設支援多種儲存引擎，來適用不同資料庫應用，使用者可以根據需要選擇合適的儲存引擎，下面是 MySQL 支援的儲存引擎

• MyISAM
• InnoDB
• BDB
• MEMORY
• MERGE
• EXAMPLE
• NDB Cluster
• ARCHIVE
• CSV
• BLACKHOLE
• FEDERATED

預設情況下，如果建立表不指定儲存引擎，會使用預設的儲存引擎，如果要修改預設的儲存引擎，那麼就可以在引數檔案中設定 default-table-type，能夠檢視當前的儲存引擎

show variables like 'table_type';複製程式碼

奇怪，為什麼沒有了呢？網上求證一下，在 5.5.3 取消了這個引數

可以通過下面兩種方法查詢當前資料庫支援的儲存引擎

show engines \g複製程式碼

在建立新表的時候，可以通過增加 ENGINE 關鍵字設定新建表的儲存引擎。

create table cxuan002(id int(10),name varchar(20)) engine = MyISAM;複製程式碼

上圖我們指定了 MyISAM 的儲存引擎。

如果你不知道表的儲存引擎怎麼辦？你可以通過 show create table 來檢視

如果不指定儲存引擎的話，從MySQL 5.1 版本之後，MySQL 的預設內建儲存引擎已經是 InnoDB了。建一張表看一下

如上圖所示，我們沒有指定預設的儲存引擎，下面檢視一下表

可以看到，預設的儲存引擎是 InnoDB。

如果你的儲存引擎想要更換，可以使用

alter table cxuan003 engine = myisam;複製程式碼

來更換，更換完成後回顯示 0 rows affected ，但其實已經操作成功

我們使用 show create table 檢視一下表的 sql 就知道

儲存引擎特性

下面會介紹幾個常用的儲存引擎以及它的基本特性，這些儲存引擎是 **MyISAM、InnoDB、MEMORY 和 MERGE **

MyISAM

在 5.1 版本之前，MyISAM 是 MySQL 的預設儲存引擎，MyISAM 並行性比較差，使用的場景比較少，主要特點是

• 不支援事務操作，ACID 的特性也就不存在了，這一設計是為了效能和效率考慮的。

• 不支援外來鍵操作，如果強行增加外來鍵，MySQL 不會報錯，只不過外來鍵不起作用。

• MyISAM 預設的鎖粒度是表級鎖，所以並行效能比較差，加鎖比較快，鎖衝突比較少，不太容易發生死鎖的情況。

• MyISAM 會在磁碟上儲存三個檔案，檔名和表名相同，擴充套件名分別是 .frm(儲存表定義)、.MYD(MYData,儲存資料)、MYI(MyIndex,儲存索引)。這裡需要特別注意的是 MyISAM 只快取索引檔案，並不快取資料檔案。

• MyISAM 支援的索引型別有 全域性索引(Full-Text)、B-Tree 索引、R-Tree 索引

  Full-Text 索引：它的出現是為了解決針對文字的模糊查詢效率較低的問題。

  B-Tree 索引：所有的索引節點都按照平衡樹的資料結構來儲存，所有的索引資料節點都在葉節點

  R-Tree索引：它的儲存方式和 B-Tree 索引有一些區別，主要設計用於儲存空間和多維資料的欄位做索引,目前的 MySQL 版本僅支援 geometry 型別的欄位作索引，相對於 BTREE，RTREE 的優勢在於範圍查詢。

• 資料庫所在主機如果宕機，MyISAM 的資料檔案容易損壞，而且難以恢復。

• 增刪改查效能方面：SELECT 效能較高，適用於查詢較多的情況

InnoDB

自從 MySQL 5.1 之後，預設的儲存引擎變成了 InnoDB 儲存引擎，相對於 MyISAM，InnoDB 儲存引擎有了較大的改變，它的主要特點是

• 支援事務操作，具有事務 ACID 隔離特性，預設的隔離級別是可重複讀(repetable-read)、通過MVCC（並行版本控制）來實現的。能夠解決髒讀和不可重複讀的問題。
• InnoDB 支援外來鍵操作。
• InnoDB 預設的鎖粒度行級鎖，並行效能比較好，會發生死鎖的情況。
• 和 MyISAM 一樣的是，InnoDB 儲存引擎也有 .frm檔案儲存表結構 定義，但是不同的是，InnoDB 的表資料與索引資料是儲存在一起的，都位於 B+ 數的葉子節點上，而 MyISAM 的表資料和索引資料是分開的。
• InnoDB 有安全的紀錄檔檔案，這個紀錄檔檔案用於恢復因資料庫崩潰或其他情況導致的資料丟失問題，保證資料的一致性。
• InnoDB 和 MyISAM 支援的索引型別相同，但具體實現因為檔案結構的不同有很大差異。
• 增刪改查效能方面，果執行大量的增刪改操作，推薦使用 InnoDB 儲存引擎，它在刪除操作時是對行刪除，不會重建表。

MEMORY

MEMORY 儲存引擎使用存在記憶體中的內容來建立表。每個 MEMORY 表實際只對應一個磁碟檔案，格式是 .frm。 MEMORY 型別的表存取速度很快，因為其資料是存放在記憶體中。預設使用 HASH 索引。

MERGE

MERGE 儲存引擎是一組 MyISAM 表的組合，MERGE 表本身沒有資料，對 MERGE 型別的表進行查詢、更新、刪除的操作，實際上是對內部的 MyISAM 表進行的。MERGE 表在磁碟上保留兩個檔案，一個是 .frm 檔案儲存表定義、一個是 .MRG 檔案儲存 MERGE 表的組成等。

選擇合適的儲存引擎

在實際開發過程中，我們往往會根據應用特點選擇合適的儲存引擎。

• MyISAM：如果應用程式通常以檢索為主，只有少量的插入、更新和刪除操作，並且對事物的完整性、並行程度不是很高的話，通常建議選擇 MyISAM 儲存引擎。
• InnoDB：如果使用到外來鍵、需要並行程度較高，資料一致性要求較高，那麼通常選擇 InnoDB 引擎，一般網際網路大廠對並行和資料完整性要求較高，所以一般都使用 InnoDB 儲存引擎。
• MEMORY：MEMORY 儲存引擎將所有資料儲存在記憶體中，在需要快速定位下能夠提供及其迅速的存取。MEMORY 通常用於更新不太頻繁的小表，用於快速存取取得結果。
• MERGE：MERGE 的內部是使用 MyISAM 表，MERGE 表的優點在於可以突破對單個 MyISAM 表大小的限制，並且通過將不同的表分佈在多個磁碟上， 可以有效地改善 MERGE 表的存取效率。

選擇合適的資料型別

我們會經常遇見的一個問題就是，在建表時如何選擇合適的資料型別，通常選擇合適的資料型別能夠提高效能、減少不必要的麻煩，下面我們就來一起探討一下，如何選擇合適的資料型別。

CHAR 和 VARCHAR 的選擇

char 和 varchar 是我們經常要用到的兩個儲存字串的資料型別，char 一般儲存定長的字串，它屬於固定長度的字元型別，比如下面

可以看到，不管你的值寫的是什麼，一旦指定了 char 字元的長度，如果你的字串長度不夠指定字元的長度的話，那麼就用空格來填補，如果超過字串長度的話，只儲存指定字元長度的字元。

這裡注意一點：如果 MySQL 使用了非 嚴格模式的話，上面表格最後一行是可以儲存的。如果 MySQL 使用了 嚴格模式 的話，那麼表格上面最後一行儲存會報錯。

如果使用了 varchar 字元型別，我們來看一下例子

可以看到，如果使用 varchar 的話，那麼儲存的位元組將根據實際的值進行儲存。你可能會疑惑為什麼 varchar 的長度是 5 ，但是卻需要儲存 3 個位元組或者 6 個位元組，這是因為使用 varchar 資料型別進行儲存時，預設會在最後增加一個字串長度，佔用1個位元組（如果列宣告的長度超過255，則使用兩個位元組）。varchar 不會填充空餘的字串。

一般使用 char 來儲存定長的字串，比如身份證號、手機號、郵箱等；使用 varchar 來儲存不定長的字串。由於 char 長度是固定的，所以它的處理速度要比 VARCHAR 快很多，但是缺點是浪費儲存空間，但是隨著 MySQL 版本的不斷演進，varchar 資料型別的效能也在不斷改進和提高，所以在許多應用中，VARCHAR 型別更多的被使用。

在 MySQL 中，不同的儲存引擎對 CHAR 和 VARCHAR 的使用原則也有不同

• MyISAM：建議使用固定長度的資料列替代可變長度的資料列，也就是 CHAR
• MEMORY：使用固定長度進行處理、CHAR 和 VARCHAR 都會被當作 CHAR 處理
• InnoDB：建議使用 VARCHAR 型別

TEXT 與 BLOB

一般在儲存較少的文字的時候，我們會選擇 CHAR 和 VARCHAR，在儲存巨量資料量的文字時，我們往往選擇 TEXT 和 BLOB；TEXT 和 BLOB 的主要差別是 BLOB 能夠儲存二進位制資料；而 TEXT 只能儲存字元資料，TEXT 往下細分有

• TEXT
• MEDIUMTEXT
• LONGTEXT

BLOB 往下細分有

• BLOB
• MEDIUMBLOB
• LONGBLOB

三種，它們最主要的區別就是儲存文字長度不同和儲存位元組不同，使用者應該根據實際情況選擇滿足需求的最小儲存型別，下面主要對 BLOB 和 TEXT 存在一些問題進行介紹

TEXT 和 BLOB 在刪除資料後會存在一些效能上的問題，為了提高效能，建議使用 OPTIMIZE TABLE 功能對錶進行碎片整理。

也可以使用合成索引來提高文字欄位（BLOB 和 TEXT）的查詢效能。合成索引就是根據大文字（BLOB 和 TEXT）欄位的內容建立一個雜湊值，把這個值存在對應列中，這樣就能夠根據雜湊值查詢到對應的資料行。一般使用雜湊演演算法比如 md5() 和 SHA1() ，如果雜湊演演算法生成的字串帶有尾部空格，就不要把它們存在 CHAR 和 VARCHAR 中，下面我們就來看一下這種使用方式

首先建立一張表，表中記錄 blob 欄位和 hash 值

向 cxuan005 中插入資料，其中 hash 值作為 info 的雜湊值。

然後再插入兩條資料

插入一條 info 為 cxuan005 的資料

如果想要查詢 info 為 cxuan005 的資料，可以通過查詢 hash 列來進行查詢

這是合成索引的例子，如果要對 BLOB 進行模糊查詢的話，就要使用字首索引。

其他優化 BLOB 和 TEXT 的方式：

• 非必要的時候不要檢索 BLOB 和 TEXT 索引
• 把 BLOB 或 TEXT 列分離到單獨的表中。

浮點數和定點數的選擇

浮點數指的就是含有小數的值，浮點數插入到指定列中超過指定精度後，浮點數會四捨五入，MySQL 中的浮點數指的就是 float 和 double，定點數指的是 decimal，定點數能夠更加精確的儲存和顯示資料。下面通過一個範例講解一下浮點數精確性問題

首先建立一個表 cxuan006 ，只為了測試浮點數問題，所以這裡我們選擇的資料型別是 float

然後分別插入兩條資料

然後執行查詢，可以看到查詢出來的兩條資料執行的舍入不同

為了清晰的看清楚浮點數與定點數的精度問題，再來看一個例子

先修改 cxuan006 的兩個欄位為相同的長度和小數位數

然後插入兩條資料

執行查詢操作，可以發現，浮點數相較於定點數來說，會產生誤差

日期型別選擇

在 MySQL 中，用來表示日期型別的有 DATE、TIME、DATETIME、TIMESTAMP，在

138 張圖帶你 MySQL 入門

這篇文中介紹過了日期型別的區別，我們這裡就不再闡述了。下面主要介紹一下選擇

• TIMESTAMP 和時區相關，更能反映當前時間，如果記錄的日期需要讓不同時區的人使用，最好使用 TIMESTAMP。
• DATE 用於表示年月日，如果實際應用值需要儲存年月日的話就可以使用 DATE。
• TIME 用於表示時分秒，如果實際應用值需要儲存時分秒的話就可以使用 TIME。
• YEAR 用於表示年份，YEAR 有 2 位（最好使用4位元）和 4 位格式的年。 預設是4位元。如果實際應用只儲存年份，那麼用 1 bytes 儲存 YEAR 型別完全可以。不但能夠節約儲存空間，還能提高表的操作效率。

MySQL 字元集

下面來認識一下 MySQL 字元集，簡單來說字元集就是一套文字元號和編碼、比較規則的集合。1960 年美國標準化組織 ANSI 釋出了第一個計算機字元集，就是著名的 ASCII(American Standard Code for Information Interchange) 。自從 ASCII 編碼後，每個國家、國際組織都研究了一套自己的字元集，比如 ISO-8859-1、GBK 等。

但是每個國家都使用自己的字元集為移植性帶來了很大的困難。所以，為了統一字元編碼，國際標準化組織(ISO) 指定了統一的字元標準 - Unicode 編碼，它容納了幾乎所有的字元編碼。下面是一些常見的字元編碼

對資料庫來說，字元集是很重要的，因為資料庫儲存的資料大多數都是各種文字，字元集對資料庫的儲存、效能、系統的移植來說都非常重要。

MySQL 支援多種字元集，可以使用 show character set; 來檢視所有可用的字元集

或者使用

select character_set_name, default_collate_name, description, maxlen from information_schema.character_sets;複製程式碼

來檢視。

使用 information_schema.character_set 來檢視字元集和校對規則。

索引的設計和使用

我們上面介紹到了索引的幾種型別並對不同的索引型別做了闡述，闡明瞭優缺點等等，下面我們從設計角度來聊一下索引，關於索引，你必須要知道的一點就是：索引是資料庫用來提高效能的最常用工具。

索引概述

所有的 MySQL 型別都可以進行索引，對相關列使用索引是提高 SELECT 查詢效能的最佳途徑。MyISAM 和 InnoDB 都是使用 BTREE 作為索引，MySQL 5 不支援函數索引，但是支援 字首索引。

字首索引顧名思義就是對列欄位的字首做索引，字首索引的長度和儲存引擎有關係。MyISAM 字首索引的長度支援到 1000 位元組，InnoDB 字首索引的長度支援到 767 位元組，索引值重複性越低，查詢效率也就越高。

在 MySQL 中，主要有下面這幾種索引

• 全域性索引(FULLTEXT)：全域性索引，目前只有 MyISAM 引擎支援全域性索引，它的出現是為了解決針對文字的模糊查詢效率較低的問題，並且只限於 CHAR、VARCHAR 和 TEXT 列。
• 雜湊索引(HASH)：雜湊索引是 MySQL 中用到的唯一 key-value 鍵值對的資料結構，很適合作為索引。HASH 索引具有一次定位的好處，不需要像樹那樣逐個節點查詢，但是這種查詢適合應用於查詢單個鍵的情況，對於範圍查詢，HASH 索引的效能就會很低。預設情況下，MEMORY 儲存引擎使用 HASH 索引，但也支援 BTREE 索引。
• B-Tree 索引：B 就是 Balance 的意思，BTree 是一種平衡樹，它有很多變種，最常見的就是 B+ Tree，它被 MySQL 廣泛使用。
• R-Tree 索引：R-Tree 在 MySQL 很少使用，僅支援 geometry 資料型別，支援該型別的儲存引擎只有MyISAM、BDb、InnoDb、NDb、Archive幾種，相對於 B-Tree 來說，R-Tree 的優勢在於範圍查詢。

索引可以在建立表的時候進行建立，也可以單獨建立，下面我們採用單獨建立的方式，我們在 cxuan004 上建立字首索引

我們使用 explain 進行分析，可以看到 cxuan004 使用索引的情況

如果不想使用索引，可以刪除索引，索引的刪除語法是

索引設計原則

建立索引的時候，要儘量考慮以下原則，便於提升索引的使用效率。

• 選擇索引位置，選擇索引最合適的位置是出現在 where 語句中的列，而不是 select 關鍵字後的選擇列表中的列。
• 選擇使用唯一索引，顧名思義，唯一索引的值是唯一的，可以更快速的確定某條記錄，例如學生的學號就適合使用唯一性索引，而學生的性別則不適合使用，因為不管搜尋哪個值，都差不多有一半的行。
• 為經常使用的欄位建立索引，如果某個欄位經常用作查詢條件，那麼這個欄位的查詢速度在極大程度上影響整個表的查詢速度，因此為這樣的欄位建立索引，可以提高整個表的查詢速度。
• 不要過度索引，限制索引數目，索引的數目不是越多越好，每個索引都會佔據磁碟空間，索引越多，需要的磁碟空間就越大。
• 儘量使用字首索引，如果索引的值很長，那麼查詢速度會受到影響，這個時候應該使用字首索引，對列的某幾個字元進行索引，可以提高檢索效率。
• 利用最左字首，在建立一個 n 列的索引時，實際上是建立了 MySQL 可利用的 n 個索引。多列索引可以起到幾個索引的作用，利用索引最左邊的列來匹配行，這樣的列稱為最左字首。
• 對於使用 InnoDB 儲存引擎的表來說，記錄會按照一定的順序儲存。如果有明確的主鍵定義，那麼會按照主鍵的順序進行儲存；如果沒有主鍵，但是有唯一索引，那麼就按照唯一索引的順序進行儲存。如果既沒有主鍵又沒有唯一索引，那麼表中會自動生成一個內部列，按照這個列的順序進行儲存。一般來說，使用主鍵的順序是最快的
• 刪除不再使用或者很少使用的索引

檢視

MySQL 從 5.0 開始就提供了檢視功能，下面我們對檢視功能進行介紹。

什麼是檢視

檢視的英文名稱是 view，它是一種虛擬存在的表。檢視對於使用者來說是透明的，它並不在資料庫中實際存在，檢視是使用資料庫行和列動態組成的表，那麼檢視相對於資料庫表來說，優勢體現在哪裡？

檢視相對於普通的表來說，優勢包含下面這幾項

• 使用檢視可以簡化操作：使用檢視我們不用關注表結構的定義，我們可以把經常使用的資料集合定義成檢視，這樣能夠簡化操作。
• 安全性：使用者對檢視不可以隨意的更改和刪除，可以保證資料的安全性。
• 資料獨立性：一旦檢視的結構 確定了， 可以遮蔽表結構變化對使用者的影響， 資料庫表增加列對檢視沒有影響；具有一定的獨立性

對檢視的操作

檢視的操作包括建立或者修改檢視、刪除檢視以及檢視檢視定義。

建立或修改檢視

使用 create view 來建立檢視

為了演示功能，我們先建立一張表 product 表，有三個欄位，id，name，price，下面是建表語句

create table product(id int(11),name varchar(20),price float(10,2));複製程式碼

然後我們向其中插入幾條資料

insert into product values(1, "apple","3.5"),(2,"banana","4.2"),(3,"melon","1.2");複製程式碼

插入完成後的表結構如下

然後我們建立檢視

create view v1 as select * from product;複製程式碼

然後我們檢視一下 v1 檢視的結構

可以看到我們把 product 中的資料放在了檢視中，也相當於是建立了一個 product 的副本，只不過這個副本跟表無關。

檢視使用

show tables;複製程式碼

也能看到所有的檢視。

刪除檢視的語法是

drop view v1;複製程式碼

能夠直接進行刪除。

檢視還有其他操作，比如查詢操作

你還可以使用

describe v1;複製程式碼

檢視表結構

更新檢視

update v1 set name = "grape" where id = 1;複製程式碼

儲存過程

MySQL 從 5.0 開始起就支援儲存過程和函數了。

那麼什麼是儲存過程呢？

儲存過程是在資料庫系統中完成一組特定功能的 SQL 語句集，它儲存在資料庫系統中，一次編譯後永久有效。那麼使用儲存過程有什麼優點呢？

• 使用儲存過程具有可封裝性，能夠隱藏複雜的 SQL 邏輯。
• 儲存過程可以接收引數，並返回結果
• 儲存過程效能非常高，一般用於批次執行語句

使用儲存過程有什麼缺點？

• 儲存過程編寫複雜
• 儲存過程對資料庫的依賴性比較強，可移植性比較差

儲存過程使用

儲存過程建立

在認識到儲存過程是什麼之後，我們就來使用一下儲存過程，這裡需要先了解一個小技巧，也就是 delimiter 的用法，delimiter 用於自定義結束符，什麼意思呢，如果你使用

delimiter ?複製程式碼

的話，那麼你在 sql 語句末使用 ; 是不能使 SQL 語句執行的，不信？我們可以看下

可以看到，我們在 SQL 語句的行末使用了 ; 但是我們卻沒有看到執行結果。下面我們使用

delimiter ;複製程式碼

恢復預設的執行條件再來看下

我們建立儲存過程首先要把 ; 替換為 ?，下面是一個儲存過程的建立語句

mysql> delimiter ?
mysql> create procedure sp_product()
    -> begin
    -> select * from product;
    -> end ?複製程式碼

儲存過程實際上是一種函數，所以建立完畢後，我們可以使用 call 方法來呼叫這個儲存過程

因為我們上面定義了使用 delimiter ? 來結尾，所以這裡也應該使用。

儲存過程也可以接受引數，比如我們定義一種接收引數的情況

然後我們使用 call 呼叫這個儲存過程

可以看到，當我們呼叫 id = 2 的時候，儲存過程的 SQL 語句相當於是

select * from product where id = 2;複製程式碼

所以只查詢出 id = 2 的結果。

儲存過程刪除

一次只能刪除一個儲存過程，刪除儲存過程的語法如下

drop procedure sp_product ;複製程式碼

直接使用 sp_product 就可以了，不用加 ()。

儲存過程檢視

儲存過程建立後，使用者可能需要需要檢視儲存過程的狀態等資訊，便於瞭解儲存過程的基本情況

我們可以使用

show create procedure proc_name;複製程式碼

變數的使用

在 MySQL 中，變數可分為兩大類，即系統變數和使用者變數，這是一種粗略的分法。但是根據實際應用又被細化為四種型別，即區域性變數、使用者變數、對談變數和全域性變數。

使用者變數

使用者變數是基於對談變數實現的，可以暫存，使用者變數與連線有關，也就是說一個使用者端定義的變數不能被其他使用者端使用看到。當用戶端退出時，連結會自動釋放。我們可以使用 set 語句設定一個變數

set @myId = "cxuan";複製程式碼

然後使用 select 查詢條件可以查詢出我們剛剛設定的使用者變數

使用者變數是和使用者端有關係，當我們退出後，這個變數會自動消失，現在我們退出使用者端

exit複製程式碼

現在我們重新登陸使用者端，再次使用 select 條件查詢

發現已經沒有這個 @myId 了。

區域性變數

MySQL 中的區域性變數與 Java 很類似 ，Java 中的區域性變數是 Java 所在的方法或者程式碼塊，而 MySQL 中的區域性變數作用域是所在的儲存過程。MySQL 區域性變數使用 declare 來宣告。

對談變數

伺服器會為每個連線的使用者端維護一個對談變數。可以使用

show session variables;複製程式碼

顯示所有的對談變數。

我們可以手動設定對談變數

set session auto_increment_increment=1;

或者使用

set @@session.auto_increment_increment=2;複製程式碼

然後進行查詢，查詢對談變數使用

或者使用

全域性變數

當服務啟動時，它將所有全域性變數初始化為預設值。其作用域為 server 的整個生命週期。

可以使用

show global variables;複製程式碼

檢視全域性變數

可以使用下面這兩種方式設定全域性變數

set global sql_warnings=ON;        -- global不能省略

/** 或者 **/

set @@global.sql_warnings=OFF;複製程式碼

查詢全域性變數時，可以使用

或者是

MySQL 流程語句介紹

MySQL 支援下面這些控制語句

• IF

IF 用於實現邏輯判斷，滿足不同條件執行不同的 SQL 語句

IF ... THEN ...複製程式碼

• CASE

CASE 實現比 IF 稍微複雜，語法如下

CASE ...
	WHEN ... THEN...
	...
END CASE複製程式碼

CASE 語句也可以使用 IF 來完成

• LOOP

LOOP 用於實現簡單的迴圈

label:LOOP
     ...
END LOOP label;複製程式碼

如果 ... 中不寫 SQL 語句的話，那麼就是一個簡單的死迴圈語句

• LEAVE

用來表示從標註的流程構造中退出，通常和 BEGIN...END 或者回圈一起使用

• ITERATE

ITERATE 語句必須用在迴圈中，作用是跳過當前回圈的剩下的語句，直接進入下一輪迴圈。

• REPEAT

帶有條件的迴圈控制語句，當滿足條件的時候退出迴圈。

REPEAT
   ...
   UNTIL
END REPEAT;複製程式碼

• WHILE

WHILE 語句表示的含義和 REPEAT 相差無幾，WHILE 迴圈和 REPEAT 迴圈的區別在於：WHILE 是滿足條件才執行迴圈，REPEAT 是滿足條件退出迴圈；

觸發器

MySQL 從 5.0 開始支援觸發器，觸發器一般作用在表上，在滿足定義條件時觸發，並執行觸發器中定義的語句集合，下面我們就來一起認識一下觸發器。

舉個例子來認識一下觸發器：比如你有一個紀錄檔表和金額表，你每錄入一筆金額就要進行紀錄檔表的記錄，你會怎麼樣？同時在金額表和紀錄檔表插入資料嗎？如果有了觸發器，你可以直接在金額表錄入資料，紀錄檔表會自動插入一條紀錄檔記錄，當然，觸發器不僅只有新增操作，還有更新和刪除操作。

建立觸發器

我們可以用如下的方式建立觸發器

create trigger triggername triggertime triggerevent on tbname for each row triggerstmt複製程式碼

上面涉及到幾個引數，我知道你有點懵逼，解釋一下。

• triggername：這個指的就是觸發器的名字
• triggertime：這個指的就是觸發器觸發時機，是 BEFORE 還是 AFTER
• triggerevent: 這個指的就是觸發器觸發事件，一共有三種事件：INSERT、UPDATE 或者 DELETE。
• tbname：這個引數指的是觸發器建立的表名，在哪個表上建立
• triggerstmt: 觸發器的程式體，也就是 SQL 語句

所以，可以建立六種觸發器

BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE、BEFORE DELETE、AFTER DELETE

上面的 for each now 表示任何一條記錄上的操作都會觸發觸發器。

下面我們通過一個例子來演示一下觸發器的操作

我們還是用上面的 procuct 表做例子，我們建立一個 product_info 產品資訊表。

create table product_info(p_info varchar(20)); 
複製程式碼

然後我們建立一個 trigger

我們在 product 表中插入一條資料

insert into product values(4,"pineapple",15.3);複製程式碼

我們進行 select 查詢，可以看到現在 product 表中有四條資料

我們沒有向 product_info 表中插入資料，現在我們來看一下 product_info 表中，我們預想到是有資料的，具體來看下

這條資料是什麼時候插入的呢？我們在建立觸發器 tg_pinfo 的時候插入了的這條資料。

刪除觸發器

觸發器可以使用 drop 進行刪除，具體刪除語法如下

drop trigger tg_pinfo;複製程式碼

和刪除表的語法是一樣的

檢視觸發器

我們經常會檢視觸發器，可以通過執行 show triggers 命令檢視觸發器的狀態、語法等資訊。

另一種查詢方式是查詢表中的 information_schema.triggers 表，這個可以查詢指定觸發器的指定資訊，操作起來方便很多

觸發器的作用

• 在新增一條資料前，檢查資料是否合理，例如檢查郵件格式是否正確
• 刪除資料後，相當於資料備份的作用
• 可以記錄資料庫的操作紀錄檔，也可以作為表的執行軌跡

注意：觸發器的使用有兩個限制

1. 觸發程式不能呼叫將資料返回使用者端的儲存程式。也不能使用 CALL 語句的動態 SQL 語句。
2. 不能在觸發器中開始和結束語句，例如 START TRANSACTION

更多相關免費學習推薦：(視訊)

以上就是通過47 張圖帶你 MySQL 進階的詳細內容，更多請關注TW511.COM其它相關文章！