Oracle 将空字符串视为 Java / JPA 程序员的 NULL 问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5510509/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle treating empty string as NULL problem for a Java / JPA programmer
提问by bertie
How do you handle this situation where Oracle stores the empty string as a null in the database ?
您如何处理 Oracle 将空字符串作为空值存储在数据库中的这种情况？
I would like it to be stored as an empty string as it is not as NULL, since issuing the query would be easier.
我希望将它存储为空字符串，因为它不是 NULL，因为发出查询会更容易。
Something like this would select the empty string and non-empty string, but not the null values
这样的事情会选择空字符串和非空字符串，但不是空值
select * from mytable where myfield like '%';
if i would like to select also the null values (which should be originally empty string), i would have to select like this :
如果我还想选择空值（最初应该是空字符串），我必须像这样选择：
select * from mytable where myfield like '%' or myfield is null;
i would love to skip doing or myfield is nullall the time later in my sql statements
我很想or myfield is null在我的 sql 语句中跳过所有的时间
The current solution i have in mind is to take care of this in the application level, for example, in the entity, i initialize all my String field default value to a space, for example :
我想到的当前解决方案是在应用程序级别处理此问题，例如，在实体中，我将所有 String 字段默认值初始化为一个空格，例如：
@Entity
public class MyEntity {
private String name = " ";
public void setName(String name) {
if (isEmptyString(name)) {
name = " ";
}
}
...
}
Or perhaps, i can make use of a new type still unknown to me from Oracle 11g that can keep empty string as it is without changing it to null value ?
或者，我可以使用 Oracle 11g 中我仍然不知道的新类型，它可以保持空字符串原样而不将其更改为空值？
Thank you !
谢谢 ！
回答by Tommi
Yup, that's the way Oracle functions. Empty strings are treated as nulls.
是的，这就是 Oracle 的运作方式。空字符串被视为空值。
You can of course "fix" this on application level - for example by storing " "values as you suggested - but first consider, what exactly is the difference with your "empty string" values compared to NULLvalues? Why do you need to treat them differently? I used to run into this dilemma, too, but usually found out that there are very few cases where I really need to tell the difference.
您当然可以在应用程序级别“修复”此问题 - 例如通过" "按照您的建议存储值 - 但首先考虑，与值相比，您的“空字符串”值究竟有什么区别NULL？为什么你需要区别对待它们？我曾经也遇到过这种困境，但通常会发现很少有我真正需要区分的情况。
回答by Mike
It′s not only the selection with special where condition but also the treating of Java String Objects. If you have a String a="" you can call its length method and get 0. If you have a String a=null you get a nullpointer exception when calling length. So working with an oracle db forces you to always check if your string is null before checking length :(
它不仅是具有特殊where条件的选择，而且是Java字符串对象的处理。如果你有一个 String a="" 你可以调用它的 length 方法并得到 0。如果你有一个 String a=null 你在调用 length 时会得到一个空指针异常。因此，使用 oracle db 迫使您在检查长度之前始终检查您的字符串是否为空 :(
回答by a_horse_with_no_name
No, there is no way to treat empty strings as empty strings. Oracle always treats a string of length zero as a NULL value.
不，没有办法将空字符串视为空字符串。Oracle 始终将长度为零的字符串视为 NULL 值。
回答by Dead Programmer
try
尝试
create index idx_myfield on mytable(nvl(myfield,-1));
select * from mytable where nvl(myfield,-1)=-1;
回答by tbone
Its early for me, but isn't
对我来说还早，但不是
select * from mytable where myfield like '%' or myfield is null
the same as
一样
select * from mytable
So, Oracle simplifies your life! ;)
因此，Oracle 简化了您的生活！;)
回答by The World is not Perfect
Beleive Oracle is optimizing the database by converting the empty string as NULL. First an empty string still may be need to be stored explicitly in the database storage at data block level (*1). Storing as NULL may reduce data storage footprint. Second if any indexes were defined on the column then the NULL values are not included in index thereby reducing index storage footprint. (*2)
Beleive Oracle 正在通过将空字符串转换为 NULL 来优化数据库。首先，可能仍需要在数据块级别 (*1) 的数据库存储中显式存储一个空字符串。存储为 NULL 可以减少数据存储占用空间。其次，如果在列上定义了任何索引，则索引中不包含 NULL 值，从而减少索引存储空间。(*2)
From a design and development standpoint unless the empty space really changes the semantics of the data from a business perspective storing as NULL should be fine.
从设计和开发的角度来看，除非空白空间真的从业务角度改变了数据的语义，否则存储为 NULL 应该没问题。
Adding code at framework level ( or parent class ) as suggested above would eliminate the need to type it out at all children classes & objects - that is what Object Oriented and even basic programming strive to do.
如上所述在框架级别（或父类）添加代码将消除在所有子类和对象中输入代码的需要——这就是面向对象甚至基本编程努力做到的。
If my code is performing best because my database storage is optimized, then I should be happy. It sucks if my code performance tanks in production just because I wanted to save a little typing or failed to do masterful design / development?
如果我的代码由于我的数据库存储优化而表现最佳，那么我应该很高兴。如果我的代码性能仅仅因为我想节省一点打字或未能进行熟练的设计/开发而在生产中表现不佳，那会很糟糕吗？
I would be happy that Oracle is optimizing it for me under the covers.
我很高兴 Oracle 在幕后为我优化它。
All about Oracle NULL:
所有关于 Oracle NULL：
NULL is a special value. NULL does not equate to anything including itself i.e NULL is not equal to NULL
NULL 是一个特殊值。NULL 不等于任何东西，包括它自己，即 NULL 不等于 NULL
