mysql函数之——控制流函数

mysql函数之——控制流函数  2006-07-14 14:37:12

分类： Mysql/postgreSQL

6.3.1.4 控制流函数

IFNULL(expr1,expr2)

如果  expr1 为非  NULL 的， IFNULL() 返回  expr1，否则返回  expr2。 IFNULL() 返回一个数字或字符串值，这取决于它被使用的语境：

mysql> SELECT IFNULL(1,0);
        -> 1
mysql> SELECT IFNULL(NULL,10);
        -> 10
mysql> SELECT IFNULL(1/0,10);
        -> 10
mysql> SELECT IFNULL(1/0,'yes');
        -> 'yes'

在 MySQL 4.0.6 和更新版本中， IFNULL(expr1,expr2) 的默认返回值以  STRING、 REAL 或  INTEGER 顺序更加'general'了两个表达式。当你基于一个表达式创建一个表或在一个临时表中 MySQL 不得不存储一个从  IFNULL() 返回的值时，这个与较早 MySQL 版本的不同将更加值得注意。

CREATE TABLE foo SELECT IFNULL(1,"test") as test;

在 MySQL 4.0.6 中，列 'test' 的类型为  CHAR(4)，然而在较早的版本中，你得到的却是  BIGINT。

NULLIF(expr1,expr2)

如果  expr1 = expr2 为真，返回  NULL，否则返回  expr1。它等同于  CASE WHEN x = y THEN NULL ELSE x END：

mysql> SELECT NULLIF(1,1);
        -> NULL
mysql> SELECT NULLIF(1,2);
        -> 1

注意，如果参数不相等，在 MySQL 中， expr1 被求值两次。

IF(expr1,expr2,expr3)

如果  expr1 为真( expr1 <> 0 以及  expr1 <> NULL)，那么  IF() 返回  expr2，否则返回  expr3。 IF() 返回一个数字或字符串，这取决于它被使用的语境：

mysql> SELECT IF(1>2,2,3);
        -> 3
mysql> SELECT IF(1<2,'yes','no');
        -> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
        -> 'no'

如果  expr2 或  expr3 明确地为  NULL，那么函数  IF() 的返回值类型为非  NULL 列的类型。(这在选择在 MySQL 4.0.3 中新加入)。  expr1 是作为一个整数值被计算的，这就意味着，如果测试的是一个浮点型或字符串值，就必须进行比较操作：

mysql> SELECT IF(0.1,1,0);
        -> 0
mysql> SELECT IF(0.1<>0,1,0);
        -> 1

在上面第一种情况下， IF(0.1) 返回  0，是因为  0.1 被转换为一个整数值，返回  IF(0) 的测试结果。这可能不是你所期望的。在第二种情况下，比较测试原浮点数是否为一个非零值。比较的结果被作为整数使用。 缺省的  IF() 返回值类型 (当结果存储在临时表中时，这是非常重要的) 在 MySQL 3.23 中按下列方式确定：

表达式	返回值
表达式(expr2)或表达式(expr3)返回值为字符串	字符串
表达式(expr2)或表达式(expr3)返回值为浮点型值	浮点型
表达式(expr2)或表达式(expr3)返回值为整型	整型

如果表达式(expr2)和表达式(expr3)均是字符串，同时两个字符串均是忽略字母大小写的，那么返回值也是忽略字母大小写的(从 MySQL 3.23.51 开始)。

CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END

CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

第一个形式当  value=compare-value 时返回  result。第二个形式当第一个为真值的  condition 出现时，返回该条件的结果。如果没有匹配的结果值，那么 ELSE 后的结果将被返回。如果没有  ELSE 部分，那么  NULL 被返回：

mysql> SELECT CASE 1 WHEN 1 THEN "one"
           WHEN 2 THEN "two" ELSE "more" END;
       -> "one"
mysql> SELECT CASE WHEN 1>0 THEN "true" ELSE "false" END;
       -> "true"
mysql> SELECT CASE BINARY "B" WHEN "a" THEN 1 WHEN "b" THEN 2 END;
       -> NULL

