β

PostgreSQL MySQL 兼容性之 - 空(NULL)

PostgreSQL research 282 阅读

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

(1 row)

postgres=# select null <=> null;
 ?column? 
----------

(1 row)

IS NULL
IS NOT NULL

coalesce

MySQL

  coalesce

PostgreSQL

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

(1 row)

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

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





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



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





(5 rows)
postgres=# select * from test order by id desc;
 id 
----



(5 rows)

postgres=# select * from test order by id desc nulls first;
 id 
----



(5 rows)

postgres=# select * from test order by id desc nulls last;
 id 
----





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

(1 row)

postgres=# select ifnull(1,3);
 ifnull 
--------

(1 row)

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

(1 row)

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

(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
(2 rows)
作者:PostgreSQL research
Free PostgreSQL Support. 公益是一辈子的事, I&apos;m 德哥@Digoal, Just Do it!
原文地址:PostgreSQL MySQL 兼容性之 - 空(NULL), 感谢原作者分享。

发表评论