oracle 遇到生僻字怎么处理

mahan

场景1： ZHS16GBK UTF8
1.查看数据库的字符集相关设置：
SQL> select * from v$nls_parameters;
NLS_LANGUAGE AMERICAN 0
NLS_TERRITORY AMERICA 0
NLS_CURRENCY $ 0
NLS_ISO_CURRENCY AMERICA 0
NLS_NUMERIC_CHARACTERS ., 0
NLS_CALENDAR GREGORIAN 0
NLS_DATE_FORMAT DD+MON+RR 0
NLS_DATE_LANGUAGE AMERICAN 0
NLS_CHARACTERSET ZHS16GBK 0
NLS_SORT BINARY 0
NLS_TIME_FORMAT HH.MI.SSXFF AM 0
NLS_TIMESTAMP_FORMAT DD+MON+RR HH.MI.SSXFF AM 0
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR 0
NLS_TIMESTAMP_TZ_FORMAT DD+MON+RR HH.MI.SSXFF AM TZR 0
NLS_DUAL_CURRENCY $ 0
NLS_NCHAR_CHARACTERSET UTF8 0
NLS_COMP BINARY 0
NLS_LENGTH_SEMANTICS BYTE 0
NLS_NCHAR_CONV_EXCP FALSE 0

NLS_CHARACTERSET++》 ZHS16GBK
NLS_NCHAR_CHARACTERSET++》 UTF8
可以看到数据库字符集和国家字符集分别如上。

2.建立测试表。设置2个字段：分别为varchar2和nvarchar2

SQL> create table char_test (name_char varchar2(100),name_nchar nvarchar2(100));

Table created.

nvarchar2会比varchar2存的中文多一些

3.插入测试数据。
insert into char_test(name_char,name_nchar) VALUES(‘㱔’,‘㱔’);
insert into char_test(name_char,name_nchar) VALUES(‘𧿹’,‘𧿹’);
insert into char_test(name_char,name_nchar) VALUES(‘㛃’,‘㛃’);

4.查询相关数据
SELECT * FROM char_test;

SQL> SELECT * FROM char_test;

结果：乱码

场景2： AL32UTF8 AL16UTF16
1.查看数据库字符集情况
SQL> set linesize 1000
SQL> set pagesize 0
SQL> select * from v$nls_parameters;
NLS_LANGUAGE AMERICAN 0
NLS_TERRITORY AMERICA 0
NLS_CURRENCY $ 0
NLS_ISO_CURRENCY AMERICA 0
NLS_NUMERIC_CHARACTERS ., 0
NLS_CALENDAR GREGORIAN 0
NLS_DATE_FORMAT DD+MON+RR 0
NLS_DATE_LANGUAGE AMERICAN 0
NLS_CHARACTERSET AL32UTF8 0
NLS_SORT BINARY 0
NLS_TIME_FORMAT HH.MI.SSXFF AM 0
NLS_TIMESTAMP_FORMAT DD+MON+RR HH.MI.SSXFF AM 0
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR 0
NLS_TIMESTAMP_TZ_FORMAT DD+MON+RR HH.MI.SSXFF AM TZR 0
NLS_DUAL_CURRENCY $ 0
NLS_NCHAR_CHARACTERSET AL16UTF16 0
NLS_COMP BINARY 0
NLS_LENGTH_SEMANTICS BYTE 0
NLS_NCHAR_CONV_EXCP FALSE 0

NLS_CHARACTERSET++》 AL32UTF8
NLS_NCHAR_CHARACTERSET++》 AL16UTF16
可以看到数据库字符集和国家字符集分别如上。

2.建立测试表。设置2个字段：分别为varchar2和nvarchar2

SQL> create table char_test (name_char varchar2(100),name_nchar nvarchar2(100));

Table created.

nvarchar2会比varchar2存的中文多一些

3.插入测试数据。
insert into char_test(name_char,name_nchar) VALUES(‘㱔’,‘㱔’);
insert into char_test(name_char,name_nchar) VALUES(‘𧿹’,‘𧿹’);
insert into char_test(name_char,name_nchar) VALUES(‘㛃’,‘㛃’);

commit;

4.查询相关数据
SELECT * FROM char_test;

