Mysql高手系列 – 第18篇：mysql流程控制語句詳解（高手進階）
- 2019 年 10 月 9 日
- 筆記
Mysql系列的目標是：通過這個系列從入門到全面掌握一個高級開發所需要的全部技能。
這是Mysql系列第18篇。
環境：mysql5.7.25，cmd命令中進行演示。
代碼中被[]包含的表示可選，|符號分開的表示可選其一。
上一篇存儲過程&自定義函數，對存儲過程和自定義函數做了一個簡單的介紹，但是如何能夠寫出複雜的存儲過程和函數呢？
這需要我們熟練掌握流程控制語句才可以，本文主要介紹mysql中流程控制語句的使用，上乾貨。
本篇內容
- if函數
- case語句
- if結構
- while循環
- repeat循環
- loop循環
- 循環體控制語句
準備數據
/*建庫javacode2018*/ drop database if exists javacode2018; create database javacode2018; /*切換到javacode2018庫*/ use javacode2018; /*創建表：t_user*/ DROP TABLE IF EXISTS t_user; CREATE TABLE t_user( id int PRIMARY KEY COMMENT '編號', sex TINYINT not null DEFAULT 1 COMMENT '性別,1:男,2:女', name VARCHAR(16) not NULL DEFAULT '' COMMENT '姓名' )COMMENT '用戶表'; /*插入數據*/ INSERT INTO t_user VALUES (1,1,'路人甲Java'),(2,1,'張學友'),(3,2,'王祖賢'),(4,1,'郭富城'),(5,2,'李嘉欣'); SELECT * FROM t_user; DROP TABLE IF EXISTS test1; CREATE TABLE test1 (a int not null); DROP TABLE IF EXISTS test2; CREATE TABLE test2 (a int not null,b int NOT NULL );if函數
語法
if(條件表達式,值1,值2);if函數有3個參數。
當參數1為true的時候，返回
值1，否則返回值2。
示例
需求：查詢
t_user表數據，返回：編號、性別（男、女）、姓名。分析一下：數據庫中性別用數字表示的，我們需要將其轉換為（男、女），可以使用if函數。
mysql> SELECT id 編號,if(sex=1,'男','女') 性別,name 姓名 FROM t_user; +--------+--------+---------------+ | 編號 | 性別 | 姓名 | +--------+--------+---------------+ | 1 | 男 | 路人甲Java | | 2 | 男 | 張學友 | | 3 | 女 | 王祖賢 | | 4 | 男 | 郭富城 | | 5 | 女 | 李嘉欣 | +--------+--------+---------------+ 5 rows in set (0.00 sec)CASE結構
2種用法。
第1種用法
類似於java中的switch語句。
case 表達式 when 值1 then 結果1或者語句1（如果是語句需要加分號） when 值2 then 結果2或者語句2 ... else 結果n或者語句n end [case] （如果是放在begin end之間需要加case，如果在select後則不需要）示例1：select中使用
查詢
t_user表數據，返回：編號、性別（男、女）、姓名。
/*寫法1：類似於java中的if else*/ SELECT id 編號,(CASE sex WHEN 1 THEN '男' ELSE '女' END) 性別,name 姓名 FROM t_user; /*寫法2：類似於java中的if else if*/ SELECT id 編號,(CASE sex WHEN 1 then '男' WHEN 2 then '女' END) 性別,name 姓名 FROM t_user;示例2：begin end中使用
寫一個存儲過程，接受3個參數：id，性別（男、女），姓名，然後插入到t_user表
創建存儲過程：
/*刪除存儲過程proc1*/ DROP PROCEDURE IF EXISTS proc1; /*s刪除id=6的記錄*/ DELETE FROM t_user WHERE id=6; /*聲明結束符為$*/ DELIMITER $ /*創建存儲過程proc1*/ CREATE PROCEDURE proc1(id int,sex_str varchar(8),name varchar(16)) BEGIN /*聲明變量v_sex用於存放性別*/ DECLARE v_sex TINYINT UNSIGNED; /*根據sex_str的值來設置性別*/ CASE sex_str when '男' THEN SET v_sex = 1; WHEN '女' THEN SET v_sex = 2; END CASE ; /*插入數據*/ INSERT INTO t_user VALUES (id,v_sex,name); END $ /*結束符置為;*/ DELIMITER ;調用存儲過程：
CALL proc1(6,'男','郭富城');查看效果：
mysql> select * from t_user; +----+-----+---------------+ | id | sex | name | +----+-----+---------------+ | 1 | 1 | 路人甲Java | | 2 | 1 | 張學友 | | 3 | 2 | 王祖賢 | | 4 | 1 | 郭富城 | | 5 | 2 | 李嘉欣 | | 6 | 1 | 郭富城 | +----+-----+---------------+ 6 rows in set (0.00 sec)示例3：函數中使用
需求：寫一個函數，根據t_user表sex的值，返回男女
創建函數：
/*刪除存儲過程proc1*/ DROP FUNCTION IF EXISTS fun1; /*聲明結束符為$*/ DELIMITER $ /*創建存儲過程proc1*/ CREATE FUNCTION fun1(sex TINYINT UNSIGNED) RETURNS varchar(8) BEGIN /*聲明變量v_sex用於存放性別*/ DECLARE v_sex VARCHAR(8); CASE sex WHEN 1 THEN SET v_sex:='男'; ELSE SET v_sex:='女'; END CASE; RETURN v_sex; END $ /*結束符置為;*/ DELIMITER ;看一下效果：
mysql> select sex, fun1(sex) 性別,name FROM t_user; +-----+--------+---------------+ | sex | 性別 | name | +-----+--------+---------------+ | 1 | 男 | 路人甲Java | | 1 | 男 | 張學友 | | 2 | 女 | 王祖賢 | | 1 | 男 | 郭富城 | | 2 | 女 | 李嘉欣 | | 1 | 男 | 郭富城 | +-----+--------+---------------+ 6 rows in set (0.00 sec)第2種用法
類似於java中多重if語句。
case when 條件1 then 結果1或者語句1（如果是語句需要加分號） when 條件2 then 結果2或者語句2 ... else 結果n或者語句n end [case] （如果是放在begin end之間需要加case，如果是在select後面case可以省略）這種寫法和1中的類似，大家用上面這種語法實現第1中用法中的3個示例，貼在留言中。
if結構
if結構類似於java中的 if..else if…else的語法，如下：
if 條件語句1 then 語句1; elseif 條件語句2 then 語句2; ... else 語句n; end if;只能使用在begin end之間。
示例
寫一個存儲過程，實現用戶數據的插入和新增，如果id存在，則修改，不存在則新增，並返回結果
/*刪除id=7的記錄*/ DELETE FROM t_user WHERE id=7; /*刪除存儲過程*/ DROP PROCEDURE IF EXISTS proc2; /*聲明結束符為$*/ DELIMITER $ /*創建存儲過程*/ CREATE PROCEDURE proc2(v_id int,v_sex varchar(8),v_name varchar(16),OUT result TINYINT) BEGIN DECLARE v_count TINYINT DEFAULT 0;/*用來保存user記錄的數量*/ /*根據v_id查詢數據放入v_count中*/ select count(id) into v_count from t_user where id = v_id; /*v_count>0表示數據存在，則修改，否則新增*/ if v_count>0 THEN BEGIN DECLARE lsex TINYINT; select if(lsex='男',1,2) into lsex; update t_user set sex = lsex,name = v_name where id = v_id; /*獲取update影響行數*/ select ROW_COUNT() INTO result; END; else BEGIN DECLARE lsex TINYINT; select if(lsex='男',1,2) into lsex; insert into t_user VALUES (v_id,lsex,v_name); select 0 into result; END; END IF; END $ /*結束符置為;*/ DELIMITER ;看效果：
mysql> SELECT * FROM t_user; +----+-----+---------------+ | id | sex | name | +----+-----+---------------+ | 1 | 1 | 路人甲Java | | 2 | 1 | 張學友 | | 3 | 2 | 王祖賢 | | 4 | 1 | 郭富城 | | 5 | 2 | 李嘉欣 | | 6 | 1 | 郭富城 | +----+-----+---------------+ 6 rows in set (0.00 sec) mysql> CALL proc2(7,'男','黎明',@result); Query OK, 1 row affected (0.00 sec) mysql> SELECT @result; +---------+ | @result | +---------+ | 0 | +---------+ 1 row in set (0.00 sec) mysql> SELECT * FROM t_user; +----+-----+---------------+ | id | sex | name | +----+-----+---------------+ | 1 | 1 | 路人甲Java | | 2 | 1 | 張學友 | | 3 | 2 | 王祖賢 | | 4 | 1 | 郭富城 | | 5 | 2 | 李嘉欣 | | 6 | 1 | 郭富城 | | 7 | 2 | 黎明 | +----+-----+---------------+ 7 rows in set (0.00 sec) mysql> CALL proc2(7,'男','梁朝偉',@result); Query OK, 1 row affected (0.00 sec) mysql> SELECT @result; +---------+ | @result | +---------+ | 1 | +---------+ 1 row in set (0.00 sec) mysql> SELECT * FROM t_user; +----+-----+---------------+ | id | sex | name | +----+-----+---------------+ | 1 | 1 | 路人甲Java | | 2 | 1 | 張學友 | | 3 | 2 | 王祖賢 | | 4 | 1 | 郭富城 | | 5 | 2 | 李嘉欣 | | 6 | 1 | 郭富城 | | 7 | 2 | 梁朝偉 | +----+-----+---------------+ 7 rows in set (0.00 sec)循環
mysql中循環有3種寫法
- while：類似於java中的while循環
- repeat：類似於java中的do while循環
- loop：類似於java中的while(true)死循環，需要在內部進行控制。
循環控制
對循環內部的流程進行控制，如：
結束本次循環
類似於java中的
continue
iterate 循環標籤;退出循環
類似於java中的
break
leave 循環標籤;下面我們分別介紹3種循環的使用。
while循環
類似於java中的while循環。
語法
[標籤:]while 循環條件 do 循環體 end while [標籤];標籤：是給while取個名字，標籤和
iterate、leave結合用於在循環內部對循環進行控制：如：跳出循環、結束本次循環。注意：這個循環先判斷條件，條件成立之後，才會執行循環體，每次執行都會先進行判斷。
示例1：無循環控制語句
根據傳入的參數v_count向test1表插入指定數量的數據。
/*刪除test1表記錄*/ DELETE FROM test1; /*刪除存儲過程*/ DROP PROCEDURE IF EXISTS proc3; /*聲明結束符為$*/ DELIMITER $ /*創建存儲過程*/ CREATE PROCEDURE proc3(v_count int) BEGIN DECLARE i int DEFAULT 1; a:WHILE i<=v_count DO INSERT into test1 values (i); SET i=i+1; END WHILE; END $ /*結束符置為;*/ DELIMITER ;見效果：
mysql> CALL proc3(5); Query OK, 1 row affected (0.01 sec) mysql> SELECT * from test1; +---+ | a | +---+ | 1 | | 2 | | 3 | | 4 | | 5 | +---+ 5 rows in set (0.00 sec)示例2：添加leave控制語句
根據傳入的參數v_count向test1表插入指定數量的數據，當插入超過10條，結束。
/*刪除存儲過程*/ DROP PROCEDURE IF EXISTS proc4; /*聲明結束符為$*/ DELIMITER $ /*創建存儲過程*/ CREATE PROCEDURE proc4(v_count int) BEGIN DECLARE i int DEFAULT 1; a:WHILE i<=v_count DO INSERT into test1 values (i); /*判斷i=10，離開循環a*/ IF i=10 THEN LEAVE a; END IF; SET i=i+1; END WHILE; END $ /*結束符置為;*/ DELIMITER ;見效果：
mysql> DELETE FROM test1; Query OK, 20 rows affected (0.00 sec) mysql> CALL proc4(20); Query OK, 1 row affected (0.02 sec) mysql> SELECT * from test1; +----+ | a | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | +----+ 10 rows in set (0.00 sec)示例3：添加iterate控制語句
根據傳入的參數v_count向test1表插入指定數量的數據，只插入偶數數據。
/*刪除test1表記錄*/ DELETE FROM test1; /*刪除存儲過程*/ DROP PROCEDURE IF EXISTS proc5; /*聲明結束符為$*/ DELIMITER $ /*創建存儲過程*/ CREATE PROCEDURE proc5(v_count int) BEGIN DECLARE i int DEFAULT 0; a:WHILE i<=v_count DO SET i=i+1; /*如果i不為偶數，跳過本次循環*/ IF i%2!=0 THEN ITERATE a; END IF; /*插入數據*/ INSERT into test1 values (i); END WHILE; END $ /*結束符置為;*/ DELIMITER ;見效果：
mysql> DELETE FROM test1; Query OK, 5 rows affected (0.00 sec) mysql> CALL proc5(10); Query OK, 1 row affected (0.01 sec) mysql> SELECT * from test1; +----+ | a | +----+ | 2 | | 4 | | 6 | | 8 | | 10 | +----+ 5 rows in set (0.00 sec)示例4：嵌套循環
test2表有2個字段（a,b），寫一個存儲過程（2個參數：v_a_count，v_b_count)，使用雙重循環插入數據，數據條件：a的範圍[1,v_a_count]、b的範圍[1,v_b_count]所有偶數的組合。
/*刪除存儲過程*/ DROP PROCEDURE IF EXISTS proc8; /*聲明結束符為$*/ DELIMITER $ /*創建存儲過程*/ CREATE PROCEDURE proc8(v_a_count int,v_b_count int) BEGIN DECLARE v_a int DEFAULT 0; DECLARE v_b int DEFAULT 0; a:WHILE v_a<=v_a_count DO SET v_a=v_a+1; SET v_b=0; b:WHILE v_b<=v_b_count DO SET v_b=v_b+1; IF v_a%2!=0 THEN ITERATE a; END IF; IF v_b%2!=0 THEN ITERATE b; END IF; INSERT INTO test2 VALUES (v_a,v_b); END WHILE b; END WHILE a; END $ /*結束符置為;*/ DELIMITER ;代碼中故意將
ITERATE a;放在內層循環中，主要讓大家看一下效果。
見效果：
mysql> DELETE FROM test2; Query OK, 6 rows affected (0.00 sec) mysql> CALL proc8(4,6); Query OK, 1 row affected (0.01 sec) mysql> SELECT * from test2; +---+---+ | a | b | +---+---+ | 2 | 2 | | 2 | 4 | | 2 | 6 | | 4 | 2 | | 4 | 4 | | 4 | 6 | +---+---+ 6 rows in set (0.00 sec)repeat循環
語法
[標籤:]repeat 循環體; until 結束循環的條件 end repeat [標籤];repeat循環類似於java中的do…while循環，不管如何，循環都會先執行一次，然後再判斷結束循環的條件，不滿足結束條件，循環體繼續執行。這塊和while不同，while是先判斷條件是否成立再執行循環體。
示例1：無循環控制語句
根據傳入的參數v_count向test1表插入指定數量的數據。
/*刪除存儲過程*/ DROP PROCEDURE IF EXISTS proc6; /*聲明結束符為$*/ DELIMITER $ /*創建存儲過程*/ CREATE PROCEDURE proc6(v_count int) BEGIN DECLARE i int DEFAULT 1; a:REPEAT INSERT into test1 values (i); SET i=i+1; UNTIL i>v_count END REPEAT; END $ /*結束符置為;*/ DELIMITER ;見效果：
mysql> DELETE FROM test1; Query OK, 1 row affected (0.00 sec) mysql> CALL proc6(5); Query OK, 1 row affected (0.01 sec) mysql> SELECT * from test1; +---+ | a | +---+ | 1 | | 2 | | 3 | | 4 | | 5 | +---+ 5 rows in set (0.00 sec)repeat中iterate和leave用法和while中類似，這塊的示例算是給大家留的作業，寫好的發在留言區，謝謝。
loop循環
語法
[標籤:]loop 循環體; end loop [標籤];loop相當於一個死循環，需要在循環體中使用
iterate或者leave來控制循環的執行。
示例1：無循環控制語句
根據傳入的參數v_count向test1表插入指定數量的數據。
/*刪除存儲過程*/ DROP PROCEDURE IF EXISTS proc7; /*聲明結束符為$*/ DELIMITER $ /*創建存儲過程*/ CREATE PROCEDURE proc7(v_count int) BEGIN DECLARE i int DEFAULT 0; a:LOOP SET i=i+1; /*當i>v_count的時候退出循環*/ IF i>v_count THEN LEAVE a; END IF; INSERT into test1 values (i); END LOOP a; END $ /*結束符置為;*/ DELIMITER ;見效果：
mysql> DELETE FROM test1; Query OK, 5 rows affected (0.00 sec) mysql> CALL proc7(5); Query OK, 1 row affected (0.01 sec) mysql> SELECT * from test1; +---+ | a | +---+ | 1 | | 2 | | 3 | | 4 | | 5 | +---+ 5 rows in set (0.00 sec)loop中iterate和leave用法和while中類似，這塊的示例算是給大家留的作業，寫好的發在留言區，謝謝。
總結
- 本文主要介紹了mysql中控制流語句的使用，請大家下去了多練習，熟練掌握
- if函數常用在select中
- case語句有2種寫法，主要用在select、begin end中，select中end後面可以省略case，begin end中使用不能省略case
- if語句用在begin end中
- 3種循環體的使用，while類似於java中的while循環，repeat類似於java中的do while循環，loop類似於java中的死循環，都用於begin end中
- 循環中體中的控制依靠
leave和iterate，leave類似於java中的break可以退出循環，iterate類似於java中的continue可以結束本次循環
Mysql系列目錄
- 第1篇：mysql基礎知識
- 第2篇：詳解mysql數據類型（重點）
- 第3篇：管理員必備技能(必須掌握)
- 第4篇：DDL常見操作
- 第5篇：DML操作匯總（insert,update,delete）
- 第6篇：select查詢基礎篇
- 第7篇：玩轉select條件查詢，避免采坑
- 第8篇：詳解排序和分頁(order by & limit)
- 第9篇：分組查詢詳解（group by & having）
- 第10篇：常用的幾十個函數詳解
- 第11篇：深入了解連接查詢及原理
- 第12篇：子查詢
- 第13篇：細說NULL導致的神坑，讓人防不勝防
- 第14篇：詳解事務
- 第15篇：詳解視圖
- 第16篇：變量詳解
- 第17篇：存儲過程&自定義函數詳解
- 第18篇：流程控制語句
- 第19篇：游標詳解
- 第20篇：異常捕獲及處理詳解
- 第21篇：什麼是索引？
mysql系列大概有20多篇，喜歡的請關注一下，歡迎大家加我微信itsoku或者留言交流mysql相關技術!
