[20221012]简单探究nvarchar2数据类型存储.txt

[20221012]简单探究nvarchar2数据类型存储.txt --//自己对于nvarchar2数据类型不是很了解,简单探究看看. --//我以前仅仅知道数字与字母占用2个字符,比如A ascii码65(0x41),如果采用nvarchar2,存储变成0x00 0x41. --//另外nvarchar2数据类型我不知道是否与字符集有关,顺便也做一个探究. 1.环境: $ env | grep NLS NLS_LANG=AMERICAN_AMERICA.ZHS16GBK NLS_TIME_TZ_FORMATx=HH24.MI.SSXFF TZH:TZM NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF TZH:TZM NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production CREATE TABLE t1 AS    SELECT object_id          ,object_name          ,CAST (object_name AS NVARCHAR2 (128)) vobject_name      FROM all_objects where rownum<=3; SCOTT@book> alter table t1 modify VOBJECT_NAME  not null ; Table altered. update t1 set object_name='测试中文',vobject_name='测试中文' where rownum=1; commit ; alter system checkpoint; alter system checkpoint; SCOTT@book> select rowid from t1 where rownum=1; ROWID ------------------ AABR50AAEAAABHjAAA SCOTT@book> @ rowid AABR50AAEAAABHjAAA     OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT ---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------     335476          4       4579          0  0x10011E3           4,4579               alter system dump datafile 4 block 4579 2.通过bbed观察: $ export NLS_LANG=AMERICAN_AMERICA.US7SACII --//方便bbed查看中文汉字. BBED> set dba   4,4579         DBA             0x010011e3 (16781795 4,4579) BBED> x /rncx *kdbr[1] rowdata[44]                                 @8136 ----------- flag@8136: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8137: 0x00 cols@8138:    3 col    0[2] @8139: 46 col    1[7] @8142: I_USER1 col   2[14] @8150:  0x00  0x49  0x00  0x5f  0x00  0x55  0x00  0x53  0x00  0x45  0x00  0x52  0x00  0x31 BBED> x /rnxx *kdbr[1] rowdata[44]                                 @8136 ----------- flag@8136: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8137: 0x00 cols@8138:    3 col    0[2] @8139: 46 col    1[7] @8142:  0x49  0x5f  0x55  0x53  0x45  0x52  0x31 col   2[14] @8150:  0x00  0x49  0x00  0x5f  0x00  0x55  0x00  0x53  0x00  0x45  0x00  0x52  0x00  0x31 --//你可以发现英文字符nvarchar2编码前面每个多了0x00,这样占用空间会加倍. BBED> x /rncx *kdbr[0] rowdata[0]                                  @8092 ---------- flag@8092: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8093: 0x02 cols@8094:    3 col    0[2] @8095: 20 col    1[8] @8098: 测试中文 col    2[8] @8107:  0x6d  0x4b  0x8b  0xd5  0x4e  0x2d  0x65  0x87 BBED> x /rnxx *kdbr[0] rowdata[0]                                  @8092 ---------- flag@8092: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8093: 0x02 cols@8094:    3 col    0[2] @8095: 20 col    1[8] @8098:  0xb2  0xe2  0xca  0xd4  0xd6  0xd0  0xce  0xc4 col    2[8] @8107:  0x6d  0x4b  0x8b  0xd5  0x4e  0x2d  0x65  0x87 ---//而中文汉字的编码nvarchar2有点奇怪. 3.我看了我以前建立测试数据库的脚本,执行如下: # mkdir -p /mnt/ramdisk # mount -t tmpfs -o size=8G tmpfs /mnt/ramdisk # su - oracle --//检查环境变量是否设置正确 $ env | grep -i oracle USER=oracle LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/db_1/lib:/u01/app/oracle/product/11.2.0/db_1/oracm/lib:/lib:/usr/lib:/usr/local/lib:/lib64:/usr/lib64:/usr/local/lib64 ORACLE_BASE=/u01/app/oracle PATH=.:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/NX/bin:/home/oracle/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/db_1/bin HOME=/home/oracle LOGNAME=oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 $ echo $NLS_LANG AMERICAN_AMERICA.zhs16gbk $ export ORACLE_SID=book $ORACLE_HOME/bin/dbca -createDatabase -templateName General_Purpose.dbc -gdbName test -sid test -sysPassword oracle \ -systemPassword oracle -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -listeners LISTENER -sampleSchema  true -totalMemory 800 \ -databaseType MULTIPURPOSE -silent -datafileDestination /mnt/ramdisk --//-characterSet ZHS16GBK -nationalCharacterSet AL16UTF16,我建立的生产系统也是使用这样的字符集. --//感觉这个nvarchar2类型与-nationalCharacterSet AL16UTF16有关.换一个参数看看.另外写一篇blog.