PostgreSQL MySQL 兼容性：控制流函数
CASE
MySQL
CASE OPERATOR
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
SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
+------------------------------------------------------------+
| CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END |
+------------------------------------------------------------+
| one |
+------------------------------------------------------------+
SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
+--------------------------------------------+
| CASE WHEN 1>0 THEN 'true' ELSE 'false' END |
+--------------------------------------------+
| true |
+--------------------------------------------+
SELECT CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
+-----------------------------------------------------+
| CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END |
+-----------------------------------------------------+
| NULL |
+-----------------------------------------------------+
PostgreSQL
用法一样
IFNULL
PostgreSQL
postgres=# create or replace function ifnull(numeric,numeric) returns numeric as $$
select case when $1 is not null then $1 else $2 end;
$$ language sql;
CREATE FUNCTION
postgres=# select ifnull(null,2);
ifnull
--------
2
(1 row)
postgres=# select ifnull(1,3);
ifnull
--------
1
(1 row)
或者使用coalesce
postgres=# select coalesce(null,2);
coalesce
----------
2
(1 row)
postgres=# select coalesce(1,3);
coalesce
----------
1
(1 row)
MySQL
IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.
SELECT IFNULL(1,0);
+-------------+
| IFNULL(1,0) |
+-------------+
| 1 |
+-------------+
SELECT IFNULL(NULL,10);
+-----------------+
| IFNULL(NULL,10) |
+-----------------+
| 10 |
+-----------------+
SELECT IFNULL(1/0,10);
+----------------+
| IFNULL(1/0,10) |
+----------------+
| 10.0000 |
+----------------+
SELECT IFNULL(1/0,'yes');
+-------------------+
| IFNULL(1/0,'yes') |
+-------------------+
| yes |
+-------------------+
nullif
PostgreSQL
postgres=# select nullif(1,1);
nullif
--------
(1 row)
postgres=# select nullif(1,2);
nullif
--------
1
(1 row)
MySQL
NULLIF(expr1,expr2)
Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
SELECT NULLIF(1,1);
+-------------+
| NULLIF(1,1) |
+-------------+
| NULL |
+-------------+
SELECT NULLIF(1,2);
+-------------+
| NULLIF(1,2) |
+-------------+
| 1 |
+-------------+
IF
PostgreSQL
create or replace function if(boolean, text, text) returns text as $$
select case when $1 and $1 is not NULL then $2 else $3 end;
$$ language sql ;
create or replace function if(boolean, numeric, numeric) returns numeric as $$
select case when $1 and $1 is not NULL then $2 else $3 end;
$$ language sql ;
create or replace function strcmp(text,text) returns boolean as $$
select $1<>$2;
$$ language sql;
postgres=# SELECT IF(1>2,2,3);
if
----
3
(1 row)
postgres=# SELECT IF(1<2,'yes','no');
if
-----
yes
(1 row)
postgres=# SELECT IF(STRCMP('test','test1'),'no','yes');
if
----
no
(1 row)
MySQL
IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3.
IF() returns a numeric or string value, depending on the context in which it is used.
SELECT IF(1>2,2,3);
+-------------+
| IF(1>2,2,3) |
+-------------+
| 3 |
+-------------+
SELECT IF(1<2,'yes','no');
+--------------------+
| IF(1<2,'yes','no') |
+--------------------+
| yes |
+--------------------+
SELECT IF(STRCMP('test','test1'),'no','yes');
+---------------------------------------+
| IF(STRCMP('test','test1'),'no','yes') |
+---------------------------------------+
| no |
+---------------------------------------+
转播
