mysql中null处理,谈谈关系数据库中null的处理

我们在设计数据库时，常常会允许一些字段为null。但是在后续处理null时，由于null的特殊性，可能在算术运算、逻辑运算中带来一些始料不及的情况，我们来总结写null的处理特例情况。

算术运算，比较运算

null在算术运算代表不知道的值，所以任何算术表达式中出现了null，其结果也是一个不知道的值，也就是null

mysql> select * from person;

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

| id | name | memo | age |

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

| 1 | mike | memo1 | 30 |

| 2 | john | memo2 | NULL |

| 3 | tom | memo3 | 25 |

| 4 | mary | NULL | NULL |

| 5 | mary | NULL | NULL |

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

5 rows in set (0.00 sec)

mysql> select age*2 from person;

+-------+

| age*2 |

+-------+

| 60 |

| NULL |

| 50 |

| NULL |

| NULL |

+-------+

5 rows in set (0.00 sec)

mysql>

mysql> select age+2 from person;

+-------+

| age+2 |

+-------+

| 32 |

| NULL |

| 27 |

| NULL |

| NULL |

+-------+

5 rows in set (0.00 sec)

mysql>

mysql> select null>1 ;

+--------+

| null>1 |

+--------+

| NULL |

+--------+

1 row in set (0.00 sec)

mysql> select null<1;

+--------+

| null<1 |

+--------+

| NULL |

+--------+

1 row in set (0.00 sec)

mysql> select null=1;

+--------+

| null=1 |

+--------+

| NULL |

+--------+

1 row in set (0.00 sec)

逻辑运算

与运算：

true and null= null;

false and null=false;

null and null=null;

mysql> select true and null ;

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

| true and null |

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

| NULL |

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

1 row in set (0.00 sec)

mysql> select false and null;

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

| false and null |

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

| 0 |

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

1 row in set (0.00 sec)

mysql> select null and null;

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

| null and null |

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

| NULL |

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

1 row in set (0.00 sec)

mysql>

或运算

true or null=true;

flase or null=null;

null or null=null;

mysql> select true or null;

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

| true or null |

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

| 1 |

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

1 row in set (0.00 sec)

mysql> select false or null

-> ;

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

| false or null |

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

| NULL |

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

1 row in set (0.00 sec)

mysql> select null or null;

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

| null or null |

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

| NULL |

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

1 row in set (0.00 sec)

mysql>

非运算

not(null)=null;

mysql> select not(null);

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

| not(null) |

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

| NULL |

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

1 row in set (0.00 sec)

mysql>

where 语句

如果想通过select将期待的结果集查询出来，其where条件必须是true。其他值包括(null，false)一概不予返回。

mysql> select * from person ;

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

| id | name | memo | age |

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

| 1 | mike | memo1 | 30 |

| 2 | john | memo2 | NULL |

| 3 | tom | memo3 | 25 |

| 4 | mary | NULL | NULL |

| 5 | mary | NULL | NULL |

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

5 rows in set (0.00 sec)

mysql> select * from person where age>25;

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

| id | name | memo | age |

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

| 1 | mike | memo1 | 30 |

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

1 row in set (0.00 sec)

mysql>

mysql> select * from person where age is null;

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

| id | name | memo | age |

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

| 2 | john | memo2 | NULL |

| 4 | mary | NULL | NULL |

| 5 | mary | NULL | NULL |

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

3 rows in set (0.00 sec)

join语句

在join条件中，如果有null值存在，结果是不匹配。

mysql> select * from person ;

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

| id | name | memo | age |

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

| 1 | mike | memo1 | 30 |

| 2 | john | memo2 | NULL |

| 3 | tom | memo3 | 25 |

| 4 | mary | NULL | NULL |

| 5 | mary | NULL | NULL |

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

5 rows in set (0.00 sec)

mysql> select * from teacher;

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

| id | name | age |

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

| 1 | mike | 30 |

| 4 | mary | 35 |

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

2 rows in set (0.00 sec)

mysql> select * from person natural join teacher;

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

| id | name | age | memo |

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

| 1 | mike | 30 | memo1 |

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

1 row in set (0.00 sec)

mysql>

集合语句

集合语句中，多个null会被当作重复值来处理，有点类似空格似的。

mysql> select * from person;

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

| id | name | memo | age |

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

| 1 | mike | memo1 | 30 |

| 2 | john | memo2 | NULL |

| 3 | tom | memo3 | 25 |

| 4 | mary | NULL | NULL |

| 5 | mary | NULL | NULL |

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

5 rows in set (0.00 sec)

mysql> select * from teacher;

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

| id | name | age |

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

| 1 | mike | 30 |

| 4 | mary | 35 |

| 2 | john | NULL |

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

3 rows in set (0.00 sec)

mysql> select name ,age from person union select name,age from teacher;

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

| name | age |

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

| mike | 30 |

| john | NULL |

| tom | 25 |

| mary | NULL |

| mary | 35 |

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

5 rows in set (0.00 sec)

mysql> select age from person union select age from teacher;

+------+

| age |

+------+

| 30 |

| NULL |

| 25 |

| 35 |

+------+

4 rows in set (0.00 sec)

mysql>

group by 语句

在group by语句中，多个null会被当作重复值。

mysql> select * from person;

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

| id | name | memo | age |

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

| 1 | mike | memo1 | 30 |

| 2 | john | memo2 | NULL |

| 3 | tom | memo3 | 25 |

| 4 | mary | NULL | NULL |

| 5 | mary | NULL | NULL |

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

5 rows in set (0.00 sec)

mysql> select count(*) from person group by age;

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

| count(*) |

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

| 1 |

| 3 |

| 1 |

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

3 rows in set (0.00 sec)

mysql> select count(*) from person group by name,age;

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

| count(*) |

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

| 1 |

| 1 |

| 1 |

| 2 |

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

4 rows in set (0.00 sec)

select 语句

select对于字段的处理默认不会去重，对null也不例外。

mysql> select age from person;

+------+

| age |

+------+

| 30 |

| NULL |

| 25 |

| NULL |

| NULL |

+------+

5 rows in set (0.00 sec)

mysql> select distinct age from person;

+------+

| age |

+------+

| 30 |

| NULL |

| 25 |

+------+

3 rows in set (0.00 sec)

mysql>

聚集函数

聚集函数在处理数据前，会剔除掉null，这与算术处理中逻辑不同，这是因为考虑到聚集函数结果不会仅仅因为一个null而成为null，从而失去实际的计算意义。但是如果处理结果是null，结果也就显示null。

mysql> select * from person;

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

| id | name | memo | age |

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

| 1 | mike | memo1 | 30 |

| 2 | john | memo2 | NULL |

| 3 | tom | memo3 | 25 |

| 4 | mary | NULL | NULL |

| 5 | mary | NULL | NULL |

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

5 rows in set (0.00 sec)

mysql> select count(age) from person;

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

| count(age) |

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

| 2 |

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

1 row in set (0.00 sec)

mysql>

mysql> select count(age) from person where age is null;

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

| count(age) |

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

| 0 |

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

1 row in set (0.00 sec)

mysql> select max(age) from person where age is null;

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

| max(age) |

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

| NULL |

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

1 row in set (0.00 sec)

mysql> select avg(age) from person where age is null;

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

| avg(age) |

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

| NULL |

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

1 row in set (0.00 sec)