浅析NLS_COMP、NLS_SORT(三）

来源：互联网发布：io是哪里的域名编辑：程序博客网时间：2024/04/30 16:35

oracle数据库提供了两种排序方式：基于二进制(binary)和基于语言（linguistic）。

基于二进制的排序方法，以字符串的数据库字符集编码为基础，编码靠前的字符排序靠前，反之编码靠后的字符排序靠后。采用二进制排序方式，是oracle数据库的默认选择，具有较好的数据操作性能。但是在某些情况下，可能不能满足我们的排序需求，例如，对于中文排序，依据字符编码往往没有语言意义的，而如果可以采用基于偏旁或者笔画的方式来排序中文，则显得比较人性化一点。

为此，oracle数据库提供了另外一种排序方式：基于语言的排序。同时，基于语言的排序，又可细分为基于单一语言和基于多种语言两种方式。

无论是基于二进制的排序还是基于语言的排序，oracle均通过排序键来进行排序和比较的。排序键是一种二进制格式的数据，在基于二进制的排序方法下，排序键取值与字符集编码，在基于语言的排序方法下，排序键基于一定的算法来获取，例如，来单一语言的排序中，排序键由major value和minor value构成，在多语言的排序中则基于ISO 14651来进行计算。不管采用何种计算方式来获取排序键，排序键的值与具有相同参数的NLSSORT函数的返回值是完全相等的。

在当前会话中，具体采用那种排序方式，是由两个参数NLS_COMP、NLS_SORT来控制的。当前会话的NLS_COMP、NLS_SORT参数默认是从nls_instance_parameters继承而来，当然我们也可以通过环境变量或者alter session来手动更改。

NLS_COMP的取值有三个：BINARY （二进制） LINGUISTIC（基于语言） ANSI(为向后兼容而保留)。

NLS_SORT的取值比较多，可以通过查询v$nls_valid_values的parameter=‘SORT' 来获取。

下图显示了，在NLS_COMP的不同取值下，各种sql操作和函数的排序行为。

例如，当我们进行“=”操作时，如果nls_comp取值为binary，则采用binary排序方式，如果nls_comp取值为linguistic,在采用nls_sort参数指定的排序方式，如果nls_comp取值为ansi,在采用nls_sort参数指定的排序方式。

首先，我们来看一下二进制的排序方式（不管采用何种方式，如果在NLS_SORT参数中添加_CI后缀则表示不区分大小写，添加_AI后缀则表示不区分重读音和大小写）。

SQL> select * from tab_nls;V       BCODE------------------------------ --------------------a       61b       62A       41B       42SQL> select v,bcode,nlssort(v,'NLS_SORT=BINARY') c1 from tab_nls order by c1;V       BCODE    C1------------------------------ -------------------- ------------------------------A       41    4100B       42    4200a       61    6100b       62    6200SQL> select v,bcode,nlssort(v,'NLS_SORT=BINARY_CI') c1 from tab_nls order by c1;V       BCODE    C1------------------------------ -------------------- ------------------------------A       41    6100a       61    6100B       42    6200b       62    6200

从这里我们可以看出，二进制排序方式是基于数据库字符集的编码来进行排序的的（BCODE列是v列在数据库中字符集编码的16进制表示）

下面我们来看一下基于单一语言的排序。首先需要说明的是，基于单一语言的排序，只针对字符集是unicode的数据库有效，当字符集为非unicode时，如果指定了单一语言的排序方式，则默认使用基于二进制的排序方法。对于单一语言排序，往往存在与之对应的多语言排序（以_M后缀结束），但这不是必须的。

在单一语言排序中，同样存在_CI和_AI的后缀使用：

_CI:不区分大小写，但是区分重读音，可以排序的字符包括基本字符以及标点符号

_AI:不区分大小写，不区分重读音，可以排序的字符包括基本字符和标点符号

无论_AI和_CI,ORACLE均将标点符号如（“-”，“*”），作为未定义的字符处理，付给器较低的权重，已进行比较。这一点与多语言的排序时有区别的，在多语言排序中，这些符号可能会在排序操作过程中北忽略。

在unicode字符集下：

SQL> select parameter,value from nls_database_parameters;PARAMETER       VALUE------------------------------ ------------------------------NLS_LANGUAGE       AMERICANNLS_TERRITORY       AMERICANLS_CURRENCY       $NLS_ISO_CURRENCY       AMERICANLS_NUMERIC_CHARACTERS       .,NLS_CHARACTERSET       AL32UTF8NLS_CALENDAR       GREGORIANNLS_DATE_FORMAT        DD-MON-RRNLS_DATE_LANGUAGE       AMERICANNLS_SORT       BINARYNLS_TIME_FORMAT        HH.MI.SSXFF AMPARAMETER       VALUE------------------------------ ------------------------------NLS_TIMESTAMP_FORMAT       DD-MON-RR HH.MI.SSXFF AMNLS_TIME_TZ_FORMAT       HH.MI.SSXFF AM TZRNLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZRNLS_DUAL_CURRENCY       $NLS_COMP       BINARYNLS_LENGTH_SEMANTICS       BYTENLS_NCHAR_CONV_EXCP       FALSENLS_NCHAR_CHARACTERSET       AL16UTF16NLS_RDBMS_VERSION       11.2.0.3.0已选择20行。

<pre name="code" class="sql">SQL> select v,bcode,nlssort(v,'NLS_SORT=FRENCH_CI') c1 from tab_nls order by c1;V   BCODEC1---------- -------------------- ------------------------------a   6114000200A   4114000200a-a   612D6114140002002D0200aa   6161 141400020200aa-   61612D1414000202002D00a-b   612D6214190002002D0200ab   6162 141900020200ab-   61622D1419000202002D00B   4219000200b   6219000200已选择10行。SQL> select v,bcode,nlssort(v,'NLS_SORT=FRENCH') c1 from tab_nls order by c1;V   BCODEC1---------- -------------------- ------------------------------A   4114000100a   6114000200a-a   612D6114140002002D0200aa   6161 141400020200aa-   61612D1414000202002D00a-b   612D6214190002002D0200ab   6162 141900020200ab-   61622D1419000202002D00B   4219000100b   6219000200已选择10行。

在非UNICODE字符集下：

SQL> select parameter,value from nls_database_parameters;PARAMETER       VALUE------------------------------ ------------------------------NLS_LANGUAGE       AMERICANNLS_TERRITORY       AMERICANLS_CURRENCY       $NLS_ISO_CURRENCY       AMERICANLS_NUMERIC_CHARACTERS       .,NLS_CHARACTERSET       ZHS16GBKNLS_CALENDAR       GREGORIANNLS_DATE_FORMAT        DD-MON-RRNLS_DATE_LANGUAGE       AMERICANNLS_SORT       BINARYNLS_TIME_FORMAT        HH.MI.SSXFF AMPARAMETER       VALUE------------------------------ ------------------------------NLS_TIMESTAMP_FORMAT       DD-MON-RR HH.MI.SSXFF AMNLS_TIME_TZ_FORMAT       HH.MI.SSXFF AM TZRNLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZRNLS_DUAL_CURRENCY       $NLS_COMP       BINARYNLS_LENGTH_SEMANTICS       BYTENLS_NCHAR_CONV_EXCP       FALSENLS_NCHAR_CHARACTERSET       AL16UTF16NLS_RDBMS_VERSION       11.2.0.3.0已选择20行。

SQL> select v,bcode,nlssort(v,'NLS_SORT=FRENCH') c1 from tab_nls order by c1;V       BCODE    C1------------------------------ -------------------- ------------------------------A       41    4100B       42    4200a       61    6100a-a       612D61    612D6100a-b       612D62    612D6200aa       6161    616100aa-       61612D    61612D00ab       6162    616200ab-       61622D    61622D00b       62    6200已选择10行。SQL> select v,bcode,nlssort(v,'NLS_SORT=FRENCH_CI') c1 from tab_nls order by c1;V       BCODE    C1------------------------------ -------------------- ------------------------------A       41    4100B       42    4200a       61    6100a-a       612D61    612D6100a-b       612D62    612D6200aa       6161    616100aa-       61612D    61612D00ab       6162    616200ab-       61622D    61622D00b       62    6200已选择10行。

从这里也可以看出，在单一语言排序方式下，如果数据库字符集为非unicode，则采用binary排序方式，且始终区分大小写

最后，让我们来看一下基于语言的多语言排序。

多语言排序并不受字符集的影响，在多语言排序中，ORACLE官方文档将其划分为三个级别：主级别、次级别和第三级别。随着级别的递增，排序时所要考虑的因素也越多，在主级别，排序仅仅考虑待排序字符的大小，不考虑重读音、大小写和标点符号；在次级别，数据库排序需要考虑字符的大小、重读音，但并不好了大小写和标点符号；在第三级别，则字符大小、重读音、大小写和标点符号均需要考虑。那么我们怎么控制排序的级别哪？是通过_AI和_CI后缀来实现的。

示例如下：

主级别：

SQL> select * from (select t.*,nlssort(v,'NLS_SORT=SCHINESE_PINYIN_M_AI') c1,dbms_random.value() c2 from tab_nls t order by c2) order by c1;V   BCODE      C1C2---------- ---------- ------------------------------ -------------A   41      01EA      .90738545862a   61      01EA      .29131364850Aa   4161       01EA01EA      .98120214993aA   6141       01EA01EA      .04583731581a-a   612D61     01EA01EA      .80005010727a-A   612D41     01EA01EA      .06654449141u   75      025B      .85688941689ü          A8B9       025B                            .85685506076已选择8行。已用时间:  00: 00: 00.00SQL>

次级别：

SQL> select * from (select t.*,nlssort(v,'NLS_SORT=SCHINESE_PINYIN_M_CI') c1,dbms_random.value() c2 from tab_nls t order by c2) order by c1;V   BCODE      C1C2---------- ---------- ------------------------------ -------------a   61      01EA000002      .03900464074A   41      01EA000002      .85093266211aA   6141       01EA01EA00000202      .36865684718a-a   612D61     01EA01EA00000202      .79673747253a-A   612D41     01EA01EA00000202      .34622505373Aa   4161       01EA01EA00000202      .92420504676u   75      025B000002      .44687837071ü          A8B9       025B00000214                    .15240488192已选择8行。已用时间:  00: 00: 00.01

第三级别：

SQL> select * from (select t.*,nlssort(v,'NLS_SORT=SCHINESE_PINYIN_M') c1,dbms_random.value() c2 from tab_nls t order by c2) order by c1;V   BCODE      C1C2---------- ---------- ------------------------------ -------------a   61      01EA0000020002      .50154768256A   41      01EA0000020006      .14626327119aA   6141       01EA01EA00000202000206      .87899379077a-a   612D61     01EA01EA0000020200022F02      .02659477873a-A   612D41     01EA01EA0000020200022F06      .40281310377Aa   4161       01EA01EA00000202000602      .93223946545u   75      025B0000020002      .47770920012ü          A8B9       025B000002140002                .44596977028已选择8行。已用时间:  00: 00: 00.00

从上面的示例，可以看出，在多语言排序时，只有第三级别是可以对标点符号进行排序的，在其他两种级别下，标点符号会被忽略，也就是官方文档中说的标点符号输入忽略字符，忽略字符的概念只在多语言排序中存在。

需要注意的地方：

1：在某些语言中某个字符的大写字符可能对应多个字符，例如德文中的 ß对应的大写字符为SS，此时如果在程序中使用upper、lowner、initcap等函数，可能得不到预期的效果，因为这些函数是基于二进制转换的，并没有基于语义。在这种情况下，我们可以尝试NLS_UPPER、nls_lower、NLS_INITCAP等函数，例如：

SQL> SELECT NLS_UPPER ('große','NLS_SORT=XGERMAN'),upper('große')  FROM DUAL;NLS_UP UPPER(------ ------GROSSE GROßE

2：我们知道，oracle是通过排序键进行排序的，而排序键的数据类型为raw，长度限制为2000字节，因此对于长度超过2000的数据进行某些操作时，可能会得不到预期的效果，例如group by 等；

3：如果我们设置nls_sort nls_comp参数，导致排序操作不是按照二进制方式进行，有可能会影响数据库性能，因为索引是依据二进制格式创建的，这是我们可以创建基于语言的函数索引来提高处理效率，但是，使用基于语言的函数索引是有某些限制的：

为了使优化器走函数索引，索引列必须为not null或者在sql 语句的where子句中指定 where nlssort( 列) is not null；

不是所有的操作都支持基于语言的索引：