logo标签列表

为什么Oracle的varchar排序顺序与varchar比较行为不一致？

oraclesortingcompare
10

像这样的SQL语句：

select * from (
  select '000000000000' as x from dual
  union
  select '978123456789' as x from dual
  union 
  select 'B002AACD0A' as x from dual
) /*where x>'000000000000'*/ order by x;

输出结果:

B002AACD0A
000000000000
978123456789

取消WHERE限制后，结果如下：
B002AACD0A
978123456789

我本以为结果只会是978123456789，因为在没有限制条件的情况下，查询返回B002AACD0A之前是000000000000。
这种行为该如何解释？我该如何对字符进行排序和比较，使它们像整数一样可以一起工作？
有趣的是，将限制条件更改为x>'B002AACD0A'时，结果为空。将其更改为x>978123456789将返回B002AACD0A。
也就是说，在比较时：
B002AACD0A > 978123456789 > 000000000000

但是当进行排序时：
978123456789 > 000000000000 > B002AACD0A

当使用二进制排序明确指定（order by NLSSORT(x,'NLS_SORT=BINARY_AI')）时，结果为B002AACD0A>978123456789>000000000000，并与比较的行为匹配。但我仍然不知道为什么会发生这种情况。

你使用的Oracle版本是什么？我看到的结果非常不同...第一个查询得到的是000000000000、978123456789和B002AACD0A，当取消注释时，结果变成了978123456789和B002AACD0A。我的版本是10.2.0.3.0。 - greghmerrill
我正在使用10.2.0.4.0版本。看到排序行为不同并不让我感到惊讶，这可能与NLS_SORT设置有关（在我的情况下，它是GERMAN）。但无论如何，我希望在单个查询中，排序和比较的行为方式是相似的... - Peter P
9.2.0.7.0、10.2.0.1.0、11.2.0.1.0和11.1.0.6.0（不要问为什么）都返回000000000000、978123456789、B002AACD0A... - Ben
感谢您查看此内容。似乎只在我的数据库上出现了奇怪的行为。 :( - Peter P
1个回答
16

Peter，

排序行为由NLS_SORT会话参数控制，而比较行为取决于NLS_COMP参数。你的两个参数可能不匹配。

我使用以下参数得到与你相同的结果：

SQL> SELECT *
  2    FROM nls_session_parameters
  3   WHERE parameter IN ('NLS_COMP', 'NLS_SORT');

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_SORT                       FRENCH
NLS_COMP                       BINARY

然而，当这两者匹配时，结果是一致的：

SQL> alter session set nls_comp=LINGUISTIC;

Session altered

SQL> select * from (
  2    select '000000000000' as x from dual
  3    union
  4    select '978123456789' as x from dual
  5    union
  6    select 'B002AACD0A' as x from dual
  7  ) /*where x>'000000000000'*/ order by x;

X
------------
B002AACD0A
000000000000
978123456789

SQL> select * from (
  2    select '000000000000' as x from dual
  3    union
  4    select '978123456789' as x from dual
  5    union
  6    select 'B002AACD0A' as x from dual
  7  ) where x > '000000000000' order by x;

X
------------
978123456789
太好了！非常感谢您指出这一点，我不知道有一个叫做NLS_COMP的参数。 - Peter P
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接