关于oracle中的汉字排序错乱

Yong Huang

原帖由 secooler 于 2010-2-25 08:30 发表
另外，用西欧字符集AMERICAN_AMERICA.WE8ISO8859P1存放汉字本身就是不严谨的。
此时存放在数据库中的内容其实是“乱码”，只因为在使用的过程中“客户终端字符集”、“NLS_LANG”环境变量以及“数据库字符集”都是WE8ISO8859P1，没有发生“转码”，客户端才得到的“汉字”内容。

secooler

So without changing the character set, there's no solution for him. Correct?

Yong Huang

secooler

在数据库字符集为WE8ISO8859P1下，因为与编码有关，从数据库的SQL角度貌似没有好的解决方案。
sec@ora10g> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

sec@ora10g> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.WE8ISO8859P1

create table t (x varchar2(10));  
insert into t values ('侯');      
insert into t values ('你');      
insert into t values ('做');      
insert into t values ('拉');      
insert into t values ('推');      
insert into t values ('拆');      
commit;                           

sec@ora10g> select * from t order by x;

X
----------
拆
拉
你
推
做
侯

6 rows selected.

sec@ora10g> select * from t order by nlssort(x,'NLS_SORT=SCHINESE_PINYIN_M');

X
----------
拆
拉
你
推
做
侯

6 rows selected.

sec@ora10g> select * from t order by nlssort(x,'NLS_SORT=SCHINESE_RADICAL_M');

X
----------
拆
拉
你
推
做
侯

6 rows selected.

sec@ora10g> select * from t order by nlssort(x,'NLS_SORT=SCHINESE_STROKE_M');

X
----------
拆
拉
你
推
做
侯

6 rows selected.

在这个实验过程中，得到的排序结果是一样的。
每个汉字背后都是由两个西欧字符的拼接。

secooler

ZALBB

原帖由 secooler 于 2010-2-25 22:25 发表
《【NLSSORT】改变Oralce 对简体汉字的排序规则（拼音、部首、笔画）》
http://space.itpub.net/519536/viewspace-627797

供参考

secooler

怀疑，默认情况下并非按照拼音测试，
SCOTT@nc200>select * from (select '金' from dual union all select '星' from dual union all
  2                 select '优' from dual union all select '睿' from dual) order by 1;

'
--
金
星
优
睿

4 rows selected.

Elapsed: 00:00:00.01
SCOTT@nc200>select sysdate from dual;

SYSDATE
-------------------
2010-07-05 14:01:05

Elapsed: 00:00:00.00
SCOTT@nc200>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

5 rows selected.

Elapsed: 00:00:00.12
SCOTT@nc200>select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ -----------------------------------------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               ZHS16GBK
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_RDBMS_VERSION              10.2.0.4.0

20 rows selected.

Elapsed: 00:00:00.04

ZALBB

原帖由 secooler 于 2010-2-25 22:25 发表
《【NLSSORT】改变Oralce 对简体汉字的排序规则（拼音、部首、笔画）》
http://space.itpub.net/519536/viewspace-627797

供参考

secooler

to secooler:

1  关于你的这篇文章（http://space.itpub.net/519536/viewspace-627797）中这一段，

   4.在此环境下，查看默认的汉字排序规则
   sec@secooler> select * from t order by x;
   
   X
   ------------------------------
   拆
   侯
   拉
   你
   推
   做
   
   6 rows selected.
   
   可见，此时的默认汉字排序规则是“拼音”。 对此，我有不同看法

2  目前我们的数据库字符集大多数选择（ZHS16GBK），在这里假设你文章中的数据库也是选择此此字符集。
   而该字符集，ORACLE定义，是按二进制排序; 遇上汉字时，则按照汉字的字符编码模式(character encoding schema)排序。
   如我的库的字符集信息

   SQL>select * from sys.props$;
   
   NAME                                     VALUE$                                   COMMENT$
   ---------------------------------------- ---------------------------------------- --------------------------------------------------
   ...
   NLS_CHARACTERSET                         ZHS16GBK                                 Character set
   NLS_CALENDAR                             GREGORIAN                                Calendar system
   NLS_DATE_FORMAT                          DD-MON-RR                                Date format
   NLS_DATE_LANGUAGE                        AMERICAN                                 Date language
   NLS_SORT                                 BINARY                                   Linguistic definition
   ...
   NLS_RDBMS_VERSION                        10.2.0.4.0                               RDBMS version for NLS parameters
   WORKLOAD_CAPTURE_MODE                                                             CAPTURE implies workload capture is in progress
   
   28 rows selected.
   
   Elapsed: 00:00:00.06


