Oracle数据库字符集为ZHS16GBK
,插入和查询生僻字显示乱码:
查询乱码:
先问问chatGPT,找找答案:
解决办法:
utl_raw.cast_to_nvarchar2
函数来插入和查询数据。这种动作较小。AL32UTF8
或ZHS32GB18030。 这种需要删除数据库,重新建库,动作较大,其实经过测试显示仍然有问题。例如生僻字:㼆 yíng ,㱔 suǒ,䶮 yǎn
-- 解决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; -- 显示乱码
解决办法:
-- 第一步: 将需要存储生僻字的字段类型修改为 nvarchar2 (注意:不是 varchar2)
alter table TEST modify N1 nvarchar2(255);
-- 第二步: 手工通过数据库图形化工具 将生僻字转换后 插入该字段(单引号前面加上 n)
update TEST set sqrmc = n'张学𡚸䄲友' where sblsh = '44180022B0000NJ';
commit;
-- 第三步: 进入业务系统页面查看 正常显示为 《 张学𡚸䄲友 》
[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中varchar2和nvarchar2的区别主要有以下几点:
1、若Oracle数据库字符集为ZHS16GBK,则必须同时满足如下2个条件,才能避免生僻字乱码:
nvarchar2
类型utl_raw.cast_to_nvarchar2('unicode码')
来插入。2、绝大多数生僻字,在UTF8字符集中都是存在的,也就是在Oracle数据库的 AL32UTF8 字符集中都能够被正确存储。
3、若数据库为UTF8格式,则建议使用正常方式来操作,若还有乱码,则可以修改NLS_LANG
参数,
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
在windows上也需要修改该参数,
若plsql developer显示有问题在:plsql developer 中在工具--》首选项--》选项中,选中 unicode enable 就可以显示了。
https://www.modb.pro/db/125984