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

问题描述

类似的 SQL 语句:

An SQL statement like:

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 在 000000000000 之前返回.

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

如何解释这种行为?我应该如何对 varchars 进行排序和比较，以便它们可以像处理整数一样一起工作?

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.

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 

当显式使用二进制排序时(order by NLSSORT(x,'NLS_SORT=BINARY_AI'))，结果为B002AACD0A>978123456789>000000000000 并匹配比较.但我仍然不知道为什么会这样.

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 know why this is happening.

推荐答案

彼得，

排序的行为由 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屋！