MySQL CASE和IF语句使用 – 运维那点事

SQL语句中的case语句与高级语言中的switch语句，是标准SQL的语法，适用于一个条件判断有多种值的情况下分别执行不同的操作。

首先，让我们看一下CASE的语法。在一般的SELECT中，其语法格式如下：

CASE  <单值表达式>
      WHEN <表达式值> THEN <SQL语句或者返回值>
      WHEN <表达式值> THEN <SQL语句或者返回值>
      ...
      WHEN <表达式值> THEN <SQL语句或者返回值>
      ELSE <SQL语句或者返回值>
END

CASE <单值表达式>

WHEN <表达式值> THEN <SQL语句或者返回值>

...

WHEN <表达式值> THEN <SQL语句或者返回值>

ELSE <SQL语句或者返回值>

END

创建一个用户表

CREATE TABLE `user` (
id int(10) unsigned NOT NULL auto_increment primary key, 
sex tinyint(1) COMMENT '性别：0女；1男；2保密'
) ENGINE=InnoDB;

insert into user(sex) select(0);
insert into user(sex) select(1);
insert into user(sex) select(2);
insert into user(sex) select(1);
insert into user(sex) select(0);

CREATE TABLE `user` (

id int(10) unsigned NOT NULL auto_increment primary key,

sex tinyint(1) COMMENT '性别：0女；1男；2保密'

) ENGINE=InnoDB;

insert into user(sex) select(0);

insert into user(sex) select(1);

insert into user(sex) select(2);

insert into user(sex) select(1);

insert into user(sex) select(0);

简单Case函数写法

mysql> select sex,(CASE sex WHEN '1' THEN '男' WHEN '0' THEN '女' ELSE '保密' END) as sex_text from user;
+------+----------+
| sex  | sex_text |
+------+----------+
|    0 | 女       |
|    1 | 男       |
|    2 | 保密     |
|    1 | 男       |
|    0 | 女       |
+------+----------+
5 rows in set (0.00 sec)

mysql> select sex,(CASE sex WHEN '1' THEN '男' WHEN '0' THEN '女' ELSE '保密' END) as sex_text from user;

+------+----------+

| sex | sex_text |

+------+----------+

| 0 | 女 |

| 1 | 男 |

| 2 | 保密 |

| 1 | 男 |

| 0 | 女 |

+------+----------+

5 rows in set (0.00 sec)

Case搜索函数写法(注意sex的位置【推荐】)

mysql> select sex,(CASE WHEN sex=1 THEN '男' WHEN sex=0 THEN '女' ELSE '保密' END) as sex_text from user;
+------+----------+
| sex  | sex_text |
+------+----------+
|    0 | 女       |
|    1 | 男       |
|    2 | 保密     |
|    1 | 男       |
|    0 | 女       |
+------+----------+
5 rows in set (0.00 sec)

mysql> select sex,(CASE WHEN sex=1 THEN '男' WHEN sex=0 THEN '女' ELSE '保密' END) as sex_text from user;

+------+----------+

| sex | sex_text |

+------+----------+

| 0 | 女 |

| 1 | 男 |

| 2 | 保密 |

| 1 | 男 |

| 0 | 女 |

+------+----------+

5 rows in set (0.00 sec)

总结：简单Case函数写法只适合相等条件判断，不能用于大于、小于及不等于的判断，Case搜索函数写法适合复杂条件判断：可用于大于、小于及不等于的判断。临时生成的字段是可以排序的，或做其他处理。

IF语句的用法

IF(expr1,expr2,expr3)，如果expr1是TRUE，则返回expr2，否则返回expr3。IF不像CASE那样可以多条件判断，IF只能判断“真”、“假”。

mysql> select sex,IF(sex='1','男','未知') as sex_text from user;
+------+----------+
| sex  | sex_text |
+------+----------+
|    0 | 未知     |
|    1 | 男       |
|    2 | 未知     |
|    1 | 男       |
|    0 | 未知     |
+------+----------+
5 rows in set (0.01 sec)

mysql> select sex,IF(sex='1','男','未知') as sex_text from user;

+------+----------+

| sex | sex_text |

+------+----------+

| 0 | 未知 |

| 1 | 男 |

| 2 | 未知 |

| 1 | 男 |

| 0 | 未知 |

+------+----------+

5 rows in set (0.01 sec)

除了查询使用CASE/IF外，可能你还会碰到下面这种语句，在UPDATE中使用CASE更新数据。如下操作，注意看更新后的结果，把符合条件的值做对应更新，而把没有符合条件的记录全部重置为默认值，所以如果真有这种需求时一定要多做测试。

mysql> update user set sex=(case when id>2 and id<4 then 0 when id>=4 then 2 end);
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from user;
+----+------+
| id | sex  |
+----+------+
|  1 | NULL |
|  2 | NULL |
|  3 |    0 |
|  4 |    2 |
|  5 |    2 |
+----+------+
5 rows in set (0.00 sec)