暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Oracle字符集设置常见问题

Oracle字符集设置常见问题

原创 晨辉 2022-09-03
1437

字符集相关参数

LANG

linux系统语言设置参数，影响的是linux系统环境字符集显示，可以理解为这个参数控制了你的linux是中文系统还是英文系统。
通过修改LANG参数来改变linux系统显示字符，通过locale可以查看LANG各项设置的字符集

初始为en_US.UTF-8显示的是英文
[oracle@ora11g1 ~]$  echo $LANG
en_US.UTF-8
[oracle@ora11g1 ~]$ date
Mon Aug 29 04:19:31 CST 2022
[oracle@ora11g1 ~]$  locale --help
Usage: locale [OPTION...] NAME
  or:  locale [OPTION...] [-a|-m]
Get locale-specific information.

 System information:
  -a, --all-locales          Write names of available locales
  -m, --charmaps             Write names of available charmaps

 Modify output format:
  -c, --category-name        Write names of selected categories
  -k, --keyword-name         Write names of selected keywords
  -v, --verbose              Print more information

  -?, --help                 Give this help list
      --usage                Give a short usage message
  -V, --version              Print program version

For bug reporting instructions, please see:
<http://www.gnu.org/software/libc/bugs.html>.
设置为zh_CN后显示为中文
[oracle@ora11g1 ~]$ export LANG=zh_CN.utf8  
[oracle@ora11g1 ~]$ date
2022年 08月 29日 星期一 04:19:39 CST
[oracle@ora11g1 ~]$ locale --help
用法： locale [选项...] NAME
  或： locale [选项...] [-a|-m]
Get locale-specific information.

 系统信息：
  -a, --all-locales          写出可用区域的名称
  -m, --charmaps             写出可用字符映射的名称

 修改输出格式：
  -c, --category-name        写出选中范畴的名称
  -k, --keyword-name         写出选中关键字的名称
  -v, --verbose              打印更多信息

  -?, --help                 给出该系统求助列表
      --usage                给出简要的用法信息
  -V, --version              打印程序版本号

For bug reporting instructions, please see:
<http://www.gnu.org/software/libc/bugs.html>.

NLS_CHARACTERSET

oracle数据库的国家字符集，创建数据库的时候指定（后续修改可能会导致现有数据乱码问题，一般不建议建库后再修改），决定了存放的数据在数据库里的编码方式。
像中文的字符集这个参数一般设置为 AL32UTF8（支持中文、韩文、日文等文字）或者 ZHS16GBK（只支持中文）

NLS_NCHAR_CHARACTERSET

国际字符集，一些国家字符集不包含的字符的编码方式

NLS_LANG

oracle 客户端字符集设置参数，这个参数只针对oracle客户端，决定着输入的中文字符能正常编码以及客户端能否正常显示。
设置不正确会导致各种乱码问题：
建议中文字符集设置为AMERICAN_AMERICA.AL32UTF8
如下测试：

  1. 数据库字符集为ZHS16GBK，LANG 为zh_CN.utf8 ，NLS_LANG为SIMPLIFIED CHINESE_CHINA.ZHS16GBK，出现乱码
[oracle@ora11g1 ~]$ echo $LANG         
zh_CN.utf8
[oracle@ora11g1 ~]$ export NLS_LANG='SIMPLIFIED CHINESE_CHINA.ZHS16GBK';
[oracle@ora11g1 ~]$  echo $NLS_LANG
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
sqlplus 
SYS@xhydb>select * from nls_database_parameters where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

PARAMETER                                     VALUE
--------------------------------------------- ----------------------------------------
NLS_CHARACTERSET                              ZHS16GBK
NLS_NCHAR_CHARACTERSET                        AL16UTF16

SYS@xhydb>
SYS@xhydb>
SYS@xhydb>set lines 160 pages 200
SYS@xhydb>col column_name for a30
SYS@xhydb>col comments for a100
SYS@xhydb>select t.column_name,t.comments from dba_col_comments t where t.table_name='TEST' and t.owner='SYS' and t.comments is not null;

COLUMN_NAME                    COMMENTS
------------------------------ ----------------------------------------------------------------------------------------------------
OWNER                          ????????????
                               ????
                               ????

TABLE_NAME                     ״̬ 
                               

                               



SYS@xhydb>comment on column test.owner is '账号测试状态
  2  电瓶
  3  电票';
le_name='TEST' and t.owner='SYS' and t.comments is not null
*
 

  ORA-00933: SQL δ



SYS@xhydb>
  1. 数据库字符集为ZHS16GBK，LANG 为zh_CN.utf8 ，NLS_LANG为AMERICAN_AMERICA.ZHS16GBK ，出现乱码
