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'插入