关于char类型的存储

create table char_type name char(4 单位);
单位：为byte 或 char 默认值取决于参数nls_length_semantics，nls_length_semantics的默认值为BYTE
byte:字节
char:字符

一个汉字在库里为一个char，对应几个byte取决于数据库的字符集，如果是ZHS16GBK字符集，则对应2个byte，如果是WE8ISO8859P1,则对应一个byte。

实验一：看参数nls_length_semantics对存储的影响

SQL> desc char_type
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------

 NAME                                               CHAR(4 CHAR)

SQL>  insert into char_type values('ABCDE');
 insert into char_type values('ABCDE')
                              *
ERROR at line 1:
ORA-01401: inserted value too large for column


SQL>  insert into char_type values('ABCD');

1 row created.

SQL> insert into char_type values('中国汉语');

1 row created.

SQL> select name,dump(name) from char_type;

NAME
--------
DUMP(NAME)
--------------------------------------------------------------------

ABCD
Typ=96 Len=4: 65,66,67,68

中国汉语
Typ=96 Len=8: 214,208,185,250,186,186,211,239

SQL> alter table char_type modify name char(4);

Table altered.

SQL> desc char_type
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------

 NAME                                               CHAR(4)

SQL> insert into char_type values('中国汉语');
insert into char_type values('中国汉语')
                             *
ERROR at line 1:
ORA-01401: inserted value too large for column


SQL> alter session set nls_length_semantics = CHAR ;

Session altered.

SQL> desc char_type
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------

 NAME                                               CHAR(4 BYTE)

SQL> alter table char_type modify name char(4);

Table altered.

SQL> desc char_type
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------

 NAME                                               CHAR(4)

SQL> insert into char_type values('中国汉语');

1 row created.

可以通过dump来查看表中数据的存储情况
SQL> select name,dump(name) from char_type;

NAME
--------
DUMP(NAME)
--------------------------------------------------

中国汉语
Typ=96 Len=8: 214,208,185,250,186,186,211,239

实验二：查看数据库字符集对存储的影响
                 


SQL> select parameter,value from v$nls_parameters where parameter='NLS_CHARACTER
SET';

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_CHARACTERSET
WE8ISO8859P1

SQL> create table char_type (name char(4));

Table created.

SQL> insert into char_type values('中国汉语');
insert into char_type values('中国汉语')
                             *
ERROR at line 1:
ORA-01401: inserted value too large for column


SQL> insert into char_type values('中国');

1 row created.

SQL> select name,dump(name) from char_type;

NAME
----
DUMP(NAME)
------------------------------------------------------------------------------

中国
Typ=96 Len=4: 214,208,185,250

SQL> select parameter,value from v$nls_parameters where parameter='NLS_CHARACTER
SET';

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_CHARACTERSET
ZHS16GBK

SQL> alter table char_type modify name char(4 char);

Table altered.
SQL> desc char_type
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------

 NAME                                               CHAR(4 CHAR)

SQL> insert into char_type values('中国汉语');
insert into char_type values('中国汉语')
                             *
ERROR at line 1:
ORA-01401: inserted value too large for column


SQL> insert into char_type values('中国');

1 row created.

SQL> select name,dump(name) from char_type;

NAME
----
DUMP(NAME)
------------------------------------------------------------------------------

中国
Typ=96 Len=4: 214,208,185,250

中国
Typ=96 Len=4: 214,208,185,250



WE8ISO8859P1字符集即使设了存储4个char，也只能存2个汉字