1.IF(expr, v1, v2)函数
如果expr成立,返回值为v1,否则返回v2
mysql> select if(1>2,2,3),
-> if(1<2,'yes','no'),
-> if(strcmp('test','test1'),'no','yes');
+-------------+--------------------+---------------------------------------+
| if(1>2,2,3) | if(1<2,'yes','no') | if(strcmp('test','test1'),'no','yes') |
+-------------+--------------------+---------------------------------------+
| 3 | yes | no |
+-------------+--------------------+---------------------------------------+
1 row in set (0.00 sec)
2.IFNULL(v1, v2)函数
IFNULL(v1,v2)假如v1不为NULL,则IFNULL()的返回值为v1;否则其返回值为v2。IFNULL()的返回值是数字或是字符串,具体情况取决于其所在的语境。
mysql> select ifnull(1,2),ifnull(NULL,10),ifnull(1/0,'wrong');
+-------------+-----------------+---------------------+
| ifnull(1,2) | ifnull(NULL,10) | ifnull(1/0,'wrong') |
+-------------+-----------------+---------------------+
| 1 | 10 | wrong |
+-------------+-----------------+---------------------+
1 row in set (0.00 sec)
3.CASE函数
CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END
该函数表示,如果expr值等于某个vn,则返回对应位置THEN后面的结果。如果与所有值都不相等,则返回ELSE后面的rn。
mysql> select case 2 when 1 then 'one' when 2 then 'two' else 'more' end;
+------------------------------------------------------------+
| case 2 when 1 then 'one' when 2 then 'two' else 'more' end |
+------------------------------------------------------------+
| two |
+------------------------------------------------------------+
1 row in set (0.00 sec)