oracle中gbk和utf8的数据库在默认nls_comp、nl_sort默认都是binary,对大小写敏感。
数据库是GBK的情况
当设置nls_somp = LINGUISTIC or ANSI 且 nls_sort= binary_ci的情况大小写不敏感,其他均大小写敏感。
数据库为UTF8的是情况
当设置nls_somp = LINGUISTIC or ANSI 且nls_sort= languagename_ci的情况大小写不敏感,nls_sort= languagname的时候大小写敏感。
===========================================================
select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
select * from NLS_SESSION_PARAMETERS where parameter in ('NLS_COMP','NLS_SORT');
select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_COMP','NLS_SORT');
select * from NLS_Instance_Parameters where parameter in ('NLS_COMP','NLS_SORT');
ALTER SESSION SET NLS_COMP=LINGUISTIC;
ALTER SESSION SET NLS_SORT=BINARY_CI;
create table cw(name varchar2(20));
insert into cw values('IPTV');
insert into cw values('iptv');
insert into cw values('Iptv');
select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen
from cw;
select SYS_CONTEXT('USERENV','CURRENT_SCHEMA') CURRENT_SCHEMA from dual
============================================================================
nls_comp
oracle允许在各个字符集上进行基于语言的比较,排序操作,正常做法是通过系统函数实现,比较麻烦,可以通过设置nls_comp为LINGUISTIC,再结合nls_sort简化。
nls_comp 默认是BINARY,允许范围:BINARY , LINGUISTIC, or ANSI
当设置LINGUISTIC时需要设置NLS_SORT才能生效,ANSI就是LINGUISTIC的兼容参数,正常应该使用LINGUISTIC。
可以通过ALTER SESSION SET NLS_COMP = LINGUISTIC; 设置
一旦把nls_comp设置为LINGUISTIC需要根据nls_sort这是的情况重新创建索引,否则会照成索引失效。
CREATE INDEX i ON t(NLSSORT(col, 'NLS_SORT=FRENCH'));
The value of NLS_COMP affects the comparison behavior of SQL operations.
You can use NLS_COMP to avoid the cumbersome process of using the NLSSORT function in SQL statements when you want to perform a linguistic comparison instead of a binary comparison. When NLS_COMP is set to LINGUISTIC, SQL operations perform a linguistic comparison based on the value of NLS_SORT. A setting of ANSI is for backward compatibility; in general, you should set NLS_COMP to LINGUISTIC when you want to perform a linguistic comparison.
nls_sort
The NLS_SORT option specifies the sequence of character values used when sorting or comparing text. The value of NLS_SORT affects the GT
,GE
, LT
, and LE
operators, SORT command, and the SORTLINES function.
Within a session, you can dynamically modify the value of this option using the OLAP DML syntax show below or by using the SQL statementALTER SESSION
SET
option =
value.
nls_sort选项指明在所文本排序或者比较的时候character的值得顺序,它会影响到gt,ge,lt and le 操作,以及sort命令和sortlines 函数。这里没有明确说是否影响等于操作符。
字符集与nls_comp\nls_sort
通过实际验证gbk和utf8的数据库在默认nls_comp、nl_sort默认都是binary,对大小写敏感。
当设置nls_somp = LINGUISTIC or ANSI 切 nls_sort= binary_ci的情况大小写不敏感,其他均大小写敏感。
数据库为UTF8的是情况
当设置nls_somp = LINGUISTIC or ANSI 切 nls_sort= languagename_ci的情况大小写不敏感,nls_sort= languagename的时候大小写敏感。
GBK数据库
SQL> conn test/test@192.168.105.63:1521/orcl
Connected.
SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_CHARACTERSET ZHS16GBK
SQL> select * from NLS_SESSION_PARAMETERS where parameter in ('NLS_COMP','NLS_SORT');
PARAMETER VALUE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_SORT BINARY
NLS_COMP BINARY
SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;
ALTER SESSION SET NLS_SORT=BINARY_CI;
Session altered.
SQL>
Session altered.
SQL> select * from test.cw ;
NAME
------------------------------------------------------------
IPTV
iptv
Iptv
SQL> select * from test.cw where name = 'iptv';
NAME
------------------------------------------------------------
IPTV
iptv
Iptv
SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen
from cw; 2
CASEWHEN
----------
1
1
1
SQL> ALTER SESSION SET NLS_SORT=BINARY;
Session altered.
SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen
from cw 2 ;
CASEWHEN
----------
1
2
3
SQL> select * from test.cw where name = 'iptv';
NAME
------------------------------------------------------------
iptv
SQL> ALTER SESSION SET NLS_SORT='XSPANISH';
Session altered.
SQL> select * from test.cw where name = 'iptv';
NAME
------------------------------------------------------------
iptv
SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen
from cw 2 ;
CASEWHEN
----------
1
2
3
SQL> ALTER SESSION SET NLS_SORT='XSPANISH_CI';
Session altered.
SQL> select * from test.cw where name = 'iptv';
NAME
------------------------------------------------------------
iptv
SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen
from cw 2 ;
CASEWHEN
----------
1
2
3
SQL> ALTER SESSION SET NLS_SORT='ITALIAN';
Session altered.
SQL> select * from test.cw where name = 'iptv';
NAME
------------------------------------------------------------
iptv
SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen
from cw 2 ;
CASEWHEN
----------
1
2
3
SQL> ALTER SESSION SET NLS_SORT='ITALIAN_CI';
Session altered.
SQL> select * from test.cw where name = 'iptv';
NAME
------------------------------------------------------------
iptv
SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen
from cw 2 ;
CASEWHEN
----------
1
2
3
SQL>
UTF8数据库
SQL> conn sys/oracle@192.168.105.63:1521/oral2 as sysdba
Connected.
SQL> set line 2000
SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_CHARACTERSET AL32UTF8
SQL> select * from NLS_SESSION_PARAMETERS where parameter in ('NLS_COMP','NLS_SORT');
PARAMETER VALUE
------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_SORT BINARY
NLS_COMP BINARY
SQL> select * from sys.cw ;
NAME
--------------------
iptv
IPTV
Iptv
SQL> select * from sys.cw where name = 'iptv';
NAME
--------------------
iptv
SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen
from cw;
2
CASEWHEN
----------
2
1
3
SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;
Session altered.
SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;
Session altered.
SQL> select * from sys.cw where name = 'iptv';
NAME
--------------------
iptv
IPTV
Iptv
SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen
from cw; 2
CASEWHEN
----------
1
1
1
SQL> ALTER SESSION SET NLS_SORT='XSPANISH';
Session altered.
SQL> select * from sys.cw where name = 'iptv';
NAME
--------------------
iptv
SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen
from cw; 2
CASEWHEN
----------
2
1
3
SQL> ALTER SESSION SET NLS_SORT='XSPANISH_CI';
Session altered.
SQL> select * from sys.cw where name = 'iptv';
NAME
--------------------
iptv
IPTV
Iptv
SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen
from cw; 2
CASEWHEN
----------
1
1
1
SQL> ALTER SESSION SET NLS_SORT='ITALIAN';
Session altered.
SQL> select * from sys.cw where name = 'iptv';
NAME
--------------------
iptv
SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen
from cw; 2
CASEWHEN
----------
2
1
3
SQL> ALTER SESSION SET NLS_SORT='ITALIAN_CI';
Session altered.
SQL> select * from sys.cw where name = 'iptv';
NAME
--------------------
iptv
IPTV
Iptv
SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen
from cw; 2
CASEWHEN
----------
1
1
1
SQL> select * from NLS_SESSION_PARAMETERS where parameter in ('NLS_COMP','NLS_SORT');
PARAMETER VALUE
------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_SORT ITALIAN_CI
NLS_COMP LINGUISTIC
SQL> ALTER SESSION SET NLS_COMP=BINARY
2 ;
Session altered.
SQL> select * from sys.cw where name = 'iptv';
NAME
--------------------
iptv
SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen
from cw; 2
CASEWHEN
----------
2
1
3
SQL> select * from NLS_SESSION_PARAMETERS where parameter in ('NLS_COMP','NLS_SORT');
PARAMETER VALUE
------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_SORT ITALIAN_CI
NLS_COMP
附录
NLS_SORT
The NLS_SORT option specifies the sequence of character values used when sorting or comparing text. The value of NLS_SORT affects the GT
,GE
, LT
, and LE
operators, SORT command, and the SORTLINES function.
Within a session, you can dynamically modify the value of this option using the OLAP DML syntax show below or by using the SQL statementALTER SESSION
SET
option =
value.
Data Type
TEXT
Syntax
NLS_SORT = option-value
Arguments
See Oracle Database Globalization Support Guide for more information about the NLS_SORT parameter.
Examples
Example 6-78 Binary and Linguistic Sorts
A dimension named words
has the following values.
cerveza, Colorado, cheremoya, llama, luna, lago
This example shows the results of a binary sort.
NLS_SORT = 'BINARY'
SORT words A words
STATUS words
The current status of WORDS is:
Colorado, cerveza, cheremoya, lago, llama, luna
A Spanish language sort results in this order.
NLS_SORT = 'SPANISH'
SORT words A words
STATUS words
The current status of WORDS is:
cerveza, cheremoya, Colorado, lago, llama, luna
An extended Spanish language sort results in this order.
NLS_SORT = 'XSPANISH'
SORT words A words
STATUS words
The current status of WORDS is:
cerveza TO cheremoya, lago TO llama
Page 128 of 604
NLS_SORT specifies the collating sequence for ORDER BY queries.
- 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.
The value of NLS_COMP
affects the comparison behavior of SQL operations.
You can use NLS_COMP
to avoid the cumbersome process of using the NLSSORT
function in SQL statements when you want to perform a linguistic comparison instead of a binary comparison. When NLS_COMP
is set to LINGUISTIC
, SQL operations perform a linguistic comparison based on the value of NLS_SORT
. A setting of ANSI
is for backward compatibility; in general, you should set NLS_COMP
to LINGUISTIC
when you want to perform a linguistic comparison.
Set NLS_COMP
to LINGUISTIC
as follows:
ALTER SESSION SET NLS_COMP = LINGUISTIC;
When NLS_COMP
is set to LINGUISTIC
, a linguistic index improves the performance of the linguistic comparison. To enable a linguistic index, use the following syntax:
CREATE INDEX i ON t(NLSSORT(col, 'NLS_SORT=FRENCH'));
NLS_SORT
http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_options073.htm
http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams145.htm
NLS_COMP
http://docs.oracle.com/cd/B28359_01/server.111/b28298/ch3globenv.htm#NLSPG231
相关推荐
9. **NLS_COMP 和 NLS_SORT**:这两个参数影响Oracle如何比较和排序数据。当`NLS_COMP=LINGUISTIC`,`NLS_SORT=BINARY_CI`时,比较将是不区分大小写的。 10. **字符串修剪**:`RTRIM(LTRIM(x,'xxx'),'xxx')`用于...
| NLS_SORT | BINARY | | NLS_TIME_FORMAT | HH.MI.SSXFFAM | | NLS_TIMESTAMP_FMT | DD-MON-RRHH.MI.SSXFFAM | | NLS_TIME_TZ_FMT | HH.MI.SSXFFAMTZR | | NLS_TS_TZ_FMT | DD-MON-RRHH.MI.SSXFFAMTZR | | NLS_DUAL...
可以通过`ALTER SESSION SET NLS_SORT=BINARY_CI`和`ALTER SESSION SET NLS_COMP=LINGUISTIC`来调整排序规则,使比较时不考虑字符集差异。 通过以上步骤,大部分情况下可以解决Oracle客户端PL/SQL的中文乱码问题。...
- **大小写和重音差异处理**:使用 `NLS_COMP` 和 `NLS_SORT` 参数来控制字符串比较时是否忽略大小写和重音差异。 - **获取全球化配置信息**:可以使用 `DBMS_GLOBALIZATION.get_parameter_value()` 函数获取当前的...
将sort_area_size设置为一个较大的值,比如100M 2.避免日志切换等待 增加重做日志组的数量,增大日志文件大小. 3.优化日志缓冲区 比如将log_buffer容量扩大10倍(最大不要超过5M) 4.使用阵列插入与提交 commit = y 注意...
Oracle sort – 用于设置Oracle的NLS_SORT参数 自动填充原始值 – 对于自动填充原始(数据库)值而不是查找值 多文件上载分隔符 – 指定用于分隔文件名的文件上载分隔符 将Colorbox用于图像 使用响应式布局 使用移动...
可以使用 NLS_COMP 指定必须根据NLS_SORT 会话参数进行语言比较。 值范围: Oracle8i National Language Support Guide 中指定的任何有效的10 字节字符串。 默认值: BINARY nls_currency: 说明: 为 L 数字格式...
在全局化设置的环境中,如NLS_COMP或NLS_SORT参数影响下,使用字符串作为索引时需要额外的处理。 联合数组是FORALL语句和BULK COLLECT子句的重要组成部分,这些特性支持批量操作,提高处理效率。例如,BULK COLLECT...
递归工作栈中含调用语句行号 adr、变参 nls 和值参 ls。 15. 创建广义表的存储结构 图示窗口显示广义表存储结构的建立过程和算法执行过程中参数Sub的当前值。 16. 遍历二叉树 图示窗口显示二叉树的逻辑结构和遍历...
递归工作栈中含调用语句行号 adr、变参 nls 和值参 ls。 15. 创建广义表的存储结构 图示窗口显示广义表存储结构的建立过程和算法执行过程中参数Sub的当前值。 16. 遍历二叉树 图示窗口显示二叉树的逻辑结构和遍历...
递归工作栈中含调用语句行号 adr、变参 nls 和值参 ls。 15. 创建广义表的存储结构 图示窗口显示广义表存储结构的建立过程和算法执行过程中参数Sub的当前值。 16. 遍历二叉树 图示窗口显示二叉树的逻辑结构和...