MySQL字段不推荐使用 Null 的理由

最新推荐文章于 2023-07-24 18:12:21 发布

georgesnoopy

最新推荐文章于 2023-07-24 18:12:21 发布

阅读量2k

点赞数 2

分类专栏：数据库

原文链接：https://www.techug.com/post/you-should-not-use-null-in-mysql.html

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

MySQL 一千个不用 Null 的理由

null貌似在哪里都是个头疼的问题，比如Java里让人头疼的 NullPointerException，为了避免猝不及防的空指针异常，千百年来程序猿们不得不在代码里小心翼翼的各种 if 判断，麻烦而又臃肿，为此java8 引入了Optional来避免这一问题。

下面咱们要聊的是MySQL里的null，在大量的MySQL优化文章和书籍里都提到了字段尽可能用NOT NULL，而不是NULL，除非特殊情况。但却都只给结论不说明原因。

一般，很多的字段使用null的原因大概可以总结为如下三个；

NULL是创建数据表时默认的，初级或不知情的或怕麻烦的程序员不会注意这点。
很多人员都以为not null需要更多空间，其实这不是重点。
重点是很多程序员觉得NULL在开发中不用去判断插入数据，写sql语句的时候更方便快捷。

Mysql官方文档对于null字段的一个说明。

NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.

《高性能mysql第二版》中也提到，Mysql难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要mysql内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致MYISAM中固定大小的索引变成可变大小的索引。

也就是说，不推荐使用可为null的字段是有根据的，并不是空穴来风。

null列改为非null列可能导致页分裂

NULL值到非NULL的更新无法做到原地更新，更容易发生索引分裂，从而影响性能。

Mysql的数据的存储单位都是存放在列中的，索引的组织也是排序按照从小到达排列的。

如果page2中的那一列原来为null，正好在page2放下，但是当把这一列改成非null后，page2就放不下了，那么就会导致page2的分裂。如果这个时候page3有空间还好，如果往后的所有page都没有空间了，那么就会造成后面所有的page都会分裂，更新性能受到影响。

但是注意：但把NULL列改为NOT NULL带来的性能提示很小，除非确定它带来了问题，否则不要把它当成优先的优化措施，最重要的是使用的列的类型的适当性。

timestamp类型的列为null容易出问题

NULL值在timestamp类型下容易出问题，特别是没有启用参数explicit_defaults_for_timestamp

查询条件中有null列需要格外注意，容易出错

mysql中，任何值和null的比较，都返回null，而不是true或者false。

包括null=null结果都是null，不是true

所以一旦在查询条件使用了null，而不是使用is null或者is not null将出现错误。不会返回任何值。

table3表中的数据如下：

如下4条sql语句不会返回空集合：

原因也就是mysql中，任何值和null比较后都是null，而不是true或者false，所有对于字段是null的，不会返回。

同样的，大于小于等不等号操作也是一样的。

select * from table1 where age>-1。age=null的行不会返回。

但是对于in的列表中有null的，还行效果就相当于忽略null

如果只是如上这些简单语句，可能知道mysql中null和任何值比较后都是null这个规则，那么其实都是可以避免的，但是就怕条件是动态的，如有子查询：

那么如果table1和table2的user_name都允许为null，这个sql是查不出table2中user_name为null的那些记录的。

同理

返回结果中，不会包含user_name=null的记录。注意，如果user_name上没有索引，那么其实就是因为’aaa’=null返回null，所以不会找回user_name=null的行，但是即使user_name上有索引，那么辅助索引树上，不会存字段为null(想想如果存了，没法比较大小)，所以也不会找回user_name=null的行

总结：

由于mysql中，null值和任何值的比较，都会返回null，导致条件中有null的时候，返回结果和想想中的可能就不一样了。