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

相关文章推荐

SQL -- 八点心得

SQL 养成一个好习惯是一笔财富

数据分布不均衡导致性能问题

今晚(2016/04/14)遇到一个奇葩案例,虽然之前也遇到过非常多奇葩案例, 但是限于当时条件,无法收集案例,谁叫他奶奶的银行,证券,电信不允许泄密啊。还好今晚这个案例可以拿出来分享。 故事是这样...

PLSql -- 递归查询的另几种实现方式(函数/存储过程)

利用函数/存储过程/WITH实现递归查询,这是一个树结构,查询教师“胡明星”的所有主管及姓名:(无主管的教师也需要显示),显示(教师编号、教师名称、主管编号、主管名称)...

HINT 不生效?

今天有个徒弟找我看一条SQLSELECT /*+ use_hash(REGION,MPI) */ REGION.REGION_CODE, REGION.REGION_NAME, ...

实现数字电视共享改造工程详细解说

随着国家推进数字电视进程的加快,大中型宾馆酒店为了方便管理、节省费用,开始着手进行有线电视改造,增加自己的有线电视前端。简单来说,就是将一台电视一个机顶盒的收看方式改变为一套节目一个机顶盒。改造之后不...

dbms_monitor.session_trace_enable 跟踪某个会话

1、先查出你想跟踪的某个机器的session信息SID,SERIAL#select  SID,SERIAL#  from v$session where lower(terminal) like %y...
  • caok
  • caok
  • 2008-11-14 11:23
  • 2845

Oracle Trace dbms_monitor

Oracle 10g 增加的dbms_monitor方法,异步跟踪,可指定跟踪其他会话,获取对应的trace文件;除了可以设置trace以外还可以开启关闭对指定会话的统计信息;dbms_monitor...

XQuery表达式之路径表达式(Path Expression)

[如需转载,请注明出处]路径表达式其实就是XPath在XQuery中的应用。如果熟悉了XPath,那么对XQuery中的路径表达式也就了解了。一个路径表达式由一个或多个Step组成,Step由“/“或...

Oracle中“HINT”的30个用法

“HINT”的30个用法
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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