mysql length函数null_MySQL的几个length函数以及聚合函数对于NULL的处理

数据库基于MySQL，其他数据库有些不一样

最近跟着CSDN上的一位博主的MySQL系列文章学习了下，做下笔记复习巩固下。

length函数：

char_length:字符个数

length:字节个数

bit_length:二进制个数

mysql> select length('a'),char_length('a'),bit_length('a');+-------------+------------------+-----------------+

| length('a') | char_length('a') | bit_length('a') |

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

| 1 | 1 | 8 |

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

1 row in set (0.00sec)

mysql> select length('啊'),char_length('啊'),bit_length('啊');+---------------+--------------------+-------------------+

| length('啊') | char_length('啊') | bit_length('啊') |

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

| 3 | 1 | 24 |

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

1 row in set (0.00 sec)

聚合函数对于NULL

COUNT(*):会对所有的NULL计数，

COUNT(1):会对所有的NULL计数,

COUNT(某列):会忽略所有的NULL,

COUNT(NULL):返回0

SUM():会忽略NULL

MAX():忽略NULL

MIN():忽略NULL

AVG():忽略NULL

上面这4个都会忽略null比较好理解：毕竟NULL无法参与计数，但是要注意是忽略NULL而不是把NULL当成0，通过这个就能说明：

mysql> select avg(v) fromtest_count;+--------+

| avg(v) |

+--------+

| 1.5000 |

+--------+

1 row in set (0.04sec)

mysql> select * fromtest_count;+------+

| v |

+------+

| 1 |

| 2 |

| NULL |

+------+

3 rows in set (0.00sec)

#平均数结果是1.5，所以能证明除以的是2而不是3，能证明NULL被忽略了

group by:这个以前我还没注意，NULL列会在查询之后放在最前面

mysql> select * fromtest_count;+------+

| v |

+------+

| 1 |

| 2 |

| NULL |

+------+

3 rows in set (0.00sec)

mysql> select * from test_count group byv;+------+

| v |

+------+

| NULL |

| 1 |

| 2 |

+------+

3 rows in set (0.01 sec)

order by:NULL会被认为是最小值 (工作需要，用了postgresql，order by ,null会在最后，order by desc，null在最前面)

mysql> select * from test_count order byv;+------+

| v |

+------+

| NULL |

| -128 |

| 1 |

| 2 |

+------+

4 rows in set (0.00 sec)

distinct:NULL会被消重

mysql> select distinct * fromtest_count;+------+

| v |

+------+

| 1 |

| 2 |

| NULL |

| -128 |

+------+

4 rows in set (0.02sec)

mysql> select * fromtest_count;+------+

| v |

+------+

| 1 |

| 2 |

| NULL |

| -128 |

| NULL |

| NULL |

+------+

6 rows in set (0.00 sec)

+:与NULL相加结果都为NULL

mysql> select * fromtest_count;+------+

| v |

+------+

| 1 |

| 2 |

| NULL |

| -128 |

| NULL |

| NULL |

+------+

6 rows in set (0.00sec)

mysql> select 1+v fromtest_count;+------+

| 1+v |

+------+

| 2 |

| 3 |

| NULL |

| -127 |

| NULL |

| NULL |

+------+

6 rows in set (0.00 sec)

,<>,=:与NULL比较都返回NULL，需要用is null 或者not null

is null和is not null：结果为真返回1，结果为假返回0

mysql> select 0v,0<>v,0=v,v is null, v is not null fromtest_count;+------+------+------+------+-----------+---------------+

| 0v | 0<>v | 0=v | v is null | v is not null |

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

| 1 | 0 | 1 | 0 | 0 | 1 |

| 1 | 0 | 1 | 0 | 0 | 1 |

| NULL | NULL | NULL | NULL | 1 | 0 |

| 0 | 1 | 1 | 0 | 0 | 1 |

| NULL | NULL | NULL | NULL | 1 | 0 |

| NULL | NULL | NULL | NULL | 1 | 0 |

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

6 rows in set (0.03sec)

mysql> select * fromtest_count;+------+

| v |

+------+

| 1 |

| 2 |

| NULL |

| -128 |

| NULL |

| NULL |

+------+

6 rows in set (0.00 sec)

如果想统计NULL一共有多少行行，可以使用if函数：

mysql> select sum(if(v is null, 1, 0)) NULL一共多少行 fromtest_count;+---------------------+

| NULL一共多少行 |

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

| 4 |

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

1 row in set (0.03sec)

mysql> select sum(if(v is null, 0, 1)) 非NULL一共多少行 fromtest_count;+------------------------+

| 非NULL一共多少行 |

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

| 3 |

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

1 row in set (0.00sec)

mysql> select * fromtest_count; unt;t;+------+

| v |

+------+

| 1 |

| 2 |

| NULL |

| -128 |

| NULL |

| NULL |

| NULL |

+------+

7 rows in set (0.00 sec)