在Oracle定義變量時(shí)，常有VARCHAR2 (3 Char)或者VARCHAR2 (10 Byte)的數(shù)據(jù)類(lèi)型，那么3char或者10Byte到底代表幾個(gè)漢字，幾個(gè)字符呢，上次外公司一同事討論這個(gè)問(wèn)題，一下沒(méi)給解釋清楚，所以下來(lái)以后整理如下：
總結(jié)：
當(dāng)NLS_CHARACTERSET=AL32UTF8時(shí)（）
NLS_LENGTH_SEMANTICS=BYTE時(shí)，一個(gè)漢字代表三個(gè)字節(jié)
NLS_LENGTH_SEMANTICS=CHAR時(shí)，一個(gè)漢字代表一個(gè)字節(jié)
當(dāng)NLS_CHARACTERSET=US7ASCII時(shí)（字符集為單字節(jié)）
NLS_LENGTH_SEMANTICS=BYTE時(shí)，一個(gè)漢字代表兩個(gè)字節(jié)
NLS_LENGTH_SEMANTICS=CHAR時(shí)，一個(gè)漢字代表兩個(gè)字節(jié)
現(xiàn)象:
select * from nls_database_parameters;
…. …………..
NLS_CHARACTERSET AL32UTF8
…..
NLS_LENGTH_SEMANTICS BYTE
….
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.4.0
SQL> alter session set nls_length_semantics='BYTE';
SQL> create table nls_byte(c1 varchar2(7));
SQL> insert into nls_byte values('測(cè)試機(jī)');
insert into nls_byte values('測(cè)試機(jī)')
ORA-12899: 列 "SYS"."NLS_BYTE"."C1" 的值太大 (實(shí)際值: 9, 最大值: 7)
SQL> insert into nls_byte values('測(cè)試a');
1 row inserted
SQL> select table_name,column_name,t.DATA_TYPE,t.DATA_LENGTH,t.CHAR_USED from user_tab_columns t where table_name='NLS_BYTE';
TABLE_NAME COLU DATA_TYP DATA_LENGTH CHAR_USED
---------- ---- -------- ----------- ---------
NLS_BYTE C1 VARCHAR2 7 B
NLS_LENGTH_SEMANTICS allows you to specify the length of a column datatype in terms of CHARacters rather than in terms of BYTEs. Typically this is when using an AL32UTF8 or other varying width NLS_CHARACTERSET database where one character is not always one byte. While using CHAR semantics has as such no added value in a 7/8 bit characterset it's fully supported so any application code / table setup using CHAR can also be used in a 7/8bit characterset like US7ASCII/WE8MSWIN1252.
This parameter is a 9i (and up) feature and is not available in older releases
翻譯過(guò)來(lái)就是:這個(gè)參數(shù)允許將列的數(shù)據(jù)單位設(shè)為字符而不是byte.這個(gè)問(wèn)題會(huì)在字符集設(shè)為UTF8的時(shí)候出現(xiàn). 此參數(shù)在9i以上版本有效.
NLS_LENGTH_SEMANTICS 設(shè)置.
1. NLS_DATABASE_PARAMETERS中的值是在數(shù)據(jù)庫(kù)創(chuàng)建的時(shí)候確定的,一般都為BYTE
2. 此參數(shù)可以以 “ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR scope=both”方式修改,但是需要重啟數(shù)據(jù)庫(kù)才能生效.
3. 也可用” ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR”使對(duì)當(dāng)前session生效.
4. 此參數(shù)可以在10G以上版本中,在環(huán)境變量或注冊(cè)表中設(shè)置(注意需要大寫(xiě)),設(shè)定后從當(dāng)前客戶(hù)端啟動(dòng)的所有會(huì)話都采用新的取值.
5. 修改后只對(duì)新建的列生效,對(duì)于已有的列沒(méi)有作用
6. 新建或升級(jí)DB時(shí)用BYTE,否則XDB或dba_tables會(huì)出現(xiàn)問(wèn)題.
7. NLS_LENGTH_SEMANTICS對(duì)sys用戶(hù)下的對(duì)象無(wú)效.
8. 如果對(duì)于7/8bit的字符集,設(shè)為byte/char意義不大,因?yàn)闊o(wú)論是char和byte都對(duì)應(yīng)一個(gè)byte.
測(cè)試:
一.在當(dāng)前session中修改此參數(shù)
SQL> alter session set nls_length_semantics='char';
Session altered
SQL> create table nls_char(c1 varchar2(7),c2 varchar2(7));
Table created
SQL> desc nls_char
Name Type Nullable Default Comments
---- ----------- -------- ------- --------
C1 VARCHAR2(7) Y
C2 VARCHAR2(7) Y
SQL> insert into nls_char values('測(cè)試機(jī)','測(cè)試測(cè)試測(cè)試');
1 row inserted
如果對(duì)于alter system,效果是一樣的
二.對(duì)于已經(jīng)存在的表,
SQL> desc nls_byte
Name Type Nullable Default Comments
---- ---------------- -------- ------- --------
C1 VARCHAR2(7 BYTE) Y
SQL> alter table nls_byte modify c1 varchar2(7 char);
Table altered
SQL> desc nls_byte
Name Type Nullable Default Comments
---- ----------- -------- ------- --------
C1 VARCHAR2(7) Y
SQL> insert into nls_byte values('測(cè)試機(jī)');
1 row inserted
1. exp/imp : 不能直接導(dǎo)入,因?yàn)闀?huì)采用source table的建表方式在target db里建表,即使目標(biāo)庫(kù)設(shè)的值為char.
*可以預(yù)先在目標(biāo)庫(kù)中以char方式建表
*然后導(dǎo)入,指定參數(shù)ignore=y
2. Alter table
alter table "<owner>"."<table>" modify "<column>" char (10 char);
創(chuàng)建腳本,修改列設(shè)定.
注:
Bug-3611750, ora-01450 online rebuild of index fails, 可以在重建索引前指定byte, 10.2.0.5以上已經(jīng)修復(fù)
Bug 1488174 UNICODE: ALTER SYSTEM SET NLS_LENGTH_SEMANTICS DOESN'T
TAKE EFFECT, 用此語(yǔ)句修改后,實(shí)際上不起作用,需要重啟才能生效, 但是如果用alter session方式即時(shí)生效,不用重啟.
進(jìn)一步測(cè)試,在另一個(gè)字符集設(shè)為us7ascii的DB設(shè)置此參數(shù)
SQL> select * from nls_database_parameters
6 NLS_CHARACTERSET US7ASCII
SQL> alter session set nls_length_semantics=byte;
Session altered.
SQL> create table nls_byte(c1 varchar2(7));
Table created.
SQL> insert into nls_byte values('測(cè)試測(cè)試');
insert into nls_byte values('測(cè)試測(cè)試')
ERROR at line 1:
ORA-12899: value too large for column "TEA"."NLS_BYTE"."C1" (actual: 8,
maximum: 7)
SQL> desc nls_byte
Name Null? Type
-------------------------------
C1 VARCHAR2(7)
SQL> alter session set nls_length_semantics=char;
Session altered.
SQL> create table nls_char(c1 varchar2(7));
Table created.
SQL> insert into nls_char values('測(cè)試測(cè)試');
insert into nls_char values('測(cè)試測(cè)試')
ERROR at line 1:
ORA-12899: value too large for column "TEA"."NLS_CHAR"."C1" (actual: 8,
maximum: 7)
SQL> desc nls_char
Name Null? Type
----------------------------------------- -------- -------------------
C1 VARCHAR2(7)
可以看出,在字符集為單字節(jié)的情況下,無(wú)論取何值,漢字都是以二個(gè)字節(jié)的方式存在的.
本文轉(zhuǎn)自http://www.wudeyao.com/post-69.html