SQL --NULL的注意事项

转载 2016年05月31日 21:57:27

问题描述

今天在跟进公司内部测试平台线上问题的时候,发现一个忽略已久的问题。

为了简化问题描述,将其进行了抽象。

有一张数据表qms_branch,里面包含了一批形式如下所示的数据:

id name types
1 dashboard_trunk dashboard
2 monkey_trunk monkey
3 dashboard_projects_10_9_9 dashboard
4 performance_trunk
5 performance_projects_10_9_8 per formance

在系统的某个页面中,需要展示出所有dashboard类型以外的分支,于是就采用如下方式进行查询(Rails)。

branches = Qms::Branch.where("types!='dashboard'")

这个方式有问题么?

之前我是觉得没什么问题。但是在代码上线后,实际使用时发现部分分支没有加载出来,这就包括了performance_trunk分支。

然后就是问题定位,到MySQL的控制台采用SQL语句进行查询:

SELECT * FROM qms_branch WHERE types != 'dashboard'

发现在查询结果中的确没有包含performance_trunk分支。

这是什么原因呢?为什么在第4条数据中,types属性的值明明就不是dashboard,但是采用types!=’dashboard’就无法查询得到结果呢?

原因追溯

查看数据表qms_branch的结构,看到types字段的属性为:DEFAULT NULL。
经过查询资料,在w3schools上找到了答案。

NULL is used as a placeholder for unknown or inapplicable values, it is treated differently from other values.

It is not possible to test for NULL values with comparison operators, such as =, <, or <>. We will have to use the IS NULL and IS NOT NULL operators instead.

也就是说,在SQL中,NULL并不能采用!=与数值进行比较,若要进行比较,我们只能采用IS NULL或IS NOT NULL。

于是,我们将SQL语句改为如下形式:

SELECT * FROM qms_branch WHERE types IS NULL or types != 'dashboard'

再次查询时,结果集就包含performance_trunk分支了。

问题延伸

通过上面例子,我们知道在对NULL进行判断处理时,只能采用IS NULL或IS NOT NULL,而不能采用=, <, <>, !=这些操作符。

那除此之外,还有别的可能存在的坑么?

再看一个例子:

有一张数据表table_foo,其中有一个字段value_field,我们想从这张表中筛选出所有value_field为’value1’,’value2’或NULL的记录。

那么,我们采用IN操作符,通过如下SQL语句进行查询。

SELECT * FROM table_foo WHERE value_field IN ('value1', 'value2', NULL)

这会存在问题么?我们并没有采用=, <, <>, !=对NULL进行比较哦。

答案是同样存在问题!

因为在SQL中,IN语句会被转换为多个=语句。例如,上面例子中的SQL在执行时就会被转换为如下SQL语句:

SELECT * FROM table_foo WHERE value_field = 'value1' OR value_field = 'value2' OR value_field = NULL

而这个时候,执行value_field = NULL时就会出现问题了。

正确的做法应该是将NULL相关的判断独立出来,如下SQL才是正确的写法。

SELECT * FROM table_foo WHERE value_field IN ('value1', 'value2') OR value_field IS NULL

安装SQLserver的注意事项

安装的时候看到很多服务默认自动启动,想起任务管理器里很多服务关不掉,就索性把这些服务都设置成手动启动了,结果。。。装好后连接补上数据库了,我猜是服务没开启,网上找了很多,最后发现只要开一个SQL Se...
  • Ehcoing
  • Ehcoing
  • 2016年03月23日 15:08
  • 401

MYSQL查询SQL的注意事项和一些技巧总结

在编写查询SQL的时候,有一些技巧可以提升查询性能,总结如下: not exists 代替 not in 尽量避免not in (子查询)的查询,如果是not in (列表)倒是可以接受的,因为no...
  • KuaiLeShiFu
  • KuaiLeShiFu
  • 2015年11月01日 14:41
  • 1057

SQL语句注意的东西

SQL不太熟悉,写的时候总是会有这样那样的问题,先从点滴做起,记录一下遇到的小错误以及解决的办法,积累多了,错误也就少了。          SQL(Structed-Query-Language)...
  • wangjinyang_123
  • wangjinyang_123
  • 2015年07月30日 10:40
  • 323

IS NULL和=NULL的区别(SQL)

IS NULL是判断某个字段是否为空,为空并不等价于为空字符串或为数字0;  而 =NULL 是判断某个值是否等于 NULL,NULL = NULL和NULL...
  • whuqin
  • whuqin
  • 2011年07月13日 20:22
  • 6913

安装SQL Server 2008注意事项

安装SQL Server 2008,如果安装了VS2008 SP1的话 , 必需要安装  汉化补丁,不然只能用英文的了-_-!!  Visual Studio 2008 SP1  官网下载地址 ...
  • ou832339
  • ou832339
  • 2014年03月10日 20:33
  • 730

SQL Server 2012 安装杂谈(安装注意事项)

SQL Server 2012 安装杂谈   回想一下,距离上次去微软培训SQL SERVER 2012已经有一段时间啦,从讲师打开2012的一瞬间,其实我已经开始想要跃跃欲试这...
  • lzqinfen
  • lzqinfen
  • 2014年08月20日 16:20
  • 1884

深入详解SQL中的Null

NULL 在计算机和编程世界中表示的是未知,不确定。虽然中文翻译为 “空”, 但此空(null)非彼空(empty)。 Null表示的是一种未知状态,未来状态,比如小明兜里有多少钱我不清楚,但也不能肯...
  • renfufei
  • renfufei
  • 2015年03月18日 20:10
  • 4734

Sql 中 不等于'<>'与 NULL

在写SQL 条件语句是经常用到 不等于‘’的sh
  • kai161
  • kai161
  • 2014年09月02日 10:56
  • 20948

sql中对于null的处理方法

1、缺省处理 Oracle在Order by 时缺省认为null是最大值,所以如果是ASC升序则排在最后,DESC降序则排在最前   2、使用nvl函数 nvl函数可以将输入参数为空时转换...
  • ding273371187
  • ding273371187
  • 2014年11月06日 00:06
  • 883

SQL NOT IN NULL的问题

转自http://blog.csdn.net/startexcel/article/details/5764562 在sql查询中,有时候会遇到如下情况: 两个表table1(10万条...
  • peng_bin1989
  • peng_bin1989
  • 2014年03月18日 14:47
  • 3759
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL --NULL的注意事项
举报原因:
原因补充:

(最多只允许输入30个字)