返回值的类型 (INTEGER、DOUBLE 或 STRING) 与第一个返回值(第一个 THEN 后的表达式)的类型相同。

MySQL控制流程函数
首先创建演示表salary
mysql> use test;
mysql> create table salary (userid int, salary decimal(9,2));
mysql> insert into salary values (1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);
mysql> select * from salary;
+--------+---------+
| userid | salary   |
+--------+---------+
|       1 | 1000.00 |
|       2 | 2000.00 |
|       3 | 3000.00 |
|       4 | 4000.00 |
|       5 | 5000.00 |
|       1 |     NULL |
+--------+---------+

1.IF(expr1,expr2,expr3)函数：如果expr1为真则返回expr2，否则返回expr3
mysql> ? IF FUNCTION
mysql> select IF( salary > 2000, 'High', 'Low') from salary;
+------------------------------------+
| if ( salary > 2000, 'High', 'Low') |
+------------------------------------+
| Low                                 |
| Low                                 |
| High                                |
| High                                |
| High                                |
| Low                                 |
+------------------------------------+

2.IFNULL(expr1,expr2)函数：若expr1为NULL则返回expr2内容
mysql> ? IFNULL
mysql> select ifnull(salary,0) from salary;
+------------------+
| ifnull(salary,0) |
+------------------+
|           1000.00 |
|           2000.00 |
|           3000.00 |
|           4000.00 |
|           5000.00 |
|              0.00 |
+------------------+

3.NULLIF(expr1,expr2)函数：若expr1 = expr2则返回NULL，否则返回expr1
mysql> ? NULLIF
mysql> select nullif(salary,2000) from salary;
+---------------------+
| nullif(salary,2000) |
+---------------------+
|              1000.00 |
|                 NULL |
|              3000.00 |
|              4000.00 |
|              5000.00 |
|                 NULL |
+---------------------+

4.CASE函数
语法如下：
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
或
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
mysql> ? CASE FUNCTION
mysql> select case when salary <= 2000 then 'Low' else 'High' end from salary;
+-----------------------------------------------------+
| case when salary <= 2000 then 'Low' else 'High' end |
+-----------------------------------------------------+
| Low                                                  |
| Low                                                  |
| High                                                 |
| High                                                 |
| High                                                 |
| High                                                 |
+-----------------------------------------------------+

mysql> select case salary when 1000 then 'Low' when 2000 then 'Mid' else 'High' end from salary;
+-----------------------------------------------------------------------+
| case salary when 1000 then 'Low' when 2000 then 'Mid' else 'High' end |
+-----------------------------------------------------------------------+
| Low                                                                    |
| Mid                                                                    |
| High                                                                   |
| High                                                                   |
| High                                                                   |
| High                                                                   |
+-----------------------------------------------------------------------+

五、其他常用函数
1.DATABASE()函数：用于查询当前使用数据库的名字（类似Oracle的show user;）
mysql> ? database
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test        |
+------------+

2.VERSION()函数：用户查询所使用数据库的版本
mysql> ? version
mysql> SELECT VERSION();
+------------+
| VERSION()   |
+------------+
| 5.0.22-log |
+------------+

3.USER()函数：查询当前登陆用户名
mysql> SELECT USER();
+----------------+
| USER()          |
+----------------+
| root@localhost |
+----------------+

4.INET_ATON(expr)函数：查询IP地址的网络字节序表示，反函数是INET_NTOA
mysql> SELECT INET_ATON('144.194.192.183');
+------------------------------+
| INET_ATON('144.194.192.183') |
+------------------------------+
|                    2428682423 |
+------------------------------+

5.INET_NTOA(expr)函数：查询网络字节序代表的IP地址，是INET_ATON的反函数
mysql> ? inet_ntoa
mysql> SELECT INET_NTOA(2428682423);
+-----------------------+
| INET_NTOA(2428682423) |
+-----------------------+
| 144.194.192.183        |
+-----------------------+