验证ORACLE不同字符集中汉字占用的byte及NLS_LENGTH_SEMANTICS参数的影响

109 阅读 0 评论 72 点赞

我是靠谱客的博主完美画板，这篇文章主要介绍验证ORACLE不同字符集中汉字占用的byte及NLS_LENGTH_SEMANTICS参数的影响，现在分享给大家，希望可以做个参考。

官方文档中关于NLS_LENGTH_SEMANTICS参数介绍：
可选值是BYTE | CHAR，默认是BYTE，可以在数据库和会话级动态修改。也可以在客户端环境变量中进行设置(不适合JDBC瘦客户端)
NCHAR, NVARCHAR2, CLOB, and NCLOB 始终是基于BYTE。
以SYS用户登陆时不考虑NLS_LENGTH_SEMANTICS参数统一使用BYTE；除非在创建对象等DDL语句中明确定义。
ORACLE强烈建议不能在数据库实例级别设置此参数。
----
AL32UTF8字符集时一个汉字占三个byte
ZHS16GBK字符集时一个汉字占两个byte

本实验结论：
1.创建表时指定字段类型为 varchar2(10)时--即不指定使用的类型，字段使用的dba_tab_columns视图的char_used字段值等于创建表时会话级的nls_length_semantics=BYTE
2.在AL32UTF8字符集时一个汉字是3个BYTE，ZHS16GBK字符集时一个汉字占两个byte。
3.如果字段类型为 varchar2(10)为BYTE(dba_tab_columns视图char_used字段是B(BYTE))，能存放三个汉字--->一个汉字是3个BYTE
4.如果字段类型为 varchar2(10)为char(dba_tab_columns视图char_used字段是C(CHAR))，能存放十个汉字--->一个汉字是3个BYTE
5.以SYS用户登陆时不考虑NLS_LENGTH_SEMANTICS参数统一使用BYTE；除非在创建对象等DDL语句中明确定义。
6.在系统或会话级修改nls_length_semantics=char参数值，不会对已有表中varchar2字段的char_used进行修改

实验的步骤：
实验1-4使用AL32UTF8的数据库进行测试。
实验1：默认nls_length_semantics=BYTE且指定字段类型为 varchar2(10)
此时从dba_tab_columns视图的char_used字段可以发现使用的是B(BYTE)，在AL32UTF8字符集下一个汉字是3个BYTE。
实验2：修改nls_length_semantics=CHAR且指定字段类型为 varchar2(10)
此时从dba_tab_columns视图的char_used字段可以发现使用的是C(CHAR)，此时可以插入10个字符。
实验3：alter system set nls_length_semantics=char;参数不会对已有的表中varchar2字段的char_used进行修改
实验4：测试以SYS用户登陆时不考虑NLS_LENGTH_SEMANTICS参数统一使用BYTE；除非在创建对象等DDL语句中明确定义。
实验5：测试ZHS16GBK字符集时一个汉字占的字符数

具体实验过程
#################################################################
实验1：
默认nls_length_semantics=BYTE且指定字段类型为 varchar2(10)
此时从dba_tab_columns视图的char_used字段可以发现使用的是B(BYTE)，在AL32UTF8字符集下一个汉字是3个BYTE。

复制代码

SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
SQL> select lengthb('白') from dual;
LENGTHB('白')
-------------
3
SQL> show user
USER is "BYS"
SQL> show parameter nls_length_semantics;
NAME
TYPE
VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics
string
BYTE
SQL> create table t1(id number,name varchar2(10));
Table created.
SQL> create table t4(id number,name varchar2(10 char));
Table created.
SQL> select table_name,column_name,data_type,char_used from dba_tab_columns where table_name='T1' or table_name='T4' ;
TABLE_NAME
COLUMN_NAME
DATA_TYPE
C
------------------------------ ------------------------------ ---------- -
T4
NAME
VARCHAR2
C
T4
ID
NUMBER
T1
NAME
VARCHAR2
B
T1
ID
NUMBER
SQL> desc t1
Name
Null?
Type
----------------------------------------- -------- ----------------------------
ID
NUMBER
NAME
VARCHAR2(10)
在SQL*PLUS窗口输入时--经过了字符转换（猜测是主机的）显示是5个汉字30个字符
SQL> insert into t1 values(1,'浙江省杭州');
insert into t1 values(1,'浙江省杭州')
*
ERROR at line 1:
ORA-12899: value too large for column "BYS"."T1"."NAME" (actual: 30, maximum: 10)
SQL> insert into t1 values(1,'浙江杭州');
在PL/SQL DEVELOPER工具窗口：
SQL> insert into t1 values(1,'浙江省杭州');
insert into t1 values(1,'浙江省杭州')
ORA-12899: 列 "BYS"."T1"."NAME" 的值太大 (实际值: 15, 最大值: 10)
SQL> insert into t1 values(1,'浙江杭州');
insert into t1 values(1,'浙江杭州')
ORA-12899: 列 "BYS"."T1"."NAME" 的值太大 (实际值: 12, 最大值: 10)
SQL> insert into t1 values(1,'浙江省');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t1;
ID NAME
---------- ----------
1 浙江省
SQL> insert into t4 values(1,'浙江省杭州市西湖区西湖');
insert into t4 values(1,'浙江省杭州市西湖区西湖')
ORA-12899: 列 "BYS"."T4"."NAME" 的值太大 (实际值: 11, 最大值: 10)
SQL> insert into t4 values(1,'浙江省杭州市西湖区西');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t4;
ID NAME
---------- ----------------------------------------
1 浙江省杭州
1 浙江省杭州市西湖区西

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
SQL> select lengthb('白') from dual;
LENGTHB('白')
-------------
3
SQL> show user
USER is "BYS"
SQL> show parameter nls_length_semantics;
NAME
TYPE
VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics
string
BYTE
SQL> create table t1(id number,name varchar2(10));
Table created.
SQL> create table t4(id number,name varchar2(10 char));
Table created.
SQL> select table_name,column_name,data_type,char_used from dba_tab_columns where table_name='T1' or table_name='T4' ;
TABLE_NAME
COLUMN_NAME
DATA_TYPE
C
------------------------------ ------------------------------ ---------- -
T4
NAME
VARCHAR2
C
T4
ID
NUMBER
T1
NAME
VARCHAR2
B
T1
ID
NUMBER
SQL> desc t1
Name
Null?
Type
----------------------------------------- -------- ----------------------------
ID
NUMBER
NAME
VARCHAR2(10)
在SQL*PLUS窗口输入时--经过了字符转换（猜测是主机的）显示是5个汉字30个字符
SQL> insert into t1 values(1,'浙江省杭州');
insert into t1 values(1,'浙江省杭州')
*
ERROR at line 1:
ORA-12899: value too large for column "BYS"."T1"."NAME" (actual: 30, maximum: 10)
SQL> insert into t1 values(1,'浙江杭州');
在PL/SQL DEVELOPER工具窗口：
SQL> insert into t1 values(1,'浙江省杭州');
insert into t1 values(1,'浙江省杭州')
ORA-12899: 列 "BYS"."T1"."NAME" 的值太大 (实际值: 15, 最大值: 10)
SQL> insert into t1 values(1,'浙江杭州');
insert into t1 values(1,'浙江杭州')
ORA-12899: 列 "BYS"."T1"."NAME" 的值太大 (实际值: 12, 最大值: 10)
SQL> insert into t1 values(1,'浙江省');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t1;
ID NAME
---------- ----------
1 浙江省
SQL> insert into t4 values(1,'浙江省杭州市西湖区西湖');
insert into t4 values(1,'浙江省杭州市西湖区西湖')
ORA-12899: 列 "BYS"."T4"."NAME" 的值太大 (实际值: 11, 最大值: 10)
SQL> insert into t4 values(1,'浙江省杭州市西湖区西');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t4;
ID NAME
---------- ----------------------------------------
1 浙江省杭州
1 浙江省杭州市西湖区西

#####################################################################
实验2：
修改nls_length_semantics=CHAR且指定字段类型为 varchar2(10)
此时从dba_tab_columns视图的char_used字段可以发现使用的是C(CHAR)，此时可以插入10个字符。

复制代码

