oracle 生僻字怎么处理
## 场景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'插入
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69924215/viewspace-3066443/ ，如需转载，请注明出处，否则将追究法律责任。
