Oracle不区分空字符串和空字符串？ [英] Oracle not distinguishing between nulls and empty strings?

问题描述

显然，oracle似乎不区分空字符串和null。例如。

 从TABLE_A中选择名称，其中id = 100; 
 ID NAME 
 100 null 
 
更新TABLE_A设置NAME =''其中id = 100; 
 SELECT  - > 
 ID NAME 
 100 null 
 
 SELECT length（NAME）FROM TABLE_A WHERE id = 100; 
 null 
  

我不能想到为什么Oracle会构建起来这样（在sqlplus中也这样做 - 我通过一个java接口访问，文章引用使用一个php客户端）。

你不会至少想区分0长度和未定义长度？这是一个已知的问题？为某些特定目的的故意行为？数据库理论中长期存在的争议？是什么赋予了？

（这是由Matt Solnit回答

解决方案

Oracle 非常非常古老。

回到 80年代是任何标准），他们认为这是一个好主意，然后给予 Oracle 存储其值，它是真的。

以下是 Oracle 如何存储数据（取自 文档 ）：

如果发生 NULL ，则数据类型不会存储在数据中，只有数据长度和数据本身。在具有值的两个列之间，它被存储为单个字节，意味着列具有长度 0 （实际上， 0xFF ）。 '

因此，要存储值'test '， Oracle 需要存储5个字节： 04 74 65 73 74 p>

但是，要存储空字符串和 NULL ， Oracle 只需要将数据长度设置为 0 。

如果您的数据要存储在 20 Mb 硬盘驱动器每个花费 5,000 $ 。

后来，当标准出现时，这不是一个好主意，但到那时，已经有很多很多代码依赖 NULL 和<$ c $ c>

$

为了解决这个问题，他们将 VARCHAR 重命名为 VARCHAR2 （不是任何标准的一部分），表示 VARCHAR2 将从不区分一个 NULL 和一个空字符串，并敦促每个人使用这个数据类型。

现在他们可能正在等待对于在 Oracle 数据库中使用 VARCHAR 的最后一个人。

Apparently oracle doesn't seem to distinguish between empty strings and nulls. E.g.

Select name from TABLE_A where id=100;
  ID   NAME
  100  null

Update TABLE_A set NAME='' where id=100;
SELECT -->
  ID   NAME
  100  null

SELECT length(NAME) FROM TABLE_A WHERE id=100;
  null

I can't think of any good reason why Oracle would be built to behave this way (does it do this in sqlplus as well?-I'm accessing through a java interface, the article referenced used a php client).

Wouldn't you at least want to distinguish 0 length from undefined length? Is this a known issue? Intentional behavior for some specific purpose? A long-running dispute in database theory? What gives?

(This was prompted by Matt Solnit's answer to this question.)

解决方案

Oracle is very very very old.

Back in 80's when it was developed (and before there were any standards) they thought is was a good idea, and given then way Oracle stores its values, it really was.

Here's how Oracle stores data (taken from the documentation):

No datatype is stored within the data, only the data length and the data itself.

If the NULL occurs between two columns with values, it's stored as a single byte meaning column has length 0 (actually, 0xFF). Trailing NULLs are not stored at all.

So to store the value 'test', Oracle needs to store 5 bytes: 04 74 65 73 74.

However, to store both an empty string and a NULL, Oracle just needs to set data length to 0.

Very smart if your data are to be stored on 20 Mb hard drives that cost 5,000$ each.

Later, when the standards appeared, it wasn't such a good idea anymore, but by that time there already were lots and lots of code relying on NULL and '' being the same thing.

Making VARCHAR to do such a distinction will break tons of code.

To fix it, they renamed VARCHAR to VARCHAR2 (which is not a part of any standard), stated that VARCHAR2 will never distinguish between a NULL and an empty string and urged everybody to use this datatype instead.

Now they are probably waiting for the last person who used a VARCHAR in Oracle database to die.

这篇关于Oracle不区分空字符串和空字符串？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！