postgresql mysql 兼容_PostgreSQL MySQL 兼容性：控制流函数

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 |

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