17611538698
webmaster@21cto.com

为什么MySQL不建议使用NULL作为列默认值

数据库 0 230 2024-02-19 07:09:38

图片


今天来分享这个高频面试题,5分钟搞懂“为什么MySQL不建议使用NULL作为列默认值? ”。


对于这个问题,通常能听到的答案是“使用了 NULL 值的列会使索引失效”,但是如果实际测试过一下,你就知道 IS NULL 会使用索引,所以上述说法存在缺陷。


着急的人拉到最下边看结论。


前言


Null 是列的特殊约束。如果在创建表时没有明确定义带有“not null”关键字的列,那么表中的列就会被添加空约束。很多程序员喜欢默认定义列,因为这样方便(减少为空的判断代码)从而导致查询存在一定的不确定性,数据库性能较差。


NULL值是一种对列的特殊约束,我们创建一个新列时,如果没有明确的使用关键字not null声明该数据列,Mysql会默认为我们添加上NULL约束。有些开发人员在创建数据表时,由于懒惰直接使用Mysql的默认推荐设置(即允许字段使用NULL值)。而这种陋习很容易在使用NULL的场景中指定不确定的查询结果以及导致数据库性能的崩溃。


介绍


null就是null意味着什么都没有,我们不能认为null等于'',它们是完全不同的。MySQL提供了三个操作符来处理空值:“IS NULL”、“IS NOT NULL”、“<=>”和一个函数ifnull()。IS NULL:如果列值为空,则返回 true。IS NOT NULL:如果列值不为空,则返回 true。<=>:它是一个比较运算符,与“=”类似但不相同。即使对于两个空值也返回 true。(例如,null <=> null 是合法的) IFNULL():指定两个输入参数,如果第一个为空值则返回第二个。它与 Oracle 的 NVL() 函数类似。


NULL并不代表什么都没有,我们要注意NULL跟''(空值)是两个完全不一样的值。MySQL中可以操作NULL值操作符主要有三个。


  • 一片空白

  • 不为空

  • <=>太空船符操作,这个操作符很像=,select NULL<=>NULL可以返回true,但是select NULL=NULL返回false。

  • IFNULL 一个函数。怎么用自己查吧……其实我会了


例子


当与除 null 和“<=>”之外的任何其他值进行比较时,Null 永远不会返回 true。


NULL通过任一操作符与其他值比较都会得到NULL,除了<=>。


(root@localhost mysql3306.sock)[zlm]>create table test_null(    -> id int not null,    -> name varchar(10)    -> );Query OK, 0 rows affected (0.02 sec)
(root@localhost mysql3306.sock)[zlm]>insert into test_null values(1,'zlm');Query OK, 1 row affected (0.00 sec)
(root@localhost mysql3306.sock)[zlm]>insert into test_null values(2,null);Query OK, 1 row affected (0.00 sec)
(root@localhost mysql3306.sock)[zlm]>select * from test_null;+----+------+| id | name |+----+------+| 1 | zlm || 2 | NULL |+----+------+2 rows in set (0.00 sec)
(root@localhost mysql3306.sock)[zlm]>select * from test_null where name=null;Empty set (0.00 sec)
(root@localhost mysql3306.sock)[zlm]>select * from test_null where name is null;+----+------+| id | name |+----+------+| 2 | NULL |+----+------+1 row in set (0.00 sec)
(root@localhost mysql3306.sock)[zlm]>select * from test_null where name is not null;+----+------+| id | name |+----+------+| 1 | zlm |+----+------+1 row in set (0.00 sec)
(root@localhost mysql3306.sock)[zlm]>select * from test_null where null=null;Empty set (0.00 sec)
(root@localhost mysql3306.sock)[zlm]>select * from test_null where null<>null;Empty set (0.00 sec)
(root@localhost mysql3306.sock)[zlm]>select * from test_null where null<=>null;+----+------+| id | name |+----+------+| 1 | zlm || 2 | NULL |+----+------+2 rows in set (0.00 sec)//null<=>null always return true,it's equal to "where 1=1".


