Oracle 9i为什么将空字符串视为NULL? [英] Why does Oracle 9i treat an empty string as NULL?

问题描述

我知道它确实将视为NULL，但这并不能告诉我为什么是这种情况.据我了解的SQL规范，''与NULL不同-一个是有效的数据，另一个表明缺少相同的信息.

I know that it does consider ' ' as NULL, but that doesn't do much to tell me why this is the case. As I understand the SQL specifications, ' ' is not the same as NULL -- one is a valid datum, and the other is indicating the absence of that same information.

请随意猜测，但请说明是否是这种情况.如果有Oracle的任何人可以对此发表评论，那就太好了！

Feel free to speculate, but please indicate if that's the case. If there's anyone from Oracle who can comment on it, that'd be fantastic!

推荐答案

我相信答案是Oracle非常非常古老.

I believe the answer is that Oracle is very, very old.

早在有SQL标准之前，Oracle就做出了设计决策，即VARCHAR/VARCHAR2列中的空字符串为NULL，并且只有一种NULL含义(有关系理论家可以区分从未提示的数据，存在答案但用户不知道的数据，没有答案的数据等，所有这些都构成了NULL的含义.

Back in the olden days before there was a SQL standard, Oracle made the design decision that empty strings in VARCHAR/VARCHAR2 columns were NULL and that there was only one sense of NULL (there are relational theorists that would differentiate between data that has never been prompted for, data where the answer exists but is not known by the user, data where there is no answer, etc. all of which constitute some sense of NULL).

当SQL标准问世并同意NULL和空字符串是不同的实体时，已经有Oracle用户使用了假定两者相等的代码.因此，Oracle基本上可以选择破坏现有代码，违反SQL标准或引入某种初始化参数的选择，这些参数可能会更改潜在大量查询的功能.在这三个选项中，违反SQL标准(IMHO)的破坏性最小.

By the time that the SQL standard came around and agreed that NULL and the empty string were distinct entities, there were already Oracle users that had code that assumed the two were equivalent. So Oracle was basically left with the options of breaking existing code, violating the SQL standard, or introducing some sort of initialization parameter that would change the functionality of potentially large number of queries. Violating the SQL standard (IMHO) was the least disruptive of these three options.

Oracle保留了VARCHAR数据类型在将来的发行版中进行更改以遵循SQL标准的可能性(这就是为什么每个人都在Oracle中使用VARCHAR2的原因，因为该数据类型的行为可以保证保持不变向前).

Oracle has left open the possibility that the VARCHAR data type would change in a future release to adhere to the SQL standard (which is why everyone uses VARCHAR2 in Oracle since that data type's behavior is guaranteed to remain the same going forward).

这篇关于Oracle 9i为什么将空字符串视为NULL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！