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

最新推荐文章于 2024-01-10 15:55:10 发布
最新推荐文章于 2024-01-10 15:55:10 发布
阅读量7.5w 收藏 1
点赞数 1
分类专栏： 数据库 mysql 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_37284798/article/details/132201876
版权

1 前言

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

2 介绍

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 代表一个不确定的值,就算是两个 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,但是在我们使用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 中支持在含有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)

3 总结

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

  1. 对含有 NULL 值的列进行统计计算,eg. count(),max(),min(),结果并不符合我们的期望值
  2. 干扰排序，分组,去重结果
  3. 有的时候为了消除NULL带来的技术债务,我们需要在 SQL 中使用IFNULL()来确保结果可控,但是这使程序变得复杂
  4. NULL值并是占用原有的字段空间存储,而是额外申请一个字节去标注,这个字段添加了NULL约束.(就像额外的标志位一样)

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

吴名氏.
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
解析MySQL设置当前时间为默认值的方法
09-10
在MySQL数据库中，设置当前时间为默认值是一种常见的需求，特别是在记录数据创建或更新时间时。本文将详细解析三种方法来实现这一目标，适用于那些需要在MySQL数据库中创建带有默认当前时间戳字段的开发者。 首先，...
为什么 MySQL 不建议使用 NULL 作为列默认值？
Java笔记虾
07-22 503
今天来分享一道美团高频面试题，5 分钟搞懂“为什么 MySQL 不建议使用 NULL 作为列默认值？”。对于这个问题，通常能听到的答案是使用了 NULL 值的列将会使索引失效,但是如果实际测试过一下,你就知道IS NULL会使用索引.所以上述说法有漏洞.着急的人拉到最下边看结论前言Null is a special constraint of columns. The columns in ta...
Mysql面试题分享二十四：为什么 MySQL 不建议使用 NULL 作为列默认值？
之乎者也·的博客
11-16 1011
MySQL不建议使用NULL作为列默认值的原因有几个。首先，NULL在SQL中表示“未知”，而不是“空”或“不存在”。当列被设置为NULL默认值时，它可能会导致查询和数据处理时的困惑和错误。其次，使用NULL默认值可能会导致查询和处理数据时的复杂性增加。在查询中，需要额外处理NULL值的情况，这可能会使查询逻辑变得复杂且容易出错。此外，对NULL值进行数学运算或连接操作时，结果可能不符合预期，需要额外处理或排除NULL值。另外，使用NULL默认值也会影响索引和性能。
为何MySQL不建议用NULL作为默认值
mr_zhangwz的博客
10-23 237
根据以上缺点,我们并不推荐在列中设置 NULL 作为列的默认值,你可以使用NOT NULL消除默认设置,使用0或者''空字符串来代替NULL。有的时候为了消除NULL带来的技术债务,我们需要在 SQL 中使用IFNULL()来确保结果可控,但是这使程序变得复杂.NULL值并是占用原有的字段空间存储,而是额外申请一个字节去标注,这个字段添加了NULL约束.(就像额外的标志位一样)对含有 NULL 值的列进行统计计算,eg. count(),max(),min(),结果并不符合我们的期望值.
美团面试：为什么MySQL不建议使用NULL作为列默认值？
热门推荐
LoveSummer
08-16 2万+
NULL值是一种对列的特殊约束,我们创建一个新列时，如果没有明确的使用关键字not null声明该数据列，Mysql会默认的为我们添加上NULL约束。有些开发人员在创建数据表时，由于懒惰直接使用 Mysql 的默认推荐设置。(即允许字段使用NULL值)。而这一陋习很容易在使用NULL的场景中得出不确定的查询结果以及引起数据库性能的下降。NULL并不意味着什么都没有,我们要注意NULL跟''(空值)是两个完全不一样的值。MySQL 中可以操作NULL值操作符主要有三个。IS NULL
为什么不建议给MySQL设置Null值？《死磕MySQL系列 十八》.doc
07-08
在本期中，我们将讨论为什么不建议给 MySQL 设置 Null 值，并分析 Null 值对数据统计的影响。 1. Null 值对统计的影响 在 MySQL 中，Null 值的存在可能会导致数据统计不准确。例如，在使用 count 函数时，如果存在...
mysql中datetime类型设置默认值方法
09-09
在MySQL 5.6及以上版本中，`CURRENT_TIMESTAMP`作为默认值是被允许的，但在某些旧版本中可能不被接受。 为了解决这个问题，你可以直接使用SQL语句来修改已有字段的默认值，如下所示： ```sql ALTER TABLE `ts_role...
MySQL中可为空的字段设置为NULL还是NOT NULL
12-16
例如，MySQL的MyISAM引擎会为每个`NULL`列分配一个位，用于记录该字段是否为空，这个位会被四舍五入到最近的字节。因此，即使`NULL`不包含任何数据，它仍然占用一定的存储资源。 在`NOT NULL`约束下，字段不允许...
mysql中将null值转换为0的语句
12-15
在MySQL数据库中，`NULL`值表示未知或者不存在的数据，它与数字0、空字符串等概念完全不同。在处理数据时，有时我们希望将`NULL`值转换为其他有意义的值，比如0。以下是对如何在MySQL中将`NULL`值转换为0的详细解释...
mysql不推荐列默认值设置为null到底是为什么
qq_30549099的博客
07-16 3476
MySQL不建议使用NULL作为列默认值到底为什么 通常能听到的答案是使用了NULL值的列将会使所以失效,但是如果实际测试过一下,你就知道IS NULL会使用索引.所以上述说法有漏洞. 但是毫无疑问,NULL是很特殊,很不合群的.既然NULL如此特殊，如此不合群,为什么它还会存在呢？我们什么时候需要使用NULL呢？ Preface Null is a special constraint of columns. The columns in table will be added null constra
为什么推荐mysql存在默认值，而不是null
weixin_52796198的博客
07-04 1097
在设计数据库模式时，如果某个字段预计需要存储值，可以使用默认值或者非空约束来避免NULL值的产生。使用默认值可以确保字段总是包含有效值，非空约束可以强制要求该字段不允许为空。这样可以简化查询逻辑，并提高数据库的性能和可靠性。如果某个字段确实需要存储未知或缺失值，可以考虑使用特殊的占位符值来代替NULL，例如空字符串(“”)或者特定的标识值。
mysql为什么尽量不要存null
m0_60721514的博客
03-21 703
在项目中优化mysql的时候字段尽量不要用NULL 值，使用-1或者其他特殊标识来替代，原因如下： 为NULL的列会使用更多的存储空间，在Mysql中也需要特殊处理 对Mysql来说更难优化，因为可为NULL的列会使索引统计和值比较都更复杂。 当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。 索引问题 如果计划在列上建索引，列尽量设计成可not null 关于Mysql中null列是否会走索引：MyS
为什么数据库中不建议使用用 Null
weixin_39585051的博客
08-18 1667
港真，Null 貌似在哪里都是个头疼的问题，比如 Java 里让人头疼的 NullPointerException，为了避免猝不及防的空指针异常，千百年来程序猿们不得不在代码里小心翼翼的各种 if 判断，麻烦而又臃肿，为此 java8 引入了 Optional 来避免这一问题。 下面咱们要聊的是 MySQL 里的 null，在大量的 MySQL 优化文章和书籍里都提到了字段尽可能用NOT NULL，而不是NULL，除非特殊情况。但却都只给结论不说明原因，犹如鸡汤不给勺子一样，让不少初学者对这个结论半信半疑
面试官：为什么mysql所有的字段都建议设置为not null,default?
qq_41046474的博客
08-22 1370
面试官：为什么mysql所有的字段都建议设置为not null,default?
揭秘MySQL中的“黑洞“：为什么你应避免默认使用NULL值？
最新发布
Java技术乐园
01-10 402
虽然NULL值在某些情况下是有用的，但在设计数据库和表结构时应该谨慎使用。为了避免潜在的问题和复杂性，建议避免默认使用NULL值，而是使用适当的默认值或强制要求提供所有字段的值。假设我们有一个名为"orders"的表，其中有一个"delivery_date"字段。如果我们允许delivery_date默认为NULL，那么每当我们插入一个新订单而没有指定交货日期时，它会自动设置为NULL。在MySQL中，NULL值是一种特殊的标记，表示数据缺失或未知。在MySQL中，NULL表示一个字段没有值或数据未知。
Mysql中创建表字段允许空值NULL和不允许空值NULL
爱吃Java的羊的博客
09-23 2900
alter table 表名 modify 字段名 字段类型 null;MODIFY功能是修改字段属性，只是alter功能下的一个功能模块.null表示对象计算中具有保留的值，用于指示指针不引用有效对象。空值表示值未知，空值一般表示数据未知、不适用或将在以后添加数据。为字段设置允许空值NULL和不允许空值NOT NULL。null表示对象的内容为空，即对象的内容是空白的。空值表示对象的内容无法确定。创建MySql数据表，
为何建议对Mysql库中将所有字段定义为NOT NULL并且提供默认值?
06-13
将所有字段定义为 NOT NULL 并提供默认值是为了确保数据的完整性和一致性。如果不定义 NOT NULL，那么在插入数据时，如果某些字段被忘记填写，就会出现 NULL 值。这会使得查询时需要对这些字段进行特殊处理，增加了...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

吴名氏.

你的鼓励是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值