SQL> SELECT * FROM char_test;

结果：乱码

场景3： AL32UTF8 AL16UTF16
将字符转换为 Unicode 再次插入

https://www.bejson.com/convert/unicode_chinese/

㱔 ++>\u3c54
𧿹 ++>\ud85f\udff9
㛃 ++>\u36c3

㱔 字的unicode编码为：\u3c54
然后，从dual中查询结果：

1、查询varchar2的结果：
select utl_raw.cast_to_varchar2(‘3c54’) from dual;
select utl_raw.cast_to_varchar2(‘d85fdff9’) from dual;
select utl_raw.cast_to_varchar2(‘36c3’) from dual;
结果：乱码

2、查询nvarchar2的结果：
select utl_raw.cast_to_nvarchar2(‘3c54’) from dual;
select utl_raw.cast_to_nvarchar2(‘d85fdff9’) from dual;
select utl_raw.cast_to_nvarchar2(‘36c3’) from dual;

结果：正常显示
测试发现，下面的nvarchar2 查询可以正常查询

insert into char_test values ((select utl_raw.cast_to_nvarchar2(‘3c54’) from dual),(select utl_raw.cast_to_nvarchar2(‘3c54’) from dual));
insert into char_test values ((select utl_raw.cast_to_nvarchar2(‘d85fdff9’) from dual),(select utl_raw.cast_to_nvarchar2(‘d85fdff9’) from dual));
insert into char_test values ((select utl_raw.cast_to_nvarchar2(‘36c3’) from dual),(select utl_raw.cast_to_nvarchar2(‘36c3’) from dual));

commit;
select * from char_test;
结果：正常显示

场景4：ZHS16GBK UTF8
将字符转换为 Unicode 再次插入

https://www.bejson.com/convert/unicode_chinese/

㱔 ++>\u3c54
𧿹 ++>\ud85f\udff9
㛃 ++>\u36c3

㱔 字的unicode编码为：\u3c54
然后，从dual中查询结果：

1、查询varchar2的结果：
select utl_raw.cast_to_varchar2(‘3c54’) from dual;
select utl_raw.cast_to_varchar2(‘d85fdff9’) from dual;
select utl_raw.cast_to_varchar2(‘36c3’) from dual;
结果： 乱码

2、查询nvarchar2的结果：
select utl_raw.cast_to_nvarchar2(‘3c54’) from dual;
select utl_raw.cast_to_nvarchar2(‘d85fdff9’) from dual;
select utl_raw.cast_to_nvarchar2(‘36c3’) from dual;
结果： 乱码

测试发现，下面的nvarchar2 查询可以正常查询

insert into char_test values ((select utl_raw.cast_to_nvarchar2(‘3c54’) from dual),(select utl_raw.cast_to_nvarchar2(‘3c54’) from dual));
insert into char_test values ((select utl_raw.cast_to_nvarchar2(‘d85fdff9’) from dual),(select utl_raw.cast_to_nvarchar2(‘d85fdff9’) from dual));
insert into char_test values ((select utl_raw.cast_to_nvarchar2(‘36c3’) from dual),(select utl_raw.cast_to_nvarchar2(‘36c3’) from dual));

commit;
select * from char_test;
结果： 乱码

场景5：ZHS16GBK UTF8
字符集ZHS16GBK 国家字符集UTF8 怎么办？

update char_test set name_nchar = N’㛃’ where name_nchar=‘z’

insert into char_test( name_nchar) VALUES(N’㱔’);

SELECT * FROM zc.char_test;
㛃
𧿹
㱔
查询正常

这个时候表里面的数据是正常的。

场景6： AL32UTF8 AL16UTF16

update char_test set name_nchar = N’㛃’ where name_nchar=‘z’

insert into char_test( name_nchar) VALUES(N’㱔’);

SELECT * FROM zc.char_test;
㛃
𧿹
㱔
查询正常

总结：
1 字段要使用 nvarchar2类型
2 AL32UTF8 AL16UTF16 要使用 cast_to_nvarchar2 进行插入或者 使用nvarchar2类型通过N 插入
3 ZHS16GBK UTF8 使用nvarchar2类型通过N’XXX’插入