首页 > 数据库 >面试官：为什么MySQL不建议使用NULL作为列默认值？（转）

面试官：为什么MySQL不建议使用NULL作为列默认值？（转）

时间：2023-08-14 09:56:59浏览次数：69  
标签：面试官 null zlm 0.00 NULL sec MySQL 默认值 select

原文：https://mp.weixin.qq.com/s/4aWKVvGTjG4Hiv2ULxQiAg

来源：微信公众号“Java充电社”

今天来分享一道美团高频面试题，5 分钟搞懂“为什么 MySQL 不建议使用 NULL 作为列默认值？”。

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

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

前言

Null is a special constraint of columns. The columns in table will be added null constrain if you do not define the column with “not null” key words explicitly when creating the table.Many programmers like to define columns by default because of the conveniences(reducing the judgement code of nullibility) what consequently cause some uncertainty of query and poor performance of database.

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

介绍

Null is null means it is not anything at all,we cannot think of null is equal to ‘’ and they are totally different. MySQL provides three operators to handle null value:“IS NULL”,“IS NOT NULL”,"<=>" and a function ifnull(). IS NULL: It returns true,if the column value is null. IS NOT NULL: It returns true,if the columns value is not null. <=>: It’s a compare operator similar with “=” but not the same.It returns true even for the two null values. (eg. null <=> null is legal) IFNULL(): Specify two input parameters,if the first is null value then returns the second one. It’s similar with Oracle’s NVL() function.

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

  • IS NULL
  • IS NOT NULL
  • <=> 太空船操作符,这个操作符很像=,select NULL<=>NULL可以返回true,但是select NULL=NULL返回false.
  • IFNULL 一个函数.怎么使用自己查吧…反正我会了

Example

Null never returns true when comparing with any other values except null with “<=>”.

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 means “a missing and unknown value”.Let’s see details below.

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.

It truns null as a result if any expression contains null value.

任何有返回值的表达式中有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.

It’s diffrent when using 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.

When using distinct,group by,order by,all null values are considered as the same value.

虽然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 supports to use index on column which contains null value(what’s different from 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: sbtest1
Create 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=utf8
1 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 value always leads to many uncertainties when disposing sql statement.It may cause bad performance accidentally.

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

例如:

null value will not be estimated in aggregate function() which may cause inaccurate results.

对含有 NULL 值的列进行统计计算,eg. count(),max(),min(),结果并不符合我们的期望值.

null value will influence the behavior of the operations such as “distinct”,“group by”,“order by” which causes wrong sort.

干扰排序，分组,去重结果.

null value needs ifnull() function to do judgement which makes the program code more complex.

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

null value needs a extra 1 byte to store the null information in the rows.

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

As these above drawbacks,it’s not recommended to define columns with default null. We recommand to define “not null” on all columns and use zero number & vacant string to substitute relevant data type of null.

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

标签：面试官,null,zlm,0.00,NULL,sec,MySQL,默认值,select
From： https://www.cnblogs.com/ajianbeyourself/p/17627826.html

相关文章

  • 开源数据库Mysql_DBA运维实战 （修改root密码）
    本文以windows为例为大家详细介绍下MySQL修改root密码的4种方法，大家可以可以根据的自己的情况自由选择，希望对大家有所帮助方法1：用SETPASSWORD命令首先登录MySQL。格式：mysql>setpasswordfor用户名@localhost=password('新密码');例子：mysql>setpasswordforroot@localh......
  • nacos 使用 MySQL8.x 出现 Nacos No DataSource set异常解决
    http://www.taodudu.cc/news/show-6345954.html?action=onClick nacos使用MySQL8.x出现NacosNoDataSourceset异常解决注：当前版本nacos2.1.0 1、nacos默认室mysql5.7当链接mysql8.x，会出现异常【NacosNoDataSourceset】解决：在nacos安装目录下新建plugins/mysql文......
  • mysql添加非 root 级别用户
    进入mysql容器内部dockerexec-itCOMMAND/bash输入密码登录MySQL回车输入密码passwordroot@94c490e7da78:/#mysql-pEnterpassword:添加数据库先使用root用户添加数据库：--进入sql服务后首先查看有哪些数据库showdatabases;--若没有新建一个CREATE......
  • mysql数据库中有表，但是抛异常，Table 'test.WORRK_NODE' donesn't exist
    原因：表名是大写的，linux上的msyql默认区分大小写问题，本地的mysql不会出现这个问题解决一：修改sql语句，变成小写库名，表名方法二：把原来表删了，重新建表，建立表时指定字符集utf8_general_ci，该字符集对大小写不敏感 解决三：开启忽略大小写，需要修改/etc/my.cnf配置文件 注意：这个配......
  • MySQL 的开窗函数
    开窗函数（WindowFunction）提供了行集之间的计算能力，在现代的主流关系型数据库中，基本都提供了相似的功能，这些功能在一些业务开发的过程中很有用，本文将简要介绍这些常用的开窗函数ROW_NUMBER()ROW_NUMBER()函数的使用语法如下：ROW_NUMBER()OVER([partition_definition][order_......
  • Found a misplaced row，分区表修复MYSQL
    在MySQL中，分区表的数据被分隔到不同的表空间中，但仍被视为单个表。在处理大量数据集的某些情况下，分区可能是一种有用的方法。在分区表中删除庞大的数据集可以加快速度，但是如果处理不当，它可能会把数据放在表的不对应的位置中。测试环境：mysql8.0.30建表master[localhost:22031]{msa......
  • mysql在开启group_replication后，状态显示为RECOVERING，告警日志报错MY-013117、MY-0115
    问题描述：mysql在开启group_replication后，状态显示为RECOVERING，告警日志报错MY-013117、MY-011582、MY-011583，如下所示：数据库：MySQL8.0.27系统：rhel7.364位1、问题重现Slave02[(none)]>select*fromperformance_schema.replication_group_members;+-----------------------......
  • mysql在开启group_replication后，报错ERROR 3092，This member has more executed transa
    问题描述：mysql在开启group_replication后，报错ERROR3092，Thismemberhasmoreexecutedtransactionsthanthosepresentinthegroup，如下所示：数据库：MySQL8.0.27系统：rhel7.31、异常重现Slave01[(none)]>startgroup_replication;ERROR3092(HY000):Theserverisnotc......
  • mysql在安装group_replication插件时，报错"version libcrypto.so.10 not defined in fi
    问题描述：mysql在安装group_replication插件时，报错"versionlibcrypto.so.10notdefinedinfilelibcrypto.so"，如下所示：数据库：mysql8.0.27系统：rhel7.364位1、异常重现mysql>installplugingroup_replicationsoname'group_replication.so';ERROR1126(HY000):......
  • 一个mysql dba的成长之旅--第零章 绝处逢生：意外收到dba offer
    （本故事纯属虚构，如有雷同实属巧合）2018年的一个秋天的下午，江南理工大学图书馆一楼的宣讲会大厅人头攒动，充满了期待的氛围。这里正在举办一场国内知名互联网公司的宣讲会，吸引了众多毕业生前来倾听。小李身穿一套整洁的求职西装，手里拿着整齐的彩色简历，坐在室友旁边，全神贯注地聆听着台......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99