写在前面

上一篇文章主要对存储过程和自定义函数进行了学习，可以发现这些逻辑都是较为简单的，但是在实际工作中的业务逻辑远比这复杂，因此需要使用到流程控制语句，那么本篇就来学习MySQL中流程控制语句相关内容。

本文的主要内容如下：（1）if函数；（2）if函数示例；（3）case结构；（4）case结构—类似于Java中的switch语句；（5）case结构—类似于Java中的多重if语句；（6）if结构；（7）if结构示例；（8）循环控制；（9）while循环；（10）repeat循环；（11）loop循环；（12）文章总结。

数据准备

为了后续学习的必要，这里先准备了一张数据表t_user，相应的建表语句如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
use envythink;

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,'envy'),(2,1,'book'),(3,2,'movie'),(4,1,'lichee'),(5,2,'bike');

select * from t_user;

当然可能后续还会使用到test1和test2表，因此这里也提前创建了：

1
2
3
4
5
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
if(条件表达式,值1,值2);

可以看到if函数有三个参数，当条件表达式结果为true的时候返回值1，否则返回值2。

if函数示例

现在有一个需求，查询t_user数据表中的数据，要求显示编号、性别（男或者女）、姓名。如果你对上述数据有印象的话，可以知道在数据库中性别是用数字来表示的，因此我们需要将其转换为男或者女，可以使用if函数来解决这个问题：

1
2
3
4
5
6
7
8
9
10
11
mysql> select id as 编号,if(sex=1,'男','女')as 性别,name as 姓名 from t_user;
+------+------+--------+
| 编号 | 性别 | 姓名   |
+------+------+--------+
|    1 | 男   | envy   |
|    2 | 男   | book   |
|    3 | 女   | movie  |
|    4 | 男   | lichee |
|    5 | 女   | bike   |
+------+------+--------+
5 rows in set (0.12 sec)

case结构

MySQL中的case结构有两种用法，第一种类似于Java中的switch语句；第二种类似于Java中的多重if语句。

类似于Java中的switch语句

此种用法下的case语句的语法格式如下所示：

1
2
3
4
5
6
7
case 表达式
when 值1 then 结果1或者语句1(注意如果是语句则需要添加分号)
when 值2 then 结果2或者语句2
when 值3 then 结果3或者语句3
...
else 结果n或者语句n
end [case](如果是放在begin end之间需要加case，如果在select后则不需要)
在select中使用

同样是前面那个需求：查询t_user数据表中的数据，要求显示编号、性别（男或者女）、姓名。

这里有两种写法，第一种类似于Java中的if else用法：

1
2
select id as 编号,
(case sex when 1 then '男' else '女' end) as 性别,name as 姓名 from t_user;

执行结果如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select id as 编号,
    -> (case sex when 1 then '男' else '女' end) as 性别,name as 姓名 from t_user;
+------+------+--------+
| 编号 | 性别 | 姓名   |
+------+------+--------+
|    1 | 男   | envy   |
|    2 | 男   | book   |
|    3 | 女   | movie  |
|    4 | 男   | lichee |
|    5 | 女   | bike   |
+------+------+--------+
5 rows in set (0.00 sec)

第二种类似于Java中的if else if用法：

1
2
select id as 编号,
(case sex when 1 then '男' when 2 then '女' end) as 性别,name as 姓名 from t_user;

执行结果如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select id as 编号,
    -> (case sex when 1 then '男' when 2 then '女' end) as 性别,name as 姓名 from t_user;
+------+------+--------+
| 编号 | 性别 | 姓名   |
+------+------+--------+
|    1 | 男   | envy   |
|    2 | 男   | book   |
|    3 | 女   | movie  |
|    4 | 男   | lichee |
|    5 | 女   | bike   |
+------+------+--------+
5 rows in set (0.00 sec)
在begin end中使用

既然是在begin end中使用，那么就需要写一个存储过程，让它接收三个参数：id、性别(男或者女)、姓名，然后插入到t_user表中。

第一步，创建存储过程：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/*删除存储过程proc1*/
drop procedure if exists proc1;
/*如果存在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 ;

第二步，调用存储过程：

1
call proc1(6,'男','nice');

第三步，查询t_user表来验证效果：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select * from t_user;
+----+-----+--------+
| id | sex | name   |
+----+-----+--------+
|  1 |   1 | envy   |
|  2 |   1 | book   |
|  3 |   2 | movie  |
|  4 |   1 | lichee |
|  5 |   2 | bike   |
|  6 |   1 | nice   |
+----+-----+--------+
6 rows in set (0.00 sec)
在函数中使用

下面有一个需求：写一个函数来根据t_user表中sex的值来返回用户性别（男和女）。

第一步，创建函数：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
/*删除函数fun1*/
drop function if exists fun1;
/*声明结束符为$*/
delimiter $
/*创建函数fun1*/
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 ;

