关于Oracle数据库字符集的选择

如果数据库只在中国地区使用,数据库字符集选择ZHS16GBK或者常用中文字符集,如果不确定,就推荐使用AL32UTF8

国家字符集就选择: AL16UTF16

字符集一旦设定,不允许修改,修改可能出现乱码问题。Default Language和Default　Territory默认即可。

数据库字符集查看：

1、查看实例字符集环境
SQL> SELECT * FROM NLS_INSTANCE_PARAMETERS;
PARAMETER                                                    VALUE
------------------------------------------------------------ --------------------------------------------------------------------------------
NLS_LANGUAGE                                                 SIMPLIFIED CHINESE
NLS_TERRITORY                                                CHINA
NLS_SORT                                                     
NLS_DATE_LANGUAGE                                            
NLS_DATE_FORMAT                                              
NLS_CURRENCY                                                 
NLS_NUMERIC_CHARACTERS                                       
NLS_ISO_CURRENCY                                             
NLS_CALENDAR                                                 
NLS_TIME_FORMAT                                              
NLS_TIMESTAMP_FORMAT                                         
NLS_TIME_TZ_FORMAT                                           
NLS_TIMESTAMP_TZ_FORMAT                                      
NLS_DUAL_CURRENCY                                            
NLS_COMP                                                     BINARY
NLS_LENGTH_SEMANTICS                                         BYTE
NLS_NCHAR_CONV_EXCP                                          FALSE
17 rows selected

2、查看数据库服务器字符集环境：
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;
PARAMETER                      VALUE
------------------------------ --------------------------------------------------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               ZHS16GBK
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.1.0
20 rows selected


3、查看客户端字符集环境
SQL> SELECT * FROM V$NLS_PARAMETERS;
PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE                                                     SIMPLIFIED CHINESE
NLS_TERRITORY                                                    CHINA
NLS_CURRENCY                                                     ￥
NLS_ISO_CURRENCY                                                 CHINA
NLS_NUMERIC_CHARACTERS                                           .,
NLS_CALENDAR                                                     GREGORIAN
NLS_DATE_FORMAT                                                  DD-MON-RR
NLS_DATE_LANGUAGE                                                SIMPLIFIED CHINESE
NLS_CHARACTERSET                                                 ZHS16GBK
NLS_SORT                                                         BINARY
NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                                                ￥
NLS_NCHAR_CHARACTERSET                                           AL16UTF16
NLS_COMP                                                         BINARY
NLS_LENGTH_SEMANTICS                                             BYTE
NLS_NCHAR_CONV_EXCP                                              FALSE
19 rows selected

4、查看会话字符集环境
SQL> SELECT * FROM NLS_SESSION_PARAMETERS;
PARAMETER                                                    VALUE
------------------------------------------------------------ --------------------------------------------------------------------------------
NLS_LANGUAGE                                                 SIMPLIFIED CHINESE
NLS_TERRITORY                                                CHINA
NLS_CURRENCY                                                 ￥
NLS_ISO_CURRENCY                                             CHINA
NLS_NUMERIC_CHARACTERS                                       .,
NLS_CALENDAR                                                 GREGORIAN
NLS_DATE_FORMAT                                              DD-MON-RR
NLS_DATE_LANGUAGE                                            SIMPLIFIED CHINESE
NLS_SORT                                                     BINARY
NLS_TIME_FORMAT                                              HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT                                         DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                                           HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT                                      DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                                            ￥
NLS_COMP                                                     BINARY
NLS_LENGTH_SEMANTICS                                         BYTE
NLS_NCHAR_CONV_EXCP                                          FALSE
17 rows selected