透過47 張圖帶你 MySQL 進階

MySQL教學欄位透過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 一般儲存定長的字串，它屬於固定長度的字元類型，例如下面

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

值	char(5)	儲存位元組
''	'     '	5個位元組
'cx'

這裡注意一點：如果 MySQL 使用了非

嚴格模式

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

如果使用了varchar 字元類型，我們來看看範例

''#'cx''cxuan'##' cxuan'6個位元組'cxuan007''cxuan'6個位元組

可以看到，如果使用 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 編碼，它容納了幾乎所有的字元編碼。以下是一些常見的字元編碼

#varchar(5)	儲存位元組
''	1個位元組
'cx '	3個位元組

##ISO-8859-1是單字節8 位元編碼GBK是雙位元組編碼UTF-8否1 - 4 位元組編碼UTF-16否2 位元組或4 位元組編碼UTF-32是4 位元組編碼#

对数据库来说，字符集是很重要的，因为数据库存储的数据大多数都是各种文字，字符集对数据库的存储、性能、系统的移植来说都非常重要。

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

更多相关免费学习推荐：mysql教程(视频)

字元集	是否定長	編碼方式
#ASCII	是	單字節7 位元編碼

以上是透過47 張圖帶你 MySQL 進階的詳細內容。更多資訊請關注PHP中文網其他相關文章！