文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

美团面试:为什么MySQL不建议使用NULL作为列默认值?

2023-09-11 11:32

关注

今天给大家分享一道美团高频面试题,“为什么 MySQL 不建议使用 NULL 作为列默认值?”。

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

前言

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

介绍

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

Example

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,它俩也不一定相等。(像不像 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值。

(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(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.

虽然select NULL=NULL的结果为false,但是在我们使用distinctgroup byorder 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 中支持在含有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)

总结

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

例如:

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

参考

MySQL 一行记录是怎么存储的?

来源地址:https://blog.csdn.net/agonie201218/article/details/132231381

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