Null 的意思是“缺失且未知的值”。让我们看看下面的详细信息。


NULL代表一个不确定的值,即使是两个NULL,它俩也不一定能够。(不像C中未初始化的局部变量)


(root@localhost mysql3306.sock)[zlm]>SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;+-----------+---------------+------------+----------------+| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |+-----------+---------------+------------+----------------+|         0 |             1 |          0 |              1 |+-----------+---------------+------------+----------------+1 row in set (0.00 sec)
//It's not equal to zero number or vacant string.//In MySQL,0 means fasle,1 means true.(root@localhost mysql3306.sock)[zlm]>SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;+----------+-----------+----------+----------+| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |+----------+-----------+----------+----------+| NULL | NULL | NULL | NULL |+----------+-----------+----------+----------+1 row in set (0.00 sec)
//It cannot be compared with number.//In MySQL,null means false,too.

如果任何表达式包含 null 值,则结果为 null。


任何有返回值的表达式有NULL参与时,都会得到另外一个NULL值。


(root@localhost mysql3306.sock)[zlm]>select ifnull(null,'First is null'),ifnull(null+10,'First is null'),ifnull(concat('abc',null),'First is null');+------------------------------+---------------------------------+--------------------------------------------+| ifnull(null,'First is null') | ifnull(null+10,'First is null') | ifnull(concat('abc',null),'First is null') |+------------------------------+---------------------------------+--------------------------------------------+| First is null                | First is null                   | First is null                              |+------------------------------+---------------------------------+--------------------------------------------+1 row in set (0.00 sec)

//null value needs to be disposed with ifnull() function,what usually causes sql statement more complex.//As we all know,MySQL does not support funcion index.Therefore,indexes on the column may not be used.That's really worse.


使用 count(*) 和 count(null column) 时有所不同。


使用count(*)或者count(null column)结果不同,count(null column)<=count(*)。

(root@localhost mysql3306.sock)[zlm]>select count(*),count(name) from test_null;+----------+-------------+| count(*) | count(name) |+----------+-------------+|        2 |           1 |+----------+-------------+1 row in set (0.00 sec)
//count(*) returns all rows ignore the null while count(name) returns the non-null rows in column "name".//This will also leads to uncertainty if someone is unaware of the details above.


当使用distinct、group by、order by时,所有空值都被视为相同的值。


虽然select NULL=NULL的结果为false,但是在我们使用distinct、group by、order by时,NULL又被认为是相同的值。


(root@localhost mysql3306.sock)[zlm]>insert into test_null values(3,null);Query OK, 1 row affected (0.00 sec)
(root@localhost mysql3306.sock)[zlm]>select distinct name from test_null;+------+| name |+------+| zlm || NULL |+------+2 rows in set (0.00 sec)
//Two rows of null value returned one and the result became two.(root@localhost mysql3306.sock)[zlm]>select name from test_null group by name;+------+| name |+------+| NULL || zlm |+------+2 rows in set (0.00 sec)//Two rows of null value were put into the same group.//By default,group by will also sort the result(null row showed first).
(root@localhost mysql3306.sock)[zlm]>select id,name from test_null order by name;+----+------+| id | name |+----+------+| 2 | NULL || 3 | NULL || 1 | zlm |+----+------+3 rows in set (0.00 sec)//Three rows were sorted(two null rows showed first).


MySQL支持在包含空值的列上使用索引(与oracle不同)。


MySQL中支持在含有NULL值的列上使用索引,但是Oracle不支持。这就是我们平时所说的如果列上含有NULL那么就会使索引失效。


严格来说,这句话对于 MySQL 来说是不准确的。


