为什么 Oracle 9i 将空字符串视为 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 标准 (恕我直言) 是这三个选项中破坏性最小的.
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).
相关文章