Oracle存储生僻字乱码问题
👉 本文共约1227个字，系统预计阅读时间或需5分钟。
现象
Oracle数据库字符集为ZHS16GBK，插入和查询生僻字显示乱码：
查询乱码：
解决办法
先问问chatGPT，找找答案：
解决办法：
- 一种是将生僻字的数据类型由varchar2改成nvarchar2，并使用
utl_raw.cast_to_nvarchar2函数来插入和查询数据。这种动作较小。 - 另一种是将数据库的字符集修改为支持更多汉字的字符集，例如
AL32UTF8或ZHS32GB18030。 这种需要删除数据库，重新建库，动作较大，其实经过测试显示仍然有问题。
例如生僻字：㼆 yíng ，㱔 suǒ，䶮 yǎn
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | -- 解决Oracle存储生僻字、偏僻字 -- 只能保证业务系统能正常读取生僻字 DROP TABLE TEST; CREATE TABLE TEST(id int,n1 varchar2(255), n2 nvarchar2(255)); insert into TEST values(0,'张学𡚸䄲友','张学𡚸䄲友'); insert into TEST values(1,n'张学𡚸䄲友',n'张学𡚸䄲友'); insert into TEST VALUES(0,'㱔','㱔'); insert into TEST VALUES(1,'㱔',N'㱔'); insert into TEST VALUES(1,'㛃',N'㛃'); insert into TEST VALUES(0,'䶮','䶮'); insert into TEST VALUES(0,'㼆','㼆'); -- 中文转unicode参考：https://www.w3cschool.cn/tools/index?name=unicode_chinese insert into TEST select 1,utl_raw.cast_to_varchar2('3c54'),utl_raw.cast_to_nvarchar2('3c54')from dual; insert into TEST select 1,utl_raw.cast_to_varchar2('36c3'),utl_raw.cast_to_nvarchar2('36c3')from dual; commit; -- ID为1且n2列为正确的插入方法 SELECT n1,n2 FROM TEST; -- 补充 -- 测试 sql select n'䄲' from dual; -- 显示正常 select '䄲' from dual; -- 显示乱码 |
解决办法：
1 2 3 4 5 6 7 8 9 10 | -- 第一步： 将需要存储生僻字的字段类型修改为 nvarchar2 （注意：不是 varchar2） alter table TEST modify N1 nvarchar2(255); -- 第二步： 手工通过数据库图形化工具 将生僻字转换后 插入该字段（单引号前面加上 n） update TEST set sqrmc = n'张学𡚸䄲友' where sblsh = '44180022B0000NJ'; commit; -- 第三步： 进入业务系统页面查看 正常显示为 《 张学𡚸䄲友 》 |
实验
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | [oracle@lhrora11204 ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \ > -gdbname gbk -sid gbk \ > -sysPassword lhr -systemPassword lhr \ > -datafileDestination '/u01/app/oracle/oradata' \ > -recoveryAreaDestination '/u01/app/oracle/flash_recovery_area' \ > -redoLogFileSize 50 \ > -storageType FS \ > -characterset ZHS16GBK -nationalCharacterSet AL16UTF16 \ > -sampleSchema true \ > -totalMemory 512 \ > -databaseType OLTP \ > -emConfiguration NONE /bin/cat: /proc/sys/net/core/wmem_default: No such file or directory /bin/cat: /proc/sys/net/core/wmem_default: No such file or directory /bin/cat: /proc/sys/net/core/wmem_default: No such file or directory Copying database files 1% complete 3% complete 11% complete 18% complete 26% complete 37% complete Creating and starting Oracle instance 40% complete 45% complete 50% complete 55% complete 56% complete 57% complete 60% complete 62% complete Completing Database Creation 66% complete 70% complete 73% complete 85% complete 96% complete 100% complete Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/gbk/gbk.log" for further details. [oracle@lhrora11204 ~]$ export ORACLE_SID=gbk [oracle@lhrora11204 ~]$ sas SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 7 17:34:32 2023 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@gbk> select userenv('language') from dual; USERENV('LANGUAGE') ---------------------------------------------------- AMERICAN_AMERICA.ZHS16GBK SYS@gbk> |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
Oracle中varchar2和nvarchar2的区别
Oracle中varchar2和nvarchar2的区别主要有以下几点：
- varchar2是可变长度的字符类型，最多可以存放4000字节的数据，而nvarchar2是可变长度的国家字符类型，最多可以存放2000个字符。
- varchar2的长度单位可以是字节或字符，取决于type参数，而nvarchar2的长度单位只能是字符。
- varchar2在存储汉字时受到数据库字符集编码的影响，例如GBK编码时一个汉字占两个字节，UTF-8编码时一个汉字占三个字节。而nvarchar2在存储汉字时不受数据库字符集编码的影响，一个汉字始终占两个字节。
- varchar2和nvarchar2在插入和查询数据时可能需要使用不同的函数来转换数据类型。
总结
1、若Oracle数据库字符集为ZHS16GBK，则必须同时满足如下2个条件，才能避免生僻字乱码：
- 字段必须修改为
nvarchar2类型 - 插入或修改在字符串前加上“N”或者使用
utl_raw.cast_to_nvarchar2('unicode码')来插入。
2、绝大多数生僻字，在UTF8字符集中都是存在的，也就是在Oracle数据库的 AL32UTF8 字符集中都能够被正确存储。
3、若数据库为UTF8格式，则建议使用正常方式来操作，若还有乱码，则可以修改NLS_LANG参数，
1 | export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 |
在windows上也需要修改该参数，
若plsql developer显示有问题在：plsql developer 中在工具--》首选项--》选项中，选中 unicode enable 就可以显示了。
