MYSQL中IF, IFNULL, CASE WHEN的使用

CASE WHEN

两种写法 

1.

SELECT 
CASE WHEN status=1 THEN 'status1'  -- 当status=1时，返回值为状态1
WHEN status=2 THEN 'status2' -- 当status=1时，返回值为状态1
ELSE 'other'
END 

FROM table

2.

SELECT 
CASE                  
status           
WHEN '1' THEN '状态1'  -- 当status=1时，返回值为状态1
WHEN '2' THEN '状态2'  -- 当status=2时，返回值为状态2
ELSE status            -- 否则返回值为status对应的值
END AS status 
FROM table 

几个需要注意的问题：

1.else 必须有，不写else，返回值为null，以前的数据就没了。

2.case函数只返回第一个符合条件的值，剩下的case部分将会被自动忽略。

比如

CASE WHEN status='1' THEN '开启'
WHEN status='1' THEN '关闭'

返回值为“开启”

3.可以 ORDER BY 但是不能做WHERE中的判断语句

CASE WHEN status=1 THEN 'status1'
WHEN status=2 THEN 'status2'
ELSE status
END AS caseStatus
ORDER BY caseStatus 正确
WHERE caseStatus=1  错误

4. 注意判断null的写法

SELECT CASE status WHEN '1' THEN 'open'
WHEN NULL THEN 'close' 

这种为错误写法，完整就是status=null, 判断是否为空用IS NULL

5.根据条件有选择的UPDATE。参考点击打开链接

例，有如下更新条件 

工资5000以上的职员，工资减少10% 
工资在2000到4600之间的职员，工资增加15% 
很容易考虑的是选择执行两次UPDATE语句，如下所示 

--条件1 
UPDATE Personnel 
SET salary = salary * 0.9 
WHERE salary >= 5000; 
--条件2 
UPDATE Personnel 
SET salary = salary * 1.15 
WHERE salary >= 2000 AND salary < 4600; 

但是事情没有想象得那么简单，假设有个人工资5000块。首先，按照条件1，工资减少10%，变成工资4500。接下来运行第二个SQL时候，因为这个人的工资是4500在2000到4600的范围之内， 需增加15%，最后这个人的工资结果是5175,不但没有减少，反而增加了。如果要是反过来执行，那么工资4600的人相反会变成减少工资。暂且不管这个规章是多么荒诞，如果想要一个SQL 语句实现这个功能的话，我们需要用到Case函数。代码如下: 

UPDATE Personnel 
SET salary = CASE WHEN salary >= 5000 
　            THEN salary * 0.9 
WHEN salary >= 2000 AND salary < 4600 
THEN salary * 1.15 
ELSE salary END; 

这里要注意一点，最后一行的ELSE salary是必需的，要是没有这行，不符合这两个条件的人的工资将会被写成NUll,那可就大事不妙了。在Case函数中Else部分的默认值是NULL，这点是需要注意的地方。 
这种方法还可以在很多地方使用，比如说变更主键这种累活。 

IFNULL(expr1,expr2)

如果expr1不为null，则返回值为expr1，否则expr2。返回值是数字还是字符串，具体情况取决于其使用的语境。

IF(expr1,expr2,expr3)

如果expr1为true，则结果为expr2，否则结果为expr3