关于oracle对汉字的排序

最近项目过程中碰到对于数据进行排序按照升序排序需求。这需求貌似很简单，我没多想直接order by columnName。忽略了汉字排序的问题导致bug的出现。

 

业务要求，按照excel中排序出来的结果。对于汉字excel默认是按照拼音排序的，那么问题来了，oracle对于汉字究竟是如何排序的？

经查阅资料发现，对于oracle对汉字排序的问题网上说法不一。有的说是根据二进制编码排序的，有的却说是根据拼音排序的。

自己试了试，肯定默认不是拼音排序。可能是二进制编码吧。

 

说下具体怎么改，也是转载网上的一些方法。

1、语句级别设置排序方式：
     //按照笔划排序 
   select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M'); 
   //按照部首排序 
   select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_RADICAL_M'); 
   //按照拼音排序，此为系统的默认排序方式 
   select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M'); 
2、Session级别的设置，修改ORACLE字段的默认排序方式：
              按拼音：alter session set nls_sort = SCHINESE_PINYIN_M;
              按笔画：alter session set nls_sort = SCHINESE_STROKE_M;
              按偏旁：alter session set nls_sort = NLS_SORT=SCHINESE_RADICAL_M；
3、修改系统参数（数据库所在操作系统）：
               set NLS_SORT=SCHINESE_RADICAL_M ;export NLS_SORT （sh）setenv NLS_SORT SCHINESE_RADICAL_M (csh)  HKLCSOFTWAREORACLEhome0NLS_SORT (win注册表)；

 

我这里只是用来语句级别的就可以了。

 

回到系统默认排序的问题就是设置数据库的NLS_SORT 这个参数

通过查看select * from nls_database_parameters; 数据库参数

我的默认值为BINARY

Oracle 官方说明：

 

Property	Description
Parameter type	String
Syntax	NLS_SORT = { BINARY | linguistic_definition }
Default value	Derived from NLS_LANGUAGE
Modifiable	ALTER SESSION
Range of values	BINARY or any valid linguistic definition name

 

• If the value is BINARY, then the collating sequence for ORDER BY queries is based on the numeric value of characters (a binary sort that requires less system overhead).

• If the value is a named linguistic sort, sorting is based on the order of the defined linguistic sort. Most (but not all) languages supported by the NLS_LANGUAGE parameter also support a linguistic sort with the same name.

  Note:

  Setting NLS_SORT to anything other than BINARY causes a sort to use a full table scan, regardless of the path chosen by the optimizer. BINARY is the exception because indexes are built according to a binary order of keys. Thus the optimizer can use an index to satisfy the ORDER BY clause when NLS_SORT is set to BINARY. If NLS_SORT is set to any linguistic sort, the optimizer must include a full table scan and a full sort in the execution plan.

         You must use the NLS_SORT operator with comparison operations if you want the linguistic sort behavior.

 

 

 

BINARY 是二进制编码排序的设定。