ITPub博客

首页 > Linux操作系统 > Linux操作系统 > nls_sort与汉字排序

nls_sort与汉字排序

原创 Linux操作系统 作者:realkid4 时间:2011-03-22 21:24:38 0 删除 编辑

 

朋友在进行报表需求评估的时候,突然问笔者一个问题:在数据库里面,汉字进行排序的顺序是按照什么方法?对空值null,是排在第一位还是最后一位?当时做了一个简单的小实验,立刻获得了答案。但是之后,在想是不是还有其它方式进行排序的支持呢?这时可以使用nls_sort参数设置。

 

 

nls_sort是Oracle的初始化参数之一。根据官方文档的介绍,该参数“specifies the collating sequence for Order by queries”,也就是说,设置该参数是可以控制SQL order by语句显示结果顺序的依据的。

 

 

初始化环境

 

首先,构建实验环境和数据。

 

//10gR2数据库环境

SQL> 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 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 Production

 

//数据表T,其中包括数字和字符类型字段

SQL> desc t;

Name   Type         Nullable Default Comments

------ ------------ -------- ------- --------

IDNUM  NUMBER(10)   Y                        

ENCHAR VARCHAR2(10) Y                        

CHCHAR VARCHAR2(10) Y                        

 

//包括中文、英文和空行;

SQL> select * from t;

 

      IDNUM ENCHAR     CHCHAR

----------- ---------- ----------

          1 adf        中国

          2 flsd      

          3 sd         阿姨

          4 lk         实验

          5            罪恶

          6 mll        饕餮

         21 lf         lfs

 

7 rows selected

 

 

 

nls_sort相关参数情况,发现nls_sort参数的设置是空。

 

 

SQL> show parameter nls

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------

nls_calendar                         string     

nls_comp                             string     

