sqlplus 查询 Oracle 数据库结果乱码或显示 ? ,则需要设置字符集。
一、客户端字符集
格式:NLS_LANG=language_territory.charset
Language: 指定服务器消息的语言, 影响提示信息是中文还是英文
Territory: 指定服务器的日期和数字格式
Charset: 指定字符集
如:SIMPLIFIED CHINESE_CHINA.ZHS16GBK
Linux 环境
# 查看系统编码
locale# 临时设置系统编码
# 永久设置(CentOS7)编辑 /etc/locale.conf
export LANG="zh_CN.UTF-8"# 临时设置 Oracle 客户端字符集,与系统编码一致
export NLS_LANG=american_america.AL32UTF8
Windows 环境
# set 设置临时环境变量,只针对当前 Shell 有效,关闭 Shell 就没有了
# setx 设置永久环境变量,和在我的电脑上右键属性设置是一样的效果# 管理员方式打开 CMD
# 设置当前用户环境变量
setx NLS_LANG "AMERICAN_AMERICA.UTF8"
# 设置系统环境变量
setx NLS_LANG "AMERICAN_AMERICA.UTF8" /m
二、服务端字符集
常用字符集
ZHS16GBK:存储中国人常用的字符
UTF8:unicode字符集,存储全世界的字符,只是不全
AL32UTF8:unicode字符集,比UTF8大多了,unicode字符集使用它,全但性能较差
以 sys 身份登录进行修改
-- 查看当前字符集
select * from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
SELECT USERENV('language') FROM DUAL;
SELECT parameter, value FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET';-- 关闭 database
shutdown immediate;-- 以启动数据库到 mount 状态下
startup mount;-- 设置session
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;-- 启动 database
alter database open;-- 修改字符集
ALTER DATABASE CHARACTER SET AL32UTF8;
-- 强制修改字符集
ALTER DATABASE character set INTERNAL_USE AL32UTF8;-- 关闭 database
shutdown immediate;-- 重启
startup;
三、常见的locale ID与字符集名称的对应关系,服务端设置的是Locale ID,客户端设置的是NLS_LANG
Language |
Locale ID |
NLS_LANG |
---|---|---|
English (American) |
en_US.UTF-8 |
AMERICAN_AMERICA.AL32UTF8 |
English (American) |
en_US.ISO-8859-1 |
AMERICAN_AMERICA.WE8ISO8859P1 |
English (American) |
en_US.ISO-8859-15 |
AMERICAN_AMERICA.WE8ISO8859P15 |
English (Australian) |
en_AU.UTF-8 |
ENGLISH_AUSTRALIA.AL32UTF8 |
English (Australian) |
en_AU.ISO-8859-1 |
ENGLISH_AUSTRALIA.WE8ISO8859P1 |
English (Australian) |
en_AU.ISO-8859-15 |
ENGLISH_AUSTRALIA.WE8ISO8859P15 |
English (British) |
en_GB.UTF-8 |
ENGLISH_UNITED KINGDOM.AL32UTF8 |
English (British) |
en_GB.ISO-8859-1 |
ENGLISH_UNITED KINGDOM.WE8ISO8859P1 |
English (British) |
en_GB.ISO-8859-15 |
ENGLISH_UNITEDKINGDOM.WE8ISO8859P15 |
English (Ireland) |
en_IE.UTF-8 |
ENGLISH_IRELAND.AL32UTF8 |
English (Ireland) |
en_IE.ISO-8859-1 |
ENGLISH_IRELAND.WE8ISO8859P1 |
English (Ireland) |
en_IE.ISO-8859-15 |
ENGLISH_IRELAND.WE8ISO8859P15 |
German |
de_DE.UTF-8 |
GERMAN_GERMANY.AL32UTF8 |
German |
de_DE.ISO-8859-1 |
GERMAN_GERMANY.WE8ISO8859P1 |
German |
de_DE.ISO-8859-15 |
GERMAN_GERMANY.WE8ISO8859P15 |
French |
fr_FR.UTF-8 |
FRENCH_FRANCE.AL32UTF8 |
French |
fr_FR.ISO-8859-1 |
FRENCH_FRANCE.WE8ISO8859P1 |
French |
fr_FR.ISO-8859-15 |
FRENCH_FRANCE.WE8ISO8859P15 |
Italian |
it_IT.UTF-8 |
ITALIAN_ITALY.AL32UTF8 |
Italian |
it_IT.ISO-8859-1 |
ITALIAN_ITALY.WE8ISO8859P1 |
Italian |
it_IT.ISO-8859-15 |
ITALIAN_ITALY.WE8ISO8859P15 |
Spanish |
es_ES.UTF-8 |
SPANISH_SPAIN.AL32UTF8 |
Spanish |
es_ES.ISO-8859-1 |
SPANISH_SPAIN.WE8ISO8859P1 |
Spanish |
es_ES.ISO-8859-15 |
SPANISH_SPAIN.WE8ISO8859P15 |
Spanish (Mexico) |
es_MX.UTF-8 |
MEXICAN SPANISH_MEXICO.AL32UTF8 |
Spanish (Mexico) |
es_MX.ISO-8859-1 |
MEXICAN SPANISH_MEXICO.WE8ISO8859P1 |
Spanish (Mexico) |
es_MX.ISO-8859-15 |
MEXICANSPANISH_MEXICO.WE8ISO8859P15 |
Portuguese (Brazilian) |
pt_BR.UTF-8 |
BRAZILIANPORTUGUESE_BRAZIL.AL32UTF8 |
Portuguese (Brazilian) |
pt_BR.ISO-8859-1 |
BRAZILIANPORTUGUESE_BRAZIL.WE8ISO8859P1 |
Portuguese (Brazilian) |
pt_BR.ISO-8859-15 |
BRAZILIANPORTUGUESE_BRAZIL.WE8ISO8859P15 |
Japanese |
ja_JP.EUC-JP |
JAPANESE_JAPAN.JA16EUC |
Japanese |
ja_JP.UTF-8 |
JAPANESE_JAPAN.AL32UTF8 |
Korean |
ko_KR.EUC-KR |
KOREAN_KOREA.KO16KSC5601 |
Korean |
ko_KR.UTF-8 |
KOREAN_KOREA.AL32UTF8 |
Chinese (simplified) |
zh_CN.GB18030 |
SIMPLIFIEDCHINESE_CHINA.ZHS32GB18030 |
Chinese (simplified) |
zh_CN.UTF-8 |
SIMPLIFIED CHINESE_CHINA.AL32UTF8 |
Chinese (traditional) |
zh_TW.BIG5 |
TRADITIONALCHINESE_TAIWAN.ZHT16BIG5 |
Chinese (traditional) |
zh_TW.UTF-8 |
TRADITIONAL CHINESE_TAIWAN |