sybase字符集完整设置

1、   sybase ase数据库字符集的设置包括两部分，数据库服务器端和数据库客户端；

 

2、   数据库服务器端字符集修改步骤（修改数据库字符集为UTF8）：

 

1）   执行sp_helpsort命令查看当前数据库默认的字符集

   1> sp_helpsort      

2> go

    Collation Name                 Collation ID

 ------------------------------ ------------

   defaultml                                20

   thaidict                                 21

   iso14651                                 22

   utf8bin                                  24

 ……………………………………………………….

   gbpinyin                                163

    Loadable Sort Table Name       Collation ID

   ------------------------------ ------------

   cp932bin                                129

 ……………………………………………………….

   big5bin                                 194

 Sort Order Description

                                                                      ------------------------------------------------------------------

  Character Set = 1, iso_1                                          

     ISO 8859-1 (Latin-1) - Western European 8-bit character set.  

 Sort Order = 50, bin_iso_1                                        

     Binary ordering, for the ISO 8859/1 or Latin-1 character set (

     iso_1).                                                       

Characters, in Order

                                                                    

  ------------------------------------------------------------------

     ! " # $ % & ' ( ) * + , - . / 0 1 2 3 4 5 6 7 8 9 : ; < = > ? 

   @ A B C D E F G H I J K L M N O P Q R S T U V W X Y Z [ \ ] ^ _ 

   ` a b c d e f g h i j k l m n o p q r s t u v w x y z { | } ~ ? 

   ? ￠ ￡ ¤ ￥ | § ¨ ? a ? ? - ? ˉ ° ± 2 3 ′ μ ? · ? 1 o ? ? ? ? ?  

   `?à??????????Π?Р????????????????

   ? ? 堦 砨 頪 ? ? ? 

                                                                   

(return status = 0)

     由上面黑体字可以看出，当前数据库的默认字符集为iso_1。

 

2）   查看所要修改的字符集是否已经安装到数据库中；

   1> select name,id from syscharsets order by name     

2> go

 name                           id 

 ------------------------------ ---

 altdict                         45

 altnoacc                        39

 ……………………………………………………….

 cyrdict                        140

 .................................................................

cyrnocs                         64

   defaultml                       20

(62 rows affected)

       由上面所列内容可以看到，数据库中没有安装所要修改的字符集UTF8，因此需要先进行安装。

 

 

3）   安装数据库字符集UTF8；

   $ charset -Usa -P123456 binary.srt utf8

注：-P部分表示sa用户的密码，请根据实际情况输入。

 

4）   查看所要修改的字符集对应的id号

   1> select name,id from syscharsets order by name     

2> go

 name                           id 

 ------------------------------ ---

 cyrdict                        140

utf8                           190

 utf8bin                         24

(62 rows affected)

       本次要修改的UTF8字符集对应的id号为190.

 

5）   修改数据库字符集为UTF8

   1> sp_configure "default character set id",190

2> go

00:00000:00010:2010/01/29 08:52:27.82 server  Configuration file '/home/sybase/ASE-12_5/testdb.cfg' has been written and the previous version has been renamed to '/home/sybase/ASE-12_5/testdb.082'.

00:00000:00010:2010/01/29 08:52:27.82 server  The configuration option 'default character set id' has been changed by 'sa' from '1' to '190'.

00:00000:00010:2010/01/29 08:52:27.83 server  WARNING: ***************************

00:00000:00010:2010/01/29 08:52:27.83 server  Default char set being reconfigured:

00:00000:00010:2010/01/29 08:52:27.83 server    old charset ID = 1      new charset ID = 190

In changing the default sort order, you have also reconfigured SQL Server's

default character set.

   Parameter Name                 Default     Memory Used Config Value

         Run Value   Unit                 Type      

   ------------------------------ ----------- ----------- ------------

         ----------- -------------------- ----------

   default character set id                 1           0         190

                   1 id                   static    

 

(1 row affected)

Configuration option changed. Since the option is static, Adaptive Server must

be rebooted in order for the change to take effect.

Changing the value of 'default character set id' to '190' increases the amount

of memory ASE uses by 30 K.

(return status = 0)

 

6）   重启数据库