(root@localhost mysql3306.sock)[sysbench]>show tables;+--------------------+| Tables_in_sysbench |+--------------------+| sbtest1            || sbtest10           || sbtest2            || sbtest3            || sbtest4            || sbtest5            || sbtest6            || sbtest7            || sbtest8            || sbtest9            |+--------------------+10 rows in set (0.00 sec)
(root@localhost mysql3306.sock)[sysbench]>show create table sbtest1\G*************************** 1. row *************************** Table: sbtest1Create Table: CREATE TABLE `sbtest1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`)) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf81 row in set (0.00 sec)
(root@localhost mysql3306.sock)[sysbench]>alter table sbtest1 modify k int null,modify c char(120) null,modify pad char(60) null;Query OK, 0 rows affected (4.14 sec)Records: 0 Duplicates: 0 Warnings: 0
(root@localhost mysql3306.sock)[sysbench]>insert into sbtest1 values(100001,null,null,null);Query OK, 1 row affected (0.00 sec)
(root@localhost mysql3306.sock)[sysbench]>explain select id,k from sbtest1 where id=100001;+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| 1 | SIMPLE | sbtest1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)
(root@localhost mysql3306.sock)[sysbench]>explain select id,k from sbtest1 where k is null;+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+| 1 | SIMPLE | sbtest1 | NULL | ref | k_1 | k_1 | 5 | const | 1 | 100.00 | Using where; Using index |+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+1 row in set, 1 warning (0.00 sec)//In the first query,the newly added row is retrieved by primary key.//In the second query,the newly added row is retrieved by secondary key "k_1"//It has been proved that indexes can be used on the columns which contain null value.//column "k" is int datatype which occupies 4 bytes,but the value of "key_len" turn out to be 5.what's happed?Because null value needs 1 byte to store the null flag in the rows.


这是我自己测试的例子。


mysql> select * from test_1;+-----------+------+------+| name      | code | id   |+-----------+------+------+| gaoyi     | wo   |    1 || gaoyi     | w    |    2 || chuzhong  | wo   |    3 || chuzhong  | w    |    4 || xiaoxue   | dd   |    5 || xiaoxue   | dfdf |    6 || sujianhui | su   |   99 || sujianhui | NULL |   99 |+-----------+------+------+8 rows in set (0.00 sec)
mysql> explain select * from test_1 where code is NULL;+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+| 1 | SIMPLE | test_1 | NULL | ref | index_code | index_code | 161 | const | 1 | 100.00 | Using index condition |+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test_1 where code is not NULL;+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+| 1 | SIMPLE | test_1 | NULL | range | index_code | index_code | 161 | NULL | 7 | 100.00 | Using index condition |+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test_1 where code='dd';+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+| 1 | SIMPLE | test_1 | NULL | ref | index_code | index_code | 161 | const | 1 | 100.00 | Using index condition |+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test_1 where code like "dd%";+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+| 1 | SIMPLE | test_1 | NULL | range | index_code | index_code | 161 | NULL | 1 | 100.00 | Using index condition |+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)


总结


空值在处理sql语句时总是会带来很多不确定性,一不小心可能会导致性能下降。


列中使用NULL值很容易引发不受控制的事情发生,有时还会严重拖慢系统的性能。


例如:


聚合函数()中不会估计空值,这可能会导致结果不准确。


对含有NULL值的列进行统计计算,eg. count()、max()、min(),结果没有达到我们的期望值


空值会影响“distinct”、“group by”、“order by”等操作的行为,从而导致错误排序。


任务排序,分组,去重结果。


Null值需要ifnull()函数进行判断,使得程序代码更加复杂。


有时为了消除 NULL 带来的技术债务,我们需要在 SQL 中使用 IFNULL() 来确保结果可控,但这会使程序变得复杂。


Null 值需要额外的 1 个字节来存储行中的 Null 信息。


NULL值并占用了原有的字段空间存储,另外申请了一个字节去标注,这个字段添加了NULL约束(就像附加的标志位一样)。


由于上述缺点,不建议定义默认为 null 的列。我们建议在所有列上定义“not null”,并使用零数字和空字符串来替换相关的null数据类型。


根据以上缺点,我们并不推荐在列中设置 NULL 作为列的默认值,你可以使用 NOT NULL 消除默认设置,使用 0 或者''空字符串来代替 NULL。



译者丨广州
来源丨公众号:JavaGuide(ID:JavaGuide)

评论