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 |