where 子句中忽略的空白字符

2021-12-30 00:00:00 oracle11g oracle oracle10g

我做了以下事情 -

    create table test (col char(10)); 
    insert into test values ('hello');
    select * from test where col = 'hello'

有人建议我上面不应该返回任何结果，因为 'col' 是 10 个字符，它会用空格填充，所以与 'hello' 比较不会返回结果.但我得到了结果.任何人都可以解释一下吗?我正在使用 11gR2

I have been suggested that the above should not return any result as 'col' is 10 chars, it will be right padded with blanks, so comparing with 'hello' will not return result. But I am getting the result. Can anyone please explain this? I am using 11gR2

推荐答案

查看 文字:

文本文字具有 CHAR 和 VARCHAR2 数据类型的属性:

Text literals have properties of both the CHAR and VARCHAR2 datatypes:

  • 在表达式和条件中，Oracle 将文本文字视为具有数据类型 CHAR，通过使用空白填充的比较语义进行比较.
  • Within expressions and conditions, Oracle treats text literals as though they have the datatype CHAR by comparing them using blank-padded comparison semantics.

以及空白填充比较语义的文档 声明:

使用空格填充语义，如果两个值的长度不同，那么 Oracle 首先在较短的值的末尾添加空格，使它们的长度相等.Oracle 然后逐个字符比较值直到第一个不同的字符.在第一个不同位置具有较大字符的值被认为较大.如果两个值没有不同的字符，则认为它们相等.此规则意味着如果两个值仅在尾随空格数上不同，则它们相等.仅当比较中的两个值都是数据类型CHAR、NCHAR 的表达式时，Oracle 才使用空白填充的比较语义、文本文字或 USER 函数返回的值.

With blank-padded semantics, if the two values have different lengths, then Oracle first adds blanks to the end of the shorter one so their lengths are equal. Oracle then compares the values character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of datatype CHAR, NCHAR, text literals, or values returned by the USER function.

由于比较的左侧是 CHAR(10) 并且右侧是文本文字，因此使用空白填充的比较语义和 'hello '= '你好' 是真的.

Since the left-hand side of the comparison is a CHAR(10) and the right-hand side is a text literal then blank-padded comparison semantics are used and 'hello ' = 'hello' is true.

您可以在简单示例中看到这一点:

You can see this in the simple example:

SELECT * FROM DUAL WHERE 'hello    ' = 'hello';

更新:

[TL;DR] 至少从 Oracle 7(1992 年发布)开始，所有版本的 Oracle 中都出现了这种行为.我不再搜索关于 20 多年前发布的文档，但我希望您会发现这是大多数(所有?)版本中的行为.

[TL;DR] This behaviour has appeared in all versions of Oracle since at least Oracle 7 (released in 1992). I stopped searching for the documentation on releases over two decades old but I expect that you will find that this has been the behaviour in most (all?) versions.

这里是各种版本的文档:

Here is the documentation for the various versions:

  • Oracle 12c 文本文字 &空白填充语义
  • Oracle 11g 文本文字 &空白填充语义
  • Oracle 10gR2 文本文字 &空白填充语义
  • Oracle 9 文本文字 &空白填充语义
  • Oracle 8 文本文字 &空白填充语义莉>
  • Oracle 7 文本文字
  • Oracle 12c Text Literals & blank-padded semantics
  • Oracle 11g Text Literals & blank-padded semantics
  • Oracle 10gR2 Text Literals & blank-padded semantics
  • Oracle 9 Text Literals & blank-padded semantics
  • Oracle 8 Text Literals & blank-padded semantics
  • Oracle 7 Text Literals

相关文章