第二步，调用fun1函数并查询t_user表的结果：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select sex,fun1(sex) as 性别,name from t_user;
+-----+------+--------+
| sex | 性别 | name   |
+-----+------+--------+
|   1 | 男   | envy   |
|   1 | 男   | book   |
|   2 | 女   | movie  |
|   1 | 男   | lichee |
|   2 | 女   | bike   |
|   1 | 男   | nice   |
+-----+------+--------+
6 rows in set (0.00 sec)

类似于Java中的多重if语句

此种用法下的case语句的语法格式如下所示：

1
2
3
4
5
6
7
case
when 条件1 then 结果1或者语句1（注意如果是语句需要加分号）
when 条件2 then 结果2或者语句2
when 条件3 then 结果3或者语句3
...
else 结果n或者语句n
end [case] （如果是放在begin end之间需要加case，如果是在select后面case可以省略）

其实这种写法和1中的写法非常相似，因此这里就忽略。

if结构

if结构非常类似于Java中的if…else if…else的语法，使用的语法格式如下所示：

1
2
3
4
5
if 条件语句1 then 语句1;
elseif 条件语句2 then 语句2;
...
else 语句n;
end if;

请注意，if结构只能使用在begin end之间。

if结构示例

现在这里有一个需求，写一个存储过程来实现用户数据的插入和新增，如果id存在则修改，否则新增，并返回结果。

第一步，创建存储过程，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
/*删除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
    /*用来保存user记录的数量*/
    declare v_count tinyint default 0;
    /*根据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 ;

第二步，执行上述语句，并查询t_user表来验证效果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
mysql> select * from t_user;
+----+-----+--------+
| id | sex | name   |
+----+-----+--------+
|  1 |   1 | envy   |
|  2 |   1 | book   |
|  3 |   2 | movie  |
|  4 |   1 | lichee |
|  5 |   2 | bike   |
|  6 |   1 | nice   |
+----+-----+--------+
6 rows in set (0.00 sec)

mysql> call proc2(7,'男','you',@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 | envy   |
|  2 |   1 | book   |
|  3 |   2 | movie  |
|  4 |   1 | lichee |
|  5 |   2 | bike   |
|  6 |   1 | nice   |
|  7 |   1 | you    |
+----+-----+--------+
7 rows in set (0.00 sec)

mysql> call proc2(7,'男','hello',@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 | envy   |
|  2 |   1 | book   |
|  3 |   2 | movie  |
|  4 |   1 | lichee |
|  5 |   2 | bike   |
|  6 |   1 | nice   |
|  7 |   1 | hello    |
+----+-----+--------+
7 rows in set (0.00 sec)

循环

MySQL中的循环有三种写法：while、repeat和loop。其中while类似于Java中的while循环；repeat类似于Java中的do while循环；loop类似于Java中的while(true)死循环，需要在内部进行控制。

循环控制

MySQL中对于循环的控制有两种，第一种是结束本次循环，类似于Java中的continue关键字的作用，其对应的语法格式如下：

1
iterate 循环标签;

另一种则是退出循环，类似于Java中的break关键字的作用，其对应的语法格式如下：

1
leave 循环标签;

在清楚这两个循环控制语句之后，接下来开始学习MySQL中的三种循环。

while循环

前面说过MySQL中的while循环是类似于Java中的while循环，其对应的语法格式如下所示：

1
2
3
[标签:]while 循环条件 do
循环体
end while [标签];

上面语法格式中的“标签”是先给while取一个名字，“标签”和iterate、leave结合，用于在循环内部对循环进行控制，如跳出循环和结束本次循环等。

请注意，这个循环会先判断条件，条件成立之后才会执行循环体，也就是说每次执行前都会进行条件的判断。

while无循环控制语句示例

接下来有一个需求，根据传入的参数v_count来向test1表中插入指定数量的数据。

第一步，创建存储过程，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/*删除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 ;

第二步，调用存储过程并查询test1表的记录：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> call proc3(5);
Query OK, 1 row affected (0.39 sec)

mysql> select * from test1;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---+
5 rows in set (0.00 sec)
添加leave控制语句示例

接下来又有一个需求，根据传入的参数v_count来向test1表中插入指定数量的数据，当插入条数超过10条时，就停止插入。

第一步，创建存储过程，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*删除test1表记录*/
delete from test1;
/*删除存储过程*/
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 ;

