Mysql 关于处理NULL值的相关函数和操作符

操作符

<=>

NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.
NULL安全的相等比较操作符。这个操作符和‘=’操作符一样用来执行相等的比较,但返回1而不是NULL如果两个操作数都是NULL,返回0而不是NULL如果只有一个操作数是零。

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL

For row comparisons, (a, b) <=> (x, y) is equivalent to:
(a <=> x) AND (b <=> y)

IS NULL

Tests whether a value is NULL.

mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
        -> 0, 0, 1

IS NOT NULL

Tests whether a value is not NULL.

mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
        -> 1, 1, 0

函数

ISNULL(expr)

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

mysql> SELECT ISNULL(1+1);
        -> 0
mysql> SELECT ISNULL(1/0);
        -> 1

ISNULL() can be used instead of = to test whether a value is NULL. (Comparing a value to NULL using = always yields NULL.)
The ISNULL() function shares some special behaviors with the IS NULL comparison operator. See the description of IS NULL.

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.

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'

The default return type of IFNULL(expr1,expr2) is the more “general” of the two expressions, in the order STRING, REAL, or INTEGER.
IFNULL(expr1,expr2)的默认结果值为两个表达式中更加“通用”的一个，顺序为STRING、 REAL或 INTEGER。

 Consider the case of a table based on expressions or where MySQL must internally store a value returned by IFNULL() in a temporary table:
如果一个表是基于表达式的表或MySQL必须在一个临时表中存储一个IFNULL()的返回值：   
  

mysql> CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;
mysql> DESCRIBE tmp;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| test  | varbinary(4) | NO   |     |         |       |
+-------+--------------+------+-----+---------+-------+

In this example, the type of the test column is VARBINARY(4) (a string type).
在这个例子中，测试列的类型为 VARBINARY(4) (a string type).

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.

The return value has the same type as the first argument.

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

COALESCE

用法

COALESCE(value,...)

说明

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.
返回参数列表里边第一个非空值，如果没有非空值，则返回NULL

The return type of COALESCE() is the aggregated type of the argument types.

mysql> SELECT COALESCE(NULL,1);
        -> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
        -> NULL

mysql>SELECT COALESCE(A, B, C);
        -> A if A is non-NULL else B if B is non-NULL else C if C is non-NULL else NULL

参考资料

https://dev.mysql.com/doc/refman/8.0/en/func-op-summary-ref.html

转载于:https://www.cnblogs.com/lanqiu5ge/p/9511898.html