3  测试发现，之所以出现你上面的情况, 是因为恰巧这几个字的字符编码模式值,刚好和该字的拼音大小顺序一致。
   也就是，这几个字的字符编码模式值排序顺序为，拆<侯<拉<你<推<做（如下:ASCII出的值），
   而在拼音上，也恰巧是 拆(chi)<侯(hou)<拉(la)<你(ni)<推(tui)<做(zuo)。因此，你ORDER BY时，刚好就看到是拼音排序。
   
   NCV31@ncdb>select ascii('拆'), ascii('侯'),ascii('拉'),ascii('你'),ascii('推'),ascii('做') from dual;
   
        ASCII('拆')      ASCII('侯')      ASCII('拉')      ASCII('你')      ASCII('推')      ASCII('做')
   ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
              45808            47854            49325            50403            52678            55286
   
   6 rows selected.

4  现在我举个反例，假设默认是拼音排序，则下面这四字的排序结果，应该如下：
   金(jin) < 睿(rui) < 星(xing) < 优(you)
   
   SQL>select name from (select '金' name from dual union all select '优' name from dual union all
     2                    select '睿' name from dual union all select '星' name from dual);
   
   NAME
   ----------------------------------------
   金
   优
   睿
   星
   
   4 rows selected.
   
   Elapsed: 00:00:00.01
   
5  但实际上, “睿” 并没有排在第2位，
   SQL>select name from (select '金' name from dual union all select '优' name from dual union all
     2                    select '睿' name from dual union all select '星' name from dual) order by name;
   
   NAME
   ----------------------------------------
   金
   星
   优
   睿
   
   4 rows selected.
   
   Elapsed: 00:00:00.01
   
6  之所以“睿” 被排在最后，是因为其编码值最大：61091。因此，真正按照二进制排序时，就出现如上的结果。
   SQL>select ascii('金'), ascii('星'), ascii('优'), ascii('睿') from dual;
   
        ASCII('金')      ASCII('星')      ASCII('优')      ASCII('睿')
   ---------------- ---------------- ---------------- ----------------
              48624            53447            54213            61091
              
   Elapsed: 00:00:00.01           
   SQL>select chr(48624), chr(53447), chr(54213), chr(61091) from dual;
   
   CH CH CH CH
   -- -- -- --
   金 星 优 睿
   
   Elapsed: 00:00:00.01   
   SQL>select name, dump(name) dump from (select '金' name from dual union all select '优' name from dual union all
     2         select '睿' name from dual union all select '星' name from dual) order by name;
   
   NAME                                     DUMP
   ---------------------------------------- -------------------------
   金                                       Typ=96 Len=2: 189,240
   星                                       Typ=96 Len=2: 208,199
   优                                       Typ=96 Len=2: 211,197
   睿                                       Typ=96 Len=2: 238,163
   
   4 rows selected.

7  测试版本和时间。   
   SQL>select * from v$version;
   
   BANNER
   ----------------------------------------------------------------
   Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
   PL/SQL Release 10.2.0.4.0 - Production
   CORE    10.2.0.4.0      Production
   TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
   NLSRTL Version 10.2.0.4.0 - Production
   
   5 rows selected.

   Elapsed: 00:00:00.21
   SQL>select sysdate from dual;
   
   SYSDATE
   -------------------
   2010-07-07 10:52:17
   
   Elapsed: 00:00:00.01

secooler

感谢ZALBB的测试。在字符集为ZHS16GBK时，默认汉字排序并没有按照拼音顺序进行排序。

SQL> col name for a30
SQL> col value$ for a30
SQL> col comment$ for a40
SQL> set lines 131
SQL> set pages 200
SQL> select * from sys.props$;
NAME                           VALUE$                         COMMENT$
------------------------------ ------------------------------ ----------------------------------------
... ...
NLS_CHARACTERSET               ZHS16GBK                       Character set
NLS_CALENDAR                   GREGORIAN                      Calendar system
NLS_DATE_FORMAT                DD-MON-RR                      Date format
NLS_DATE_LANGUAGE              AMERICAN                       Date language
NLS_SORT                       BINARY                         Linguistic definition
NLS_TIME_FORMAT                HH.MI.SSXFF AM                 Time format
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM       Time stamp format
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR             Time with timezone format
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR   Timestamp with timezone format
NLS_DUAL_CURRENCY              $                              Dual currency symbol
NLS_COMP                       BINARY                         NLS comparison
NLS_LENGTH_SEMANTICS           BYTE                           NLS length semantics
NLS_NCHAR_CONV_EXCP            FALSE                          NLS conversion exception
NLS_NCHAR_CHARACTERSET         AL16UTF16                      NCHAR Character set
NLS_RDBMS_VERSION              10.2.0.3.0                     RDBMS version for NLS parameters
... ...


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production


SQL> select name from (select '金' name from dual union all select '优' name from dual union all select '睿' name from dual union all select '星' name from dual);

NA
--
金
优
睿
星

SQL> select name from (select '金' name from dual union all select '优' name from dual union all select '睿' name from dual union all select '星' name from dual) order by  nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M');

NA
--
金
睿
星
优



secooler