(篇幅原因,省略

nls_language                         string      SIMPLIFIED CHINESE

nls_sort                             string     

 

 

nls_sort取值分为两种类型:binary或者linguistic_definition。binary表示排序标准按照字符存储的顺序进行保存,这种方式对DBMS进行处理来说是最快的一种排序方式。linguistic_definition是指语系定义,通常一种语言language要对应特定的一系列语系定义。

 

当前系统中,我们发现nls_sort参数并没有进行定义。在没有定义nls_sort的时候,系统采用的排序方式与nls_language所对应的默认排序Default Sort方式相同。

 

Oracle文档库的《Globalization Support Guide》中,定义了各种language对应的Default Sort方式。当前nls_language取值为“SIMPLIFIED CHINESE”,对应的默认排序方式就是binary。

 

默认排序方式

 

在当前系统中,采用binary作为排序方式,对应的是存储的二进制编码顺序。下面通过实验来证明。

 

SQL> col idnum for a5;

SQL> select t.*, dump(chchar,1016) from t order by chchar;

 

IDNUM ENCHAR     CHCHAR     DUMP(CHCHAR,1016)

----- ---------- ---------- --------------------------------------------------------------------------------

   21 lf         lfs        Typ=1 Len=3 CharacterSet=ZHS16GBK: 6c,66,73

    3 sd         阿姨       Typ=1 Len=4 CharacterSet=ZHS16GBK: b0,a2,d2,cc

    4 lk         实验       Typ=1 Len=4 CharacterSet=ZHS16GBK: ca,b5,d1,e9

    1 adf        中国       Typ=1 Len=4 CharacterSet=ZHS16GBK: d6,d0,b9,fa

    5            罪恶       Typ=1 Len=4 CharacterSet=ZHS16GBK: d7,ef,b6,f1

    6 mll        饕餮       Typ=1 Len=4 CharacterSet=ZHS16GBK: f7,d2,f7,d1

    2 flsd                  NULL

 

7 rows selected

 

 

通过dump成16进制的字符编码,可以看出binary排序方式。而且,在binary方式下,空会被放在最末尾位置。

 

 

中文可用排序方式

 

Oracle9i开始,对中文语言方式(Simplified Chinese和Traditional Chinese)提供了多种排序方式。

 

排序方式名称

描述

SCHINESE_RADICAL_M

针对简体中文,按照部首(第一顺序)、笔划(第二顺序)排序

SCHINESE_STROKE_M

针对简体中文,按照笔划(第一顺序)、部首(第二顺序)排序

SCHINESE_PINYIN_M

针对简体中文,按照拼音排序

TCHINESE_RADICAL_M

针对繁体中文,按照部首(第一顺序)、笔划(第二顺序)排序

TCHINESE_STROKE_M

针对繁体中文,按照笔划(第一顺序)、部首(第二顺序)排序

 

由此,就为中文进行排序提供了多种的依据和设定。

 

 

设定排序方法1——参数文件设置

 

这种方法是最根本的方法。在参数文件pfile中,设置参数nls_sort的取值。之后重新启动数据库实例即可。这种方法会应用排序方式到实例所有的查询中,实际中较少使用。

 

设定排序方法2——Session会话级别设置

 

可以在会话中,通过alter session设置nls_sort取值。之后该会话就会自动应用上新的nls_sort方式。

 

SQL> alter session set nls_sort='SCHINESE_RADICAL_M';

 

Session altered

 

SQL> select t.*, dump(chchar,1016) from t order by chchar;

 

IDNUM ENCHAR     CHCHAR     DUMP(CHCHAR,1016)

----- ---------- ---------- --------------------------------------------------------------------------------

   21 lf         lfs        Typ=1 Len=3 CharacterSet=ZHS16GBK: 6c,66,73

    1 adf        中国       Typ=1 Len=4 CharacterSet=ZHS16GBK: d6,d0,b9,fa

    4 lk         实验       Typ=1 Len=4 CharacterSet=ZHS16GBK: ca,b5,d1,e9

    5            罪恶       Typ=1 Len=4 CharacterSet=ZHS16GBK: d7,ef,b6,f1

    3 sd         阿姨       Typ=1 Len=4 CharacterSet=ZHS16GBK: b0,a2,d2,cc

    6 mll        饕餮       Typ=1 Len=4 CharacterSet=ZHS16GBK: f7,d2,f7,d1

    2 flsd                  NULL

 

7 rows selected

 

SQL> alter session set nls_sort='SCHINESE_STROKE_M';

 

Session altered

 

SQL> select t.*, dump(chchar,1016) from t order by chchar;

 

IDNUM ENCHAR     CHCHAR     DUMP(CHCHAR,1016)

----- ---------- ---------- --------------------------------------------------------------------------------

   21 lf         lfs        Typ=1 Len=3 CharacterSet=ZHS16GBK: 6c,66,73

    1 adf        中国       Typ=1 Len=4 CharacterSet=ZHS16GBK: d6,d0,b9,fa

    3 sd         阿姨       Typ=1 Len=4 CharacterSet=ZHS16GBK: b0,a2,d2,cc

    4 lk         实验       Typ=1 Len=4 CharacterSet=ZHS16GBK: ca,b5,d1,e9

    5            罪恶       Typ=1 Len=4 CharacterSet=ZHS16GBK: d7,ef,b6,f1

    6 mll        饕餮       Typ=1 Len=4 CharacterSet=ZHS16GBK: f7,d2,f7,d1

    2 flsd                  NULL

 

7 rows selected

 

 

通过session级别的设置。我们可以清晰看出不同排序方法的效果。

 

 

设定排序方法3——在order by子句中直接指定

 

除了上述的两种方法,还可以在select语句的order by子句中直接指定排序方式。这样可以在SQL语句级别设置排序方式,应该是一种最灵活的控制方式。

 

 

SQL> select * from t order by chchar;

 

      IDNUM ENCHAR     CHCHAR

----------- ---------- ----------

         21 lf         lfs

          3 sd         阿姨

          4 lk         实验

          1 adf        中国

          5            罪恶

          6 mll        饕餮

          2 flsd      

 

7 rows selected

 

SQL> select * from t order by nlssort(chchar,'nls_sort=SCHINESE_RADICAL_M');

 

      IDNUM ENCHAR     CHCHAR

----------- ---------- ----------

         21 lf         lfs

          1 adf        中国

          4 lk         实验

          5            罪恶

          3 sd         阿姨

          6 mll        饕餮

          2 flsd      

 

7 rows selected

 

SQL> select * from t order by nlssort(chchar,'nls_sort=SCHINESE_STROKE_M');

 

      IDNUM ENCHAR     CHCHAR

----------- ---------- ----------

         21 lf         lfs

          1 adf        中国

          3 sd         阿姨

          4 lk         实验

          5            罪恶

          6 mll        饕餮

          2 flsd      

 

7 rows selected

 

 

 

此外,对很多的数据访问接口,如JDBC、Hibernate和JPA,都在一定程度上支持多种排序方式的指定。具体的方式可以参见相关文献资料,本文就不加以累述了。

 

 

最后,作为本文的结束,也作为一个新问题的起点。使用nls_sort之后,虽然可以方便的指定排序方式,但是还会带来一些问题。其中最大的问题就是索引的失效性。nls_sort的改写改变了原有的索引结构和顺序,会导致原有的索引不能使用。从而影响整体系统性能。

 

在这种情况下,就需要设置一种较为特殊的索引index类型,语系linguistic Indexes,同时也要对nls_comp参数加以控制设置,这样可以保证索引的正确设置和提高系统访问效率。这个问题,留待我们以后继续进行讨论。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-690198/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7752628