[oracle@ora11g1 ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@ora11g1 ~]$ echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK
[oracle@ora11g1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 29 06:02:02 2022

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


Session altered.

SYS@xhydb>set lines 160 pages 200
SYS@xhydb>col column_name for a30
SYS@xhydb>col comments for a100
SYS@xhydb>select t.column_name,t.comments from dba_col_comments t where t.table_name='TEST' and t.owner='SYS' and t.comments is not null;

COLUMN_NAME                    COMMENTS
------------------------------ ----------------------------------------------------------------------------------------------------
OWNER                          ????????????
                               ????
                               ????

TABLE_NAME                     ״̬ 
                               

                               



SYS@xhydb>comment on column test.owner is '账号测试状态
  2  电瓶
  3  电票';
le_name='TEST' and t.owner='SYS' and t.comments is not null
*
ERROR at line 3:
ORA-00933: SQL command not properly ended


SYS@xhydb>
  1. 数据库字符集为ZHS16GBK，LANG 为zh_CN.utf8 ，NLS_LANG为AMERICAN_AMERICA.AL32UTF8, 没有乱码
[oracle@ora11g1 ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@ora11g1 ~]$  echo $NLS_LANG
AMERICAN_AMERICA.AL32UTF8
[oracle@ora11g1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 29 06:04:27 2022

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


Session altered.

SYS@xhydb>set lines 160 pages 200
SYS@xhydb>col column_name for a30
SYS@xhydb>col comments for a100
SYS@xhydb>select t.column_name,t.comments from dba_col_comments t where t.table_name='TEST' and t.owner='SYS' and t.comments is not null;

COLUMN_NAME                    COMMENTS
------------------------------ ----------------------------------------------------------------------------------------------------
OWNER                          ????????????
                               ????
                               ????

TABLE_NAME                     账号测试状态
                               电瓶
                               电票


SYS@xhydb>comment on column test.owner is '账号测试状态
  2  电瓶
  3  电票';

Comment created.

SYS@xhydb>set lines 160 pages 200
SYS@xhydb>col column_name for a30
SYS@xhydb>col comments for a100
SYS@xhydb>select t.column_name,t.comments from dba_col_comments t where t.table_name='TEST' and t.owner='SYS' and t.comments is not null;

COLUMN_NAME                    COMMENTS
------------------------------ ----------------------------------------------------------------------------------------------------
OWNER                          账号测试状态
                               电瓶
                               电票

TABLE_NAME                     账号测试状态
                               电瓶
                               电票


SYS@xhydb>
  1. 数据库字符集为ZHS16GBK，LANG 为zh_CN.utf8 ，NLS_LANG为AMERICAN_AMERICA.AL32UTF8,没有乱码
[oracle@ora11g1 ~]$ export NLS_LANG='SIMPLIFIED CHINESE_CHINA.AL32UTF8';
[oracle@ora11g1 ~]$ echo $NLS_LANG
SIMPLIFIED CHINESE_CHINA.AL32UTF8
[oracle@ora11g1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期一 8月 29 06:07:09 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


连接到: 
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@xhydb>set lines 160 pages 200
SYS@xhydb>col column_name for a30
SYS@xhydb>col comments for a100
SYS@xhydb>select t.column_name,t.comments from dba_col_comments t where t.table_name='TEST' and t.owner='SYS' and t.comments is not null;

COLUMN_NAME                    COMMENTS
------------------------------ ----------------------------------------------------------------------------------------------------
OWNER                          账号测试状态
                               电瓶
                               电票

TABLE_NAME                     账号测试状态
                               电瓶
                               电票


SYS@xhydb>comment on column test.owner is '账号测试状态1
  2  电瓶
  3  电票';

注释已创建。

SYS@xhydb>set lines 160 pages 200
SYS@xhydb>col column_name for a30
SYS@xhydb>col comments for a100
SYS@xhydb>select t.column_name,t.comments from dba_col_comments t where t.table_name='TEST' and t.owner='SYS' and t.comments is not null;

COLUMN_NAME                    COMMENTS
------------------------------ ----------------------------------------------------------------------------------------------------
OWNER                          账号测试状态1
                               电瓶
                               电票

TABLE_NAME                     账号测试状态
                               电瓶
                               电票


SYS@xhydb>
  1. 关于PLSQL客户端字符集设置
    PLSQL的NLS_LANG 参数取自于系统环境变量，如不设置可能会导致意想不到的乱码问题。
    将NLS_LANG删除
    image.png
    image.png
    image.png
    在环境变量添加NLS_LANG参数设置为AMERICAN_AMERICA.AL32UTF8
    image.png
    image.png
    image.png

NLS_LANG 总结:

针对 ZHS16GB和AL32UTF8字符集的数据库，NLS_LANG 设置为SIMPLIFIED CHINESE_CHINA.AL32UTF8和AMERICAN_AMERICA.AL32UTF8都是不会出现乱码问题的。
NLS_LANG推荐设置为：AMERICAN_AMERICA.AL32UTF8。

墨力计划oracle
最后修改时间：2022-09-05 10:57:36
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论