SQL> alter session set nls_length_semantics=CHAR;
Session altered.
SQL> show parameter nls_length_semantics;
NAME
TYPE
VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics
string
CHAR
SQL> create table t2(id number,name varchar2(10));
Table created.
SQL> create table t3(id number,name varchar2(10 char));
Table created.
SQL> create table t6(id number,name varchar2(10 byte));
Table created.
SQL> select table_name,column_name,data_type,char_used from dba_tab_columns where table_name='T3' or table_name='T2' or table_name='T6' ;
TABLE_NAME
COLUMN_NAME
DATA_TYPE
C
------------------------------ ------------------------------ ---------- -
T6
NAME
VARCHAR2
B
T6
ID
NUMBER
T3
NAME
VARCHAR2
C
T3
ID
NUMBER
T2
NAME
VARCHAR2
C
T2
ID
NUMBER
在PL/SQL DEVELOPER工具窗口：
SQL> insert into t2 values(1,'浙江省杭州市江干区下沙');
insert into t2 values(1,'浙江省杭州市江干区下沙')
ORA-12899: 列 "BYS"."T2"."NAME" 的值太大 (实际值: 11, 最大值: 10)
SQL> insert into t2 values(1,'浙江省杭州市江干区下');
1 row inserted
SQL> insert into t2 values(1,'浙江省杭州市江干区');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t2;
ID NAME
---------- ----------------------------------------
1 浙江省杭州市江干区下
1 浙江省杭州市江干区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
SQL> alter session set nls_length_semantics=CHAR;
Session altered.
SQL> show parameter nls_length_semantics;
NAME
TYPE
VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics
string
CHAR
SQL> create table t2(id number,name varchar2(10));
Table created.
SQL> create table t3(id number,name varchar2(10 char));
Table created.
SQL> create table t6(id number,name varchar2(10 byte));
Table created.
SQL> select table_name,column_name,data_type,char_used from dba_tab_columns where table_name='T3' or table_name='T2' or table_name='T6' ;
TABLE_NAME
COLUMN_NAME
DATA_TYPE
C
------------------------------ ------------------------------ ---------- -
T6
NAME
VARCHAR2
B
T6
ID
NUMBER
T3
NAME
VARCHAR2
C
T3
ID
NUMBER
T2
NAME
VARCHAR2
C
T2
ID
NUMBER
在PL/SQL DEVELOPER工具窗口：
SQL> insert into t2 values(1,'浙江省杭州市江干区下沙');
insert into t2 values(1,'浙江省杭州市江干区下沙')
ORA-12899: 列 "BYS"."T2"."NAME" 的值太大 (实际值: 11, 最大值: 10)
SQL> insert into t2 values(1,'浙江省杭州市江干区下');
1 row inserted
SQL> insert into t2 values(1,'浙江省杭州市江干区');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t2;
ID NAME
---------- ----------------------------------------
1 浙江省杭州市江干区下
1 浙江省杭州市江干区

################################################################
实验3：alter system set nls_length_semantics=char;参数不会对原有的表中varchar2字段的char_used进行修改

复制代码

SQL> select table_name,column_name,data_type,char_used from dba_tab_columns where table_name='T1' or table_name='T4' ;
TABLE_NAME
COLUMN_NAME
DATA_TYPE
C
------------------------------ ------------------------------ ---------- -
T4
NAME
VARCHAR2
C
T4
ID
NUMBER
T1
NAME
VARCHAR2
B
T1
ID
NUMBER
SQL> alter system set nls_length_semantics=char;
System altered.
SQL> select table_name,column_name,data_type,char_used from dba_tab_columns where table_name='T1' or table_name='T4' ;
TABLE_NAME
COLUMN_NAME
DATA_TYPE
C
------------------------------ ------------------------------ ---------- -
T1
ID
NUMBER
T1
NAME
VARCHAR2
B
T4
ID
NUMBER
T4
NAME
VARCHAR2
C
此时对T1进行插入操作，超过3个汉字会报错
SQL> insert into t1 values(11,'浙江省美');
 
insert into t1 values(11,'浙江省美')
 
ORA-12899: value too large for column "BYS"."T1"."NAME" (actual: 12, maximum: 10)
实验4：测试以SYS用户登陆时不考虑NLS_LENGTH_SEMANTICS参数统一使用BYTE；除非在创建对象等DDL语句中明确定义。
SQL> show user
USER is "SYS"
SQL>
show parameter nls_length_semantics;
NAME
TYPE
VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics
string
BYTE
SQL> SQL> alter system set nls_length_semantics=char;
System altered.
SQL>
show parameter nls_length_semantics;
NAME
TYPE
VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics
string
CHAR
SQL> create table bys.t8(id number,name varchar2(10));
Table created.
SQL> create table bys.t9(id number,name varchar2(10 char));
Table created.
SQL> col data_type for a10
set linesize 160
SQL> SQL> select table_name,column_name,data_type,char_used from dba_tab_columns where table_name='T8' or table_name='T9' ;
TABLE_NAME
COLUMN_NAME
DATA_TYPE
C
------------------------------ ------------------------------ ---------- -
T8
ID
NUMBER
T8
NAME
VARCHAR2
B
T9
ID
NUMBER
T9
NAME
VARCHAR2
C

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
SQL> select table_name,column_name,data_type,char_used from dba_tab_columns where table_name='T1' or table_name='T4' ;
TABLE_NAME
COLUMN_NAME
DATA_TYPE
C
------------------------------ ------------------------------ ---------- -
T4
NAME
VARCHAR2
C
T4
ID
NUMBER
T1
NAME
VARCHAR2
B
T1
ID
NUMBER
SQL> alter system set nls_length_semantics=char;
System altered.
SQL> select table_name,column_name,data_type,char_used from dba_tab_columns where table_name='T1' or table_name='T4' ;
TABLE_NAME
COLUMN_NAME
DATA_TYPE
C
------------------------------ ------------------------------ ---------- -
T1
ID
NUMBER
T1
NAME
VARCHAR2
B
T4
ID
NUMBER
T4
NAME
VARCHAR2
C
此时对T1进行插入操作，超过3个汉字会报错
SQL> insert into t1 values(11,'浙江省美');
 
