MySQL null与not null和null与空值''''的区别详解

相信很多用了MySQL很久的人，对这两个字段属性的概念还不是很清楚，一般会有以下疑问：

  • 我字段类型是not null，为什么我可以插入空值
  • 为毛not null的效率比null高
  • 判断字段不为空的时候，到底要 select * from table where column <> '' 还是要用 select * from table wherecolumn is not null 呢。

带着上面几个疑问，我们来深入研究一下null 和 not null 到底有什么不一样。
首先，我们要搞清楚“空值” 和 “NULL” 的概念：

  • 空值是不占用空间的
  • mysql中的NULL其实是占用空间的，下面是来自于MYSQL官方的解释：

“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

打个比方来说，你有一个杯子，空值代表杯子是真空的，NULL代表杯子中装满了空气，虽然杯子看起来都是空的，但是区别是很大的。

搞清楚“空值”和“NULL”的概念之后，问题基本就明了了，我们搞个例子测试一下：

CREATE TABLE `test` (
 `col1` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
 `col2` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL
) ENGINE = MYISAM ;

插入数据：

INSERT INTO `test` VALUES (null,1);

mysql发生错误：

#1048 - Column 'col1' cannot be null

再来一条

INSERT INTO `test` VALUES ('',1);

成功插入。

可见，NOT NULL 的字段是不能插入“NULL”的，只能插入“空值”，上面的问题1也就有答案了。

对于问题2，上面我们已经说过了，NULL 其实并不是空值，而是要占用空间，所以mysql在进行比较的时候，NULL 会参与字段比较，所以对效率有一部分影响。
而且B树索引时不会存储NULL值的，所以如果索引的字段可以为NULL，索引的效率会下降很多。

我们再向test的表中插入几条数据：

INSERT INTO `test` VALUES ('', NULL);
INSERT INTO `test` VALUES ('1', '2');

现在表中数据：

现在根据需求，我要统计test表中col1不为空的所有数据，我是该用“<> ''” 还是 “IS NOT NULL” 呢，让我们来看一下结果的区别。

SELECT * FROM `test` WHERE col1 IS NOT NULL


SELECT * FROM `test` WHERE col1 <> ''

可以看到，结果迥然不同，所以我们一定要根据业务需求，搞清楚到底是要用那种搜索条件。

MYSQL建议列属性尽量为NOT NULL

长度验证：注意空值的''之间是没有空格的。

mysql> select length(''),length(null),length(' ');

+------------+--------------+--------------+

| length('') | length(null) | length(' ') |

+------------+--------------+--------------+

| 0 | NULL | 2 |

+------------+--------------+--------------+

注意事项：

  • 在进行count()统计某列的记录数的时候，如果采用的NULL值，系统会自动忽略掉，但是空值是会进行统计到其中的。
  • 判断NULL 用IS NULL 或者 IS NOT NULL, SQL语句函数中可以使用ifnull()函数来进行处理，判断空字符用=''或者 <>''来进行处理
  • 对于MySQL特殊的注意事项，对于timestamp数据类型，如果往这个数据类型插入的列插入NULL值，则出现的值是当前系统时间。插入空值，则会出现 0000-00-00 00:00:00
  • 对于空值的判断到底是使用is null 还是='' 要根据实际业务来进行区分。

到此这篇关于MySQL null与not null和null与空值''''的区别详解的文章就介绍到这了,更多相关MySQL null与not null 内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL查询空字段或非空字段(is null和not null)

    现在我们先来把test表中的一条记录的birth字段设置为空. mysql> update test set t_birth=null where t_id=1; Query OK, 1 row affected (0.02 sec) Rows matched: 1  Changed: 1  Warnings: 0 OK,执行成功! 设置一个字段值为空时的语法为:set <字段名>=NULL 说明一下,这里没有大小写的区分,可以是null,也可以是NULL. 下面看看结果: mysql&

  • MySQL中可为空的字段设置为NULL还是NOT NULL

    MySQL中可为空的字段设置为NULL还是NOT NULL

    经常用mysql的人可能会遇到下面几种情况: 1.我字段类型是not null,为什么我可以插入空值 2.为什么not null的效率比null高 3.判断字段不为空的时候,到底要用 select * from table where column <> '' 还是要用 select * from table where column is not null 带着上面几个疑问,我们来简单的研究一下null 和 not null 到底有什么不一样,他们之间的区别是什么以及各自的效率问题. 首先,

  • MySQL中建表时可空（NULL）和非空（NOT NULL）的用法详解

    对于MySQL的一些个规范,某些公司建表规范中有一项要求是所有字段非空,意味着没有值的时候存储一个默认值.其实所有字段非空这么说应该是绝对了,应该说是尽可能非空,某些情况下不可能给出一个默认值. 那么这条要求,是基于哪些考虑因素,存储空间?相关增删查改操作的性能?亦或是其他考虑?该理论到底有没有道理或者可行性,本文就个人的理解,做一个粗浅的分析. 1,基于存储的考虑 这里对存储的分析要清楚MySQL数据行的存储格式,这里直接从这篇文章白嫖一部分结论,文章里分析的非常清楚(其实也是参考<MySQL

  • mysql not in、left join、IS NULL、NOT EXISTS 效率问题记录

    NOT IN.JOIN.IS NULL.NOT EXISTS效率对比 语句一:select count(*) from A where A.a not in (select a from B) 语句二:select count(*) from A left join B on A.a = B.a where B.a is null 语句三:select count(*) from A where not exists (select a from B where A.a = B.a) 知道以上三

  • MySQL中NOT IN填坑之列为null的问题解决

    前一段时间在公司做一个小功能的时候,统计一下某种情况下有多少条数据,然后修改的问题,当时感觉很简单,写了一个如下的 SQL: SELECT COUNT(*) FROM t1 where tl.c1 not IN (SELECT t2.c1 FROM t2); 预期的结果是:有多少条数据在 t1 中,同时不在 t2 中,结果为:0,也就是 t1 中数据都在 t2 中,但是很容易就发现某些数据在 t1 中不在 t2 中,所以就感觉很奇怪,这个 SQL 看着也没问题啊.经过一番查询原来是因为 t2 的

  • MySQL null与not null和null与空值''''的区别详解

    相信很多用了MySQL很久的人,对这两个字段属性的概念还不是很清楚,一般会有以下疑问: 我字段类型是not null,为什么我可以插入空值 为毛not null的效率比null高 判断字段不为空的时候,到底要 select * from table where column <> '' 还是要用 select * from table wherecolumn is not null 呢. 带着上面几个疑问,我们来深入研究一下null 和 not null 到底有什么不一样. 首先,我们要搞清楚

  • MySQL中B树索引和B+树索引的区别详解

    MySQL中B树索引和B+树索引的区别详解

    目录 1.多路搜索树 2.B树-多路平衡搜索树 3.B树索引 4.B+树索引 总结 如果用树作为索引的数据结构,每查找一次数据就会从磁盘中读取树的一个节点,也就是一页,而二叉树的每个节点只存储一条数据,并不能填满一页的存储空间,那多余的存储空间岂不是要浪费了?为了解决二叉平衡搜索树的这个弊端,我们应该寻找一种单个节点可以存储更多数据的数据结构,也就是多路搜索树. 1. 多路搜索树 1.完全二叉树高度:O(log2N),其中2为对数,树每层的节点数: 2.完全M路搜索树的高度:O(logmN),其

  • IOS 基础之nil，NULL，NSNULL区别详解

    IOS 基础之nil,NULL,NSNULL区别详解 ① nil:一般赋值给空对象. ② NULL:NULL 是一个通用指针(泛型指针). 一般赋值给 nil 之外的其他空值.如SEL等. ③ NSNULL:[NSNull null] 是一个对象,他用在不能使用 nil 的场合. 因为在 NSArray 和 NSDictionary 中 nil 有特殊的含义(表示列表结束),所以不能在集合中放入 nil 值.如要确实需要存储一个表示"什么都没有"的值,可以使用 NSNull 类.NSN

  • Python中的None与 NULL(即空字符)的区别详解

    1.首先要了解Python的对象的概念: Python中,万物皆对象,所有的操作都是针对对象的,那什么是对象,5是一个int对象,'oblong'是一个str对象,异常也是一个对象,抽象一点是,人,猫,够也是一个对象 那对于一个对象,它就有包括两方面的特征:  属性:去描述它的特征  方法: 它所具有的行为 所以,对象=属性+方法 (其实方法也是一种属性,一种区别于数据属性的可调用属性 把具有相同属性和方法的对象就可以归为一类,即Classl.类就好比是一张蓝图,使用一个类可以创建多个对象实例

  • Oracle10个分区和Mysql分区区别详解

    Oracle10g分区常用的是:range(范围分区).list(列表分区).hash(哈希分区).range-hash(范围-哈希分区).range-list(列表-复合分区). Range分区:Range分区是应用范围比较广的表分区方式,它是以列的值的范围来做为分区的划分条件,将记录存放到列值所在的range分区中. 如按照时间划分,2010年1月的数据放到a分区,2月的数据放到b分区,在创建的时候,需要指定基于的列,以及分区的范围值. 在按时间分区时,如果某些记录暂无法预测范围,可以创建m

  • MySQL中Decimal类型和Float Double的区别(详解)

    MySQL中存在float,double等非标准数据类型,也有decimal这种标准数据类型. 其区别在于,float,double等非标准类型,在DB中保存的是近似值,而Decimal则以字符串的形式保存数值. float,double类型是可以存浮点数(即小数类型),但是float有个坏处,当你给定的数据是整数的时候,那么它就以整数给你处理.这样我们在存取货币值的时候自然遇到问题,我的default值为:0.00而实际存储是0,同样我存取货币为12.00,实际存储是12. 幸好mysql提供

  • MySQL 行锁和表锁的含义及区别详解

    一.前言 对于行锁和表锁的含义区别,在面试中应该是高频出现的,我们应该对MySQL中的锁有一个系统的认识,更详细的需要自行查阅资料,本篇为概括性的总结回答. MySQL常用引擎有MyISAM和InnoDB,而InnoDB是mysql默认的引擎.MyISAM不支持行锁,而InnoDB支持行锁和表锁. 相对其他数据库而言,MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制. MySQL大致可归纳为以下3种锁: 表级锁:开销小,加锁快:不会出现死锁:锁定粒度大,发生锁冲突的概率

  • MySql分表、分库、分片和分区知识深入详解

    MySql分表、分库、分片和分区知识深入详解

    一.前言 数据库的数据量达到一定程度之后,为避免带来系统性能上的瓶颈.需要进行数据的处理,采用的手段是分区.分片.分库.分表. 二.分片(类似分库) 分片是把数据库横向扩展(Scale Out)到多个物理节点上的一种有效的方式,其主要目的是为突破单节点数据库服务器的 I/O 能力限制,解决数据库扩展性问题.Shard这个词的意思是"碎片".如果将一个数据库当作一块大玻璃,将这块玻璃打碎,那么每一小块都称为数据库的碎片(DatabaseShard).将整个数据库打碎的过程就叫做分片,可以

  • MySQL中in和exists区别详解

    MySQL中in和exists区别详解

    一.提前准备 为了大家学习方便,我在这里面建立两张表并为其添加一些数据. 一张水果表,一张供应商表. 水果表 fruits表 f_id f_name f_price a1 apple 5 a2 appricot 2 b1 blackberry 10 b2 berry 8 c1 cocount 9 供应商表 suppliers表 s_id s_name 101 天虹 102 沃尔玛 103 家乐福 104 华润万家 我们将用这两张表做演示. 二.什么是exists exists关键字后面的参数是一

  • mysql触发器实时检测一条语句进行备份删除思路详解

    mysql触发器实时检测一条语句进行备份删除思路详解

    问题描述:用户有一个这样一个需求,在一张表里会不时出现 "违规" 字样的字段,需要在出现这个字段的时候,把整行的数据删掉.这是个采集任务,如果发现有"违规"字样的数据,会整点或者什么时间进行统一上报,也无法对源头进行控制让这种数据不生成. 现在需要实现以下需求: 1.实时检测这条数据的产生,发现后删除 2.在删除之前作备份这条数据 解决思路: 需要明确解决思路, 1.首先是如何实时探测删除?询问开发,这条数据的生成方式为insert,就可以做一个当表做插入的时候,然

随机推荐