MySQL聚合函数操作NULL字段要小心

1.COUNT(expr)

Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.
COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values.

  • count(*):不忽略NULL值,有几行算几行;
  • count(num):如果字段值为NULL,该行忽略不统计。

因此,针对某个字段进行统计时,如果该字段可能为NULL就要注意了,这可能不是你想要的结果。

2.SUM([DISTINCT] expr)

If there are no matching rows, SUM() returns NULL.

SUM函数,会忽略NULL值,对非NULL值求和。
但需要注意的是,如果没查到数据,或字段全为NULL,则SUM函数返回NULL。

为避免出现空指针,一般我们希望的是返回0。解决方法如下:

select coalesce(sum(num),0)...
or
select ifnull(sum(num),0)...

3.COALESCE(value,…)

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

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

示例:

SELECT COALESCE(NULL,1); -- 1

SELECT COALESCE(2,NULL); -- 2

SELECT COALESCE(NULL,3,2); -- 3

SELECT COALESCE(NULL,NULL,NULL); -- NULL

4.IFNULL(expr1,expr2)

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

示例:

SELECT IFNULL(1,0); -- 1

SELECT IFNULL(NULL,10); -- 10

SELECT IFNULL(1/0,10); -- 10

SELECT IFNULL(1/0,'yes'); -- 'yes'

5.If there are no matching rows

  • AVG() returns NULL.
  • COUNT() returns 0.
  • COUNT(DISTINCT) returns 0.
  • MAX() returns NULL.
  • MIN() returns NULL.
  • SUM() returns NULL.

参考

https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html

https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_coalesce

https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#function_ifnull


---转载本站文章请注明作者和出处 二进制之路(binarylife.icu),请勿用于任何商业用途---

留下评论