NLS_LENGTH_SEMANTICS参数对VARCHAR2类型的字段的影响

服务器端字符集为：AL32UTF8

1.探讨 NLS_LENGTH_SEMANTICS 值为 CHAR 和 BYTE 时，对 VARCHAR2 类型字段的影响。

SQL> alter session set NLS_LENGTH_SEMANTICS=’CHAR’;
Session altered

SQL> show parameter nls_length;
NAME TYPE VALUE

---

nls_length_semantics string CHAR

SQL> create table TEST2 (NAME varchar2(20));
SQL>
Table created

SQL> insert into test2 values(RPAD(‘汉’, 40, ‘字’));
1 row inserted

SQL> select * from test2;

NAME

汉字字字字字字字字字字字字字字字字字字字

SQL> insert into test2 values(RPAD(‘汉’, 42, ‘字’));
insert into test2 values(RPAD(‘汉’, 42, ‘字’))
ORA-12899: value too large for column “PHARMACODIA”.”TEST2”.”NAME” (actual: 21, maximum: 20)

SQL> alter session set NLS_LENGTH_SEMANTICS=’BYTE’;
Session altered

SQL> show parameter nls_length;
NAME TYPE VALUE

---

nls_length_semantics string BYTE

SQL> drop table test2;
Table dropped

SQL> create table TEST2 (NAME varchar2(20));
Table created

SQL> insert into test2 values(RPAD(‘汉’, 40, ‘字’));
insert into test2 values(RPAD(‘汉’, 40, ‘字’))
ORA-12899: value too large for column “PHARMACODIA”.”TEST2”.”NAME” (actual: 60, maximum: 20)

SQL>
结论：NLS_LENGTH_SEMANTICS=’CHAR’时，按照字符进行处理。
NLS_LENGTH_SEMANTICS=’BYTE’时，按照字节进行处理（字符集为 AL32UTF8 时，一个汉字占3个字节）。

2.探讨 NLS_LENGTH_SEMANTICS 值为 CHAR 和 BYTE 时，对 NVARCHAR2 类型字段的影响。

SQL> drop table test2;
Table dropped

SQL> alter session set NLS_LENGTH_SEMANTICS=’BYTE’;
Session altered

SQL> show parameter nls_length;
NAME TYPE VALUE

---

nls_length_semantics string BYTE

SQL> create table TEST2 (NAME nvarchar2(20));
Table created

SQL> insert into test2 values(RPAD(‘汉’, 40, ‘字’));
1 row inserted

SQL> insert into test2 values(RPAD(‘汉’, 42, ‘字’));
insert into test2 values(RPAD(‘汉’, 42, ‘字’))
ORA-12899: value too large for column “PHARMACODIA”.”TEST2”.”NAME” (actual: 21, maximum: 20)

SQL> drop table test2;
Table dropped

SQL> alter session set NLS_LENGTH_SEMANTICS=’CHAR’;
Session altered

SQL> show parameter nls_length;
NAME TYPE VALUE

---

nls_length_semantics string CHAR

SQL> create table TEST2 (NAME nvarchar2(20));
Table created

SQL> insert into test2 values(RPAD(‘汉’, 40, ‘字’));
1 row inserted

SQL> insert into test2 values(RPAD(‘汉’, 42, ‘字’));
insert into test2 values(RPAD(‘汉’, 42, ‘字’))
ORA-12899: value too large for column “PHARMACODIA”.”TEST2”.”NAME” (actual: 21, maximum: 20)

SQL>
结论：对 NVARCHAR2 不影响。

最终结论：
NLS_LENGTH_SEMANTICS=’CHAR’时，VARCHAR2 类型字段按照字符进行处理。
NLS_LENGTH_SEMANTICS=’BYTE’时，VARCHAR2 类型字段按照字节进行处理（字符集为 AL32UTF8 时，一个汉字占3个字节）。
而 NVARCHAR2 类型的字段完全不受影响，都是按照字符类型处理的。