insert into t1 values(11,'浙江省美')
 
ORA-12899: value too large for column "BYS"."T1"."NAME" (actual: 12, maximum: 10)
实验4：测试以SYS用户登陆时不考虑NLS_LENGTH_SEMANTICS参数统一使用BYTE；除非在创建对象等DDL语句中明确定义。
SQL> show user
USER is "SYS"
SQL>
show parameter nls_length_semantics;
NAME
TYPE
VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics
string
BYTE
SQL> SQL> alter system set nls_length_semantics=char;
System altered.
SQL>
show parameter nls_length_semantics;
NAME
TYPE
VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics
string
CHAR
SQL> create table bys.t8(id number,name varchar2(10));
Table created.
SQL> create table bys.t9(id number,name varchar2(10 char));
Table created.
SQL> col data_type for a10
set linesize 160
SQL> SQL> select table_name,column_name,data_type,char_used from dba_tab_columns where table_name='T8' or table_name='T9' ;
TABLE_NAME
COLUMN_NAME
DATA_TYPE
C
------------------------------ ------------------------------ ---------- -
T8
ID
NUMBER
T8
NAME
VARCHAR2
B
T9
ID
NUMBER
T9
NAME
VARCHAR2
C

##########################################################
实验5：测试ZHS16GBK字符集时一个汉字占的字符数

复制代码

SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
SQL> show user
USER is "BYS"
SQL> show parameter nls_length_semantics;
NAME
TYPE
VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics
string
BYTE
SQL> create table t1(id number,name varchar2(8));
Table created.
SQL> create table t2(id number,name varchar2(8 char));
Table created.
SQL>
select table_name,column_name,data_type,char_used from dba_tab_columns where table_name='T1' or table_name='T2' ;
TABLE_NAME
COLUMN_NAME
DATA_TYPE
C
------------------------------ ------------------------------ ---------- -
T2
NAME
VARCHAR2
C
T2
ID
NUMBER
T1
NAME
VARCHAR2
B
T1
ID
NUMBER
SQL> select lengthb('白') from dual;
LENGTHB('白')
-------------
2
SQL> insert into t1 values(1,'浙江省杭州');
insert into t1 values(1,'浙江省杭州')
ORA-12899: 列 "BYS"."T1"."NAME" 的值太大 (实际值: 10, 最大值: 8)
SQL> insert into t1 values(1,'浙江杭州');
1 row inserted
SQL> insert into t2 values(1,'浙江省杭州市西湖区');
insert into t2 values(1,'浙江省杭州市西湖区')
ORA-12899: 列 "BYS"."T2"."NAME" 的值太大 (实际值: 9, 最大值: 8)
SQL> insert into t2 values(1,'浙江省杭州市西湖');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t1;
ID NAME
---------- --------
1 浙江杭州
SQL> select * from t2;
ID NAME
---------- ----------------
1 浙江省杭州市西湖

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
SQL> show user
USER is "BYS"
SQL> show parameter nls_length_semantics;
NAME
TYPE
VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics
string
BYTE
SQL> create table t1(id number,name varchar2(8));
Table created.
SQL> create table t2(id number,name varchar2(8 char));
Table created.
SQL>
select table_name,column_name,data_type,char_used from dba_tab_columns where table_name='T1' or table_name='T2' ;
TABLE_NAME
COLUMN_NAME
DATA_TYPE
C
------------------------------ ------------------------------ ---------- -
T2
NAME
VARCHAR2
C
T2
ID
NUMBER
T1
NAME
VARCHAR2
B
T1
ID
NUMBER
SQL> select lengthb('白') from dual;
LENGTHB('白')
-------------
2
SQL> insert into t1 values(1,'浙江省杭州');
insert into t1 values(1,'浙江省杭州')
ORA-12899: 列 "BYS"."T1"."NAME" 的值太大 (实际值: 10, 最大值: 8)
SQL> insert into t1 values(1,'浙江杭州');
1 row inserted
SQL> insert into t2 values(1,'浙江省杭州市西湖区');
insert into t2 values(1,'浙江省杭州市西湖区')
ORA-12899: 列 "BYS"."T2"."NAME" 的值太大 (实际值: 9, 最大值: 8)
SQL> insert into t2 values(1,'浙江省杭州市西湖');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t1;
ID NAME
---------- --------
1 浙江杭州
SQL> select * from t2;
ID NAME
---------- ----------------
1 浙江省杭州市西湖