进入到/home/sybase/ASE-12_5/install目录下，执行startserver -f RUN_testdb将sybase重启二次，注：第一次启动会自动down掉，为正常情况，重新再启动即可。

 

   在重启过程中数据库可能会报：

   00:00000:00001:2010/01/29 08:49:48.80 server  Re-start Adaptive Server, use sp_configure to increase 'number of open databases' to at least 15, then re-configure the character set or sort order.

   这时需要重启数据库，并执行命令：

   1> sp_configure "number of open databases",15

2> go

00:00000:00010:2010/01/29 08:51:43.90 kernel  Setting console to nonblocking mode.

00:00000:00010:2010/01/29 08:51:43.90 server  Configuration file '/home/sybase/ASE-12_5/testdb.cfg' has been written and the previous version has been renamed to '/home/sybase/ASE-12_5/testdb.081'.

00:00000:00010:2010/01/29 08:51:43.90 server  The configuration option 'number of open databases' has been changed by 'sa' from '12' to '15'.

 Parameter Name                 Default     Memory Used Config Value

         Run Value   Unit                 Type      

 ------------------------------ ----------- ----------- ------------

         ----------- -------------------- ----------

 number of open databases                12        1792          15

                  15 number               dynamic   

 

(1 row affected)

Configuration option changed. The SQL Server need not be rebooted since the

option is dynamic.

the value of 'number of open databases' to '15' increases the amount of

memory ASE uses by 344 K.

(return status = 0)

修改参数为15，否则字符集不能成功修改，然后再操作一遍第6步，并重启数据库。

 

7）   设置默认的系统字符集。

A）$ langinstall -Usa -P123456 us_english utf8

 

B）   用sa进入isql进行配置

1> use master

2> go

1> select langid, name from syslanguages

2> go

1> sp_configure "default language", langid(此处用前面查找出来的id替换即可)

2> go

1> reconfigure with override

2> go

 

8）   检查数据库字符集是否修改成功

   sybase@linux:~/ASE-12_5/install> isql -Usa -P -Stestdb

1> sp_helpsort

2> go

 

   Collation Name                 Collation ID

   ------------------------------ ------------

   utf8bin                                  24

   ………………………………………………………..

 gbpinyin                                163

 

   Loadable Sort Table Name       Collation ID

   ------------------------------ ------------

   cp932bin                                129

   dynix                                   130

   ………………………………………………………..

 big5bin                                 194

 

Sort Order Description

                                                                   

 ------------------------------------------------------------------

 Character Set = 190, utf8                                         

     Unicode 3.1 UTF-8 Character Set                               

     Class 2 Character Set                                         

 Sort Order = 50, bin_utf8                                          

     Binary sort order for the ISO 10646-1, UTF-8 multibyte encodin

     g character set (utf8).                                       

(return status = 0)

从黑体字可以看出，数据库服务器端字符集修改成功。

 

2、   数据库客户端字符集修改步骤（修改数据库字符集为utf8）：

1）   确认客户端操作系统类型

   操作系统类型我们常用的一般为hpux/aix/linux/nt等

语言环境变量的确认规则：LC_ALL>LANG>default，即如果设置了LC_ALL变量，以LC_ALL变量的值为准，否则，如果设置了LANG变量，以LANG变量的值为准，否则，为default

2）   修改客户端字符集

   cd $SYBASE/locales

vi locales.dat

     找到[linux]配置组

   [linux]

        locale = FRENCH, french, iso_1

        locale = fr, french, iso_1

        locale = fr_BE, french, iso_1

        locale = zh_CN.gbk, chinese, eucgb

        locale = en_US.UTF-8, us_english,utf8

 再找[linux配置组]的default一行记录，如：

        locale = default, us_english, iso_1

将default的语言集改为：

    locale = default, us_english, utf8

修改保存即可．

说明：

a)       因为是linux操作系统，所以找linux配置组；因为LC_ALL和LANG都没有配置，所以寻找locale=default….行，综合起来就是寻找linux配置组下的locale=default对应的行。

b)       如果是AIX系统，LC_ALL配置的值是zh_CN.gbk，那么就应该寻找aix配置组下的locale=zh_CN.gbk对应的行进行修改。

c)       如果在配置组下找不到对应的Locale=xxx行，可以手工添加该行

3）   查看客户端字符集

>select @@client_csname

>go