第二步，调用存储过程并查询test1表的记录：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> call proc4(30);
Query OK, 1 row affected (0.48 sec)

mysql> select * from test1;
+----+
| a  |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+
10 rows in set (0.00 sec)
添加iterate控制语句示例

接下来又有一个需求，根据传入的参数v_count来向test1表中插入指定数量的数据，注意只插入偶数数据。

第一步，创建存储过程，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/*删除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 ;

第二步，调用存储过程并查询test1表的记录：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> call proc5(10);
Query OK, 1 row affected (0.35 sec)

mysql> select * from test1;
+----+
| a  |
+----+
|  2 |
|  4 |
|  6 |
|  8 |
| 10 |
+----+
5 rows in set (0.00 sec)
嵌套循环示例

接下来又有一个需求，在之前创建的test2表中有两个字段a和b，要求写一个存储过程，里面包含两个参数v_a_count和v_b_count，使用双重循环来插入数据，但是插入的数据是有范围的，其中a的取值范围为[1,v_a_count]、b的取值范围为[1,v_b_count]内的所有偶数的组合。

第一步，创建存储过程，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
/*删除test2表记录*/
delete from test2;
/*删除存储过程*/
drop procedure if exists proc7;
/*声明结束符为$*/
delimiter $
/*创建存储过程*/
create procedure proc7(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的代码放在了内存循环中。

第二步，调用存储过程并查询test2表的记录：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> call proc7(4,6);
Query OK, 1 row affected (0.37 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循环对应的语法格式如下所示：

1
2
3
[标签:] repeat
循环体;
until 结束循环的条件 end repeat [标签];

前面也说了repeat循环类似于Java中的do…while循环，也就是说无论怎样，循环都会先执行一次，然后再判断循环结束的条件，如果循环不满足结束条件，那么循环将继续执行；否则就结束循环。可以很明显感受到它和while的区别，while是先判断条件是否成立，然后再执行循环体内的逻辑。

repeat无循环控制语句示例

同样还是以之前的需求为例进行介绍和学习：根据传入的参数v_count来向test1表中插入指定数量的数据。

第一步，创建存储过程，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/*删除test1表记录*/
delete from 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 ;

第二步，调用存储过程并查询test1表的记录：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> call proc6(5);
Query OK, 1 row affected (0.30 sec)

mysql> select * from test1;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---+
5 rows in set (0.00 sec)

repeat中添加leave和iterate控制语句的用法和while中的类似，因此这里就不赘述了。

loop循环

loop循环对应的语法格式如下所示：

1
2
3
[标签:] loop
循环体;
end loop [标签];

请注意loop相当于一个死循环，需要在循环体中使用iterate或者leave来控制循环的执行。

loop无循环控制语句示例

同样还是以之前的需求为例进行介绍和学习：根据传入的参数v_count来向test1表中插入指定数量的数据。

第一步，创建存储过程，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*删除test1表记录*/
delete from test1;
/*删除存储过程*/
drop procedure if exists proc8;
/*声明结束符为$*/
delimiter $
/*创建存储过程*/
create procedure proc8(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 ;

第二步，调用存储过程并查询test1表的记录：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> call proc8(5);
Query OK, 1 row affected (0.27 sec)

mysql> select * from test1;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---+
5 rows in set (0.00 sec)

loop中添加leave和iterate控制语句的用法和前面所介绍的while和repeat中的类似，因此这里就不赘述了。

文章总结

本篇主要学习了MySQL中控制流语句，其中if函数常用在select中；case结构有2种写法，主要用在select、begin…end中，select中end后面可以省略case，但是begin…end中使用不能省略case。if结构只能用在begin end之间。还学习了while、repeat和loop三种循环的使用，其中while类似于java中的while循环；repeat类似于java中的do…while循环；loop类似于java中的死循环，但是它们都用于begin…end中。当然循环体的控制还需要leave和iterate关键字，其中leave类似于java中的break，通常用于退出循环体；而iterate类似于java中的continue，通常用于结束本次循环，也就是跳出本次循环，继续下次循环。