关于mysql null 值的一些问题

最近在项目中用mysql查询数据库时发现了一些可以为null的字段很纠结。

我们先看看含有null的字段查询

关于explain的用法，可以看看这里

我的mysql版本：

yehaha@ubuntu:$ mysql --version
mysql  Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using  EditLine wrappe

创建库

create table test(id int,name varchar(10));

insert into test values(1,'a');
insert into test values(2,'b');
insert into test values(3,null);

看一看表

mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | NULL |
+------+------+

在没有索引的情况下，所有的搜索都是

mysql> explain select * from test where  name = "a";
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

给name创建索引

create index  name_index on test (name);

再搜索

mysql> explain select * from test where  name = "a";
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ref  | name_index    | name_index | 13      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+

通过type是ref可以看到这里用到了索引， 那么null的情况怎么用索引的呢？

mysql> explain select * from test where  name is null;
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test  | NULL       | ref  | name_index    | name_index | 13      | const |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

通过这个可以看到is null是可以用到索引的，而且跟 =”a”,差不多的。

is not null也可用到索引， not null是一个范围索引 type是range

mysql> explain select * from test where  name is  not null;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test  | NULL       | range | name_index    | name_index | 13      | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

那结论就是，如果没有索引的字段，那么对于是不是null都是全表数据遍历，如果有索引 is null 和 is not null 都会用到索引。

然后我们看下用in查询：
先看查询数据

mysql> select * from test where  name in ("a");
+------+------+
| id   | name |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.01 sec)

mysql> select * from test where  name not in ("a");
+------+------+
| id   | name |
+------+------+
|    2 | b    |
+------+------+
1 row in set (0.00 sec)

mysql> select * from test where  name  in ("a", null);
+------+------+
| id   | name |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

不管用in 还是not in 数据都不会查到name是null的行；
mysql> select * from test where name = null;
Empty set (0.00 sec)
原因是： in 会被mysql转化成 name=”a” or name=”b” or name=null，等号不能比较null数据，而在mysql中定义null是不可知，不确定，所以null不能和任何值进行比较（包括null本身）

Think of the null as "unknown" in that case (or "does not exist"). In either of those cases, you can't say that they are equal, because you don't know the value of either of them. So, null=null evaluates to not true (false or null, depending on your system), because you don't know the values to say that they ARE equal. This behavior is defined in the ANSI SQL-92 standard.

那么反之not in 也不会查询到null的结果。

然后看一下两个的性能：

mysql> explain select * from test where  name in ("a");
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ref  | name_index    | name_index | 13      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test where  name not in ("a");
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | name_index    | NULL | NULL    | NULL |    3 |    66.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

那么是不是因为null引起的， 我们换id试一下（理论上null会建立所以不应该是null引起的）

mysql> explain select * from test where  id not in ("a");
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | id_index      | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

也是这样的，加上非空属性呢？

mysql> alter table test add age int(4) default 0 not null;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update test set age=1 where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> create index  age_index on test (age);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------+------+-----+
| id   | name | age |
+------+------+-----+
|    1 | a    |   1 |
|    2 | b    |   0 |
|    3 | NULL |   0 |
+------+------+-----+
3 rows in set (0.00 sec)

mysql> explain select * from test where age not in (0);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | age_index     | NULL | NULL    | NULL |    3 |    66.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

也不是，所以以后少用 not in

那么<>呢

mysql> explain select * from test where age <> 0;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | age_index     | NULL | NULL    | NULL |    3 |    66.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

猜测： not in, <> 用的算法是不是排除法，而导致需要搜索所有的值？

如果加了where呢，是不是在 where的其他筛选过的结果里找值呢？

mysql> mysql> explain select * from test where age <> 0 and id>2;
+----+-------------+-------+------------+-------+--------------------+----------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys      | key      | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+--------------------+----------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | test  | NULL       | range | id_index,age_index | id_index | 5       | NULL |    1 |    66.67 | Using index condition; Using where |
+----+-------------+-------+------------+-------+--------------------+----------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.01 sec)

可以看到的确用到了索引

总结：

1. 数据库没有索引查找某字段值，是全文扫描。
2. 用is null 和is not null都可以用的索引。
3. 用in 是匹配不到null值的
4. not in 和 <> 在搜索的时候用的全表扫描。