MySQL中NULL和空值的区别及说明

目录

• MySQL中NULL和空值的区别
• 简介
• 区别
• 区别验证
• 总结

MySQL中NULL和空值的区别

简介

NULL也就是在字段中存储NULL值，空值也就是字段中存储空字符(’’)。

区别

1、空值不占空间，NULL值占空间。当字段不为NULL时，也可以插入空值。

2、当使用 IS NOT NULL 或者 IS NULL 时，只能查出字段中没有不为NULL的或者为 NULL 的，不能查出空值。

3、判断NULL 用IS NULL 或者 is not null,SQL 语句函数中可以使用IFNULL()函数来进行处理，判断空字符用 =’‘或者<>’'来进行处理。

4、在进行count()统计某列的记录数的时候，如果采用的NULL值，会别系统自动忽略掉，但是空值是会进行统计到其中的。

5、MySql中如果某一列中含有NULL，那么包含该列的索引就无效了。这一句不是很准确。

6：实际到底是使用NULL值还是空值(’’)，根据实际业务来进行区分。个人建议在实际开发中如果没有特殊的业务场景，可以直接使用空值。

区别验证

1、占用空间区别

 mysql> select length(NULL), length(''), length('1'); +--------------+------------+-------------+ | length(NULL) | length('') | length('1') | +--------------+------------+-------------+ | NULL | 0 | 1 | +--------------+------------+-------------+ 1 row in set 

通俗的讲：

空值就像是一个真空转态杯子，什么都没有，而NULL值就是一个装满空气的杯子，虽然看起来都是一样的，但是有着本质的区别。

NULL columns require additional space in the row to record whether their values are NULL.

NULL列需要行中的额外空间来记录它们的值是否为NULL。

小结：从上面看出空值(’’)的长度是0，是不占用空间的；而的NULL长度是NULL，其实它是占用空间的，看下面说明。

2、插入/查询方式区别

 CREATE TABLE `tb_test` ( `one` varchar(10) NOT NULL, `two` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 全部插入 NULL，失败 mysql> INSERT tb_test VALUES (NULL,NULL); 1048 - Column 'one' cannot be null -- 全部插入 空值，成功 mysql> INSERT tb_test VALUES ('',''); Query OK, 1 row affected -- 模拟数据： INSERT tb_test VALUES (1,NULL); INSERT tb_test VALUES ('',2); INSERT tb_test VALUES (3,3); ` --空值字段： -- 使用 is null/is not null mysql> SELECT * FROM tb_test where one is NULL; Empty set mysql> SELECT * FROM tb_test where one is not NULL; +-----+------+ | one | two | +-----+------+ | 1 | NULL | | | 2 | | 3 | 3 | +-----+------+ 3 rows in set -- 使用 = 、!= mysql> SELECT * FROM tb_test where one = ''; +-----+-----+ | one | two | +-----+-----+ | | 2 | +-----+-----+ 1 row in set mysql> SELECT * FROM tb_test where one != ''; +-----+------+ | one | two | +-----+------+ | 1 | NULL | | 3 | 3 | +-----+------+ 2 rows in set --NULL值字段： -- 使用 is null/is not null mysql> SELECT * FROM tb_test where two is not NULL; +-----+-----+ | one | two | +-----+-----+ | | 2 | | 3 | 3 | +-----+-----+ 2 rows in set mysql> SELECT * FROM tb_test where two is NULL; +-----+------+ | one | two | +-----+------+ | 1 | NULL | +-----+------+ 1 row in set -- 使用 = 、!= mysql> SELECT * FROM tb_test where two = ''; Empty set mysql> SELECT * FROM tb_test where two != ''; +-----+-----+ | one | two | +-----+-----+ | | 2 | | 3 | 3 | +-----+-----+ 2 rows in set 

小结：

如果要单纯查NULL值列，则使用 is NULL去查，单纯去查空值(’’)列，则使用 =’’。

建议查询方式：NULL值查询使用is null/is not null查询，而空值(’’)可以使用=或者!=、<、>等算术运算符。

3、COUNT 和 IFNULL函数

使用COUNT函数：

 mysql> SELECT count(one) FROM tb_test; +------------+ | count(one) | +------------+ | 3 | +------------+ 1 row in set mysql> SELECT count(two) FROM tb_test; +------------+ | count(two) | +------------+ | 2 | +------------+ 1 row in set mysql> SELECT count(*) FROM tb_test; +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set 

使用IFNULL函数：

 mysql> SELECT IFNULL(one,) from tb_test WHERE one = ''; +-----------------------+ | IFNULL(one,) | +-----------------------+ | | +-----------------------+ 1 row in set mysql> SELECT IFNULL(two,) from tb_test where two is NULL; +----------------------+ | IFNULL(two,) | +----------------------+ |  | +----------------------+ 1 row in set 

小结：使用 COUNT(字段) 统计会过滤掉 NULL 值，但是不会过滤掉空值。

说明：

IFNULL有两个参数。如果第一个参数字段不是NULL，则返回第一个字段的值。

否则，IFNULL函数返回第二个参数的值（默认值）。

4、索引字段说明

看到网上有一些人说： MySql中如果某一列中含有NULL，那么包含该列的索引就无效了。

一个普通索引，一个复合索引。

复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。

 -- ALTER TABLE table_name ADD INDEX index_name(col_name); ALTER TABLE tb_test ADD INDEX index_oat (one, two); ALTER TABLE tb_test add INDEX index_two(two); 

使用 show keys from 表名;或show indexes from 表名; ，查看这个表的所有索引信息。

复合索引

普通索引

发现查询two字段 是可以正常使用索引的。我使用的MYSQL 5.7 ,InnoDB 引擎。MySQL可以在含有null的列上使用索引。可能是其他条件下不行。

小结：在有NULL值得字段上使用常用的索引，如普通索引、复合索引、全文索引等不会使索引失效。在官网查看在空间索引的情况下，说明了 索引列必须为NOT NULL。

附官网查看

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持本网站。

您可能感兴趣的文章:

• MySQL导出数据遇到secure-file-priv问题的解决方法
• 一文总结使用MySQL时遇到null值的坑
• MySQL中的case when中对于NULL值判断的坑及解决
• windows下如何解决mysql secure_file_priv null问题