为什么Oracle的varchar排序顺序不匹配varchar比较的行为？ [英] Why does Oracle's varchar sort order not match the behavior of varchar comparison?

问题描述

一个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限制，结果是

After uncommenting the WHERE-restriction, the result is

B002AACD0A
978123456789

我预期结果只是 978123456789 ，因为 B002AACD0A

I would have expected the result to be just 978123456789 since B002AACD0A is returned before 000000000000 when running the query without restriction.

如何解释这种行为？在 000000000000

How can this behavior be explained? And how am I supposed to sort and compare varchars so that they can work together like I can do with integers?

编辑：有趣的是，当将限制更改为 x>'B002AACD0A'时，结果为空。将其更改为 x> 978123456789 会返回 B002AACD0A 。

EDIT: Funny enough, when changing the restriction to x>'B002AACD0A', the result is empty. Changing it tox>978123456789 returns B002AACD0A.

也就是说当比较时：

B002AACD0A > 978123456789 > 000000000000

但在排序时

978123456789 > 000000000000 > B002AACD0A 

EDIT 2 ：明确使用二进制排序c> order by NLSSORT（x，'NLS_SORT = BINARY_AI'）），结果是 B002AACD0A> 978123456789> 000000000000 ，并匹配比较行为。但我仍然不知道为什么会发生这种情况。

EDIT 2: When using binary sort explicitely (order by NLSSORT(x,'NLS_SORT=BINARY_AI')), the result is B002AACD0A>978123456789>000000000000 and matches the behavior of comparison. But I still do not have a clue why this is happening.

推荐答案

Peter，

排序的行为由 NLS_SORT 会话参数，而比较的行为取决于 NLS_COMP 参数。

the behaviour of the sorting is regulated by the NLS_SORT session parameter, whereas the behaviour for comparisons is dependent upon the NLS_COMP parameter. You must have a mismatch.

我获得与以下参数相同的结果：

I obtain the same result as you do with the following parameters:

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

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

但是，当两者匹配时，结果是一致的：

However when the two are matched the result is consistent:

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

这篇关于为什么Oracle的varchar排序顺序不匹配varchar比较的行为？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！