PostgreSQL MySQL 兼容性之 - 空(NULL)

NULL compare operator <=>

MySQL

SELECT 99 <=> NULL, NULL <=> NULL;
+-------------+---------------+
| 99 <=> NULL | NULL <=> NULL |
+-------------+---------------+
|           0 |             1 |
+-------------+---------------+

IS NULL
IS NOT NULL

PostgreSQL

针对不同的类型，需要创建不同的函数 和 <=>
create or replace function nulleq(int,int) returns int as 
$$

declare
begin
  if $1 is null and $2 is null then
    return 1;
  else
    return 0; 
  end if;
end;

$$
 language plpgsql;

postgres=# create operator <=> (procedure=nulleq,leftarg=int,rightarg=int);
CREATE OPERATOR
postgres=# select 1 <=> null;
 ?column? 
----------
        0
(1 row)

postgres=# select null <=> null;
 ?column? 
----------
        1
(1 row)

IS NULL
IS NOT NULL

coalesce

MySQL

  coalesce

PostgreSQL

postgres=# select coalesce(null,1,2);
 coalesce 
----------
        1
(1 row)

postgres=# select coalesce(null,null,2);
 coalesce 
----------
        2
(1 row)

postgres=# select coalesce('a',null,'b');
 coalesce 
----------
 a
(1 row)

order

MySQL

  SELECT col1 FROM tab ORDER BY ISNULL(col1), col1;  -- null is first
  SELECT col1 FROM tab ORDER BY IF(col1 IS NULL, 0, 1), col1 DESC;  -- Descending order, with NULLs first

  All NULL values are also regarded as equivalent for the purposes of the DISTINCT and GROUP BY clauses.

PostgreSQL

默认nulls比其他值更大
postgres=# create table test(id int);
CREATE TABLE
postgres=# insert into test values (1),(2),(3),(null),(null);
INSERT 0 5
postgres=# select * from test order by test;
 id 
----
  1
  2
  3
   
   
(5 rows)
postgres=# select * from test order by id nulls first;
 id 
----
   
   
  1
  2
  3
(5 rows)
postgres=# select * from test order by id nulls last;
 id 
----
  1
  2
  3
   
   
(5 rows)
postgres=# select * from test order by id desc;
 id 
----
   
   
  3
  2
  1
(5 rows)

postgres=# select * from test order by id desc nulls first;
 id 
----
   
   
  3
  2
  1
(5 rows)

postgres=# select * from test order by id desc nulls last;
 id 
----
  3
  2
  1
   
   
(5 rows)

ISNULL, NULLIF, IFNULL

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 |
+-----------------+

  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 |
+-------------+

  ISNULL(expr)
    If expr is NULL, ISNULL() returns 1, otherwise it returns 0.

SELECT ISNULL(1+1);
+-------------+
| ISNULL(1+1) |
+-------------+
|           0 |
+-------------+

SELECT ISNULL(1/0);
+-------------+
| ISNULL(1/0) |
+-------------+
|           1 |
+-------------+

  ISNULL(expr)
    If expr is NULL, ISNULL() returns 1, otherwise it returns 0.
SELECT ISNULL(1+1);
+-------------+
| ISNULL(1+1) |
+-------------+
|           0 |
+-------------+

SELECT ISNULL(1/0);
+-------------+
| ISNULL(1/0) |
+-------------+
|           1 |
+-------------+

PostgreSQL

postgres=# create or replace function ifnull(int,int) returns int 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)

nullif
postgres=# select nullif(1,1);
 nullif 
--------
       
(1 row)

postgres=# select nullif(1,2);
 nullif 
--------
      1
(1 row)

isnull
postgres=# create or replace function isnull(anyelement) returns int as 
$$

select case when $1 is null then 1 else 0 end;              

$$
 language sql;
CREATE FUNCTION

ostgres=# create table ttt(id int);
CREATE TABLE
postgres=# insert into ttt values (null);
INSERT 0 1
postgres=# insert into ttt values (1);
INSERT 0 1
postgres=# select isnull(id),id from ttt;
 isnull | id 
--------+----
      1 |   
      0 |  1
(2 rows)