Oracle:NULL与EMPTY字符串之间的区别 [英] Oracle:Difference between NULL and EMPTY string

问题描述

由于Oracle行为，我的查询没有返回任何值.

I am in a situation where my query is not returning any values due to oracle behaviour.

问题是这样的: Oracle认为空字符串为<插入数据时为strong> NULL ，但选择返回数据时不这样做.

Problem is this:Oracle considers EMPTY STRING as NULL when INSERTING DATA but not when SELECTING BACK the data.

这不是> Oracle为何重复的副本9i将空字符串视为NULL?，因为在这里我不是在问这个问题的原因，我很清楚原因，是在寻求解决此问题的方法.

This is not a duplicate of Why does Oracle 9i treat an empty string as NULL? because here i am not asking for the reason to this problem,i am well aware of the reason,i am asking for a solution to this problem.

这是我的表结构

   CREATE TABLE TEST
    (
      ID          NUMBER not null,
      NAME VARCHAR2(255)
    )

在插入值时oracle将接受

when inserting the values oracle will accept

将值插入测试值(1，'');

INSERT INTO TEST values(1,'');

我发现oracle在内部将零长度的字符串转换为NULL 并存储

I found out that internally oracle converts Strings of Zero length to NULL and stores

但是我的查询

选择*从测试 姓名=输入;(输入='')

SELECT * FROM TEST WHERE NAME = INPUT;(INPUT='')

(输入是从前端传递的，有时会有空字符串)
不会返回任何结果
由于性能问题，我无法编写动态查询

(Input is passed from front end and will sometimes have empty string)
will not return any result
I can not write dynamic query due to performance issue

之前遇到过此问题的人请让我知道如何比较空字符串和NULL

Somebody who faced this issue before please let me know how do i compare EMPTY STRING with NULL

推荐答案

这是Oracle最令人讨厌的功能之一-在其他数据库产品中找不到.对于Oracle的所有其他其他优点，您将不得不忍受它-并且要准备好学习曲线不是很快.

This is one of the most annoying features of Oracle - not found in other DB products. You will have to put up with it, for all the other massive advantages of Oracle - and be prepared that the learning curve is not very quick.

要检查null是否相等，最好的方法是显式地编写您在做什么，而不是使用using头.例如:

To check for equality of nulls, the best approach is to write explicitly what you are doing, instead of using gimmicks. For example:

... where NAME = INPUT or (NAME IS NULL and INPUT IS NULL)

现在和以后，这将使您自己以及以后的其他人更容易调试，维护和修改代码.也有其他解决方案，但是将来它们可能会使其他人感到困惑.例如，出于某些原因，我将不使用此功能:

This will make it a lot easier for yourself, and for others after you, to debug, maintain, and modify the code, now and especially later. There are other solutions, too, but they may confuse others in the future; for example, this is something I wouldn't use (for several reasons):

... where NAME || 'z' = INPUT || 'z'

尽管显然用更少的输入即可达到相同的结果.

although it would obviously achieve the same result with less typing.

还有一件事情，在大多数情况下，您不应该在结果行中包括将NULL视为等于"的行-出于某种原因，该值为NULL，并且在大多数情况下，如果使两个NULL相等，则不预期的结果.

One more thing, in most cases you should NOT include in your results rows where you treat NULL as "equal" - the values are NULL for a reason, and in most cases if you make two NULL's equal, that is NOT the intended result.

这篇关于Oracle:NULL与EMPTY字符串之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！