• 查看和设置MySQL数据库字符集
    时间:2009-08-02   作者:佚名   出处:互联网

    Liunx下修改MySQL字符集:
    1.查找MySQL的cnf文件的位置
    find / -iname '*.cnf' -print
     
    /usr/share/mysql/my-innodb-heavy-4G.cnf
    /usr/share/mysql/my-large.cnf
    /usr/share/mysql/my-small.cnf
    /usr/share/mysql/my-medium.cnf
    /usr/share/mysql/my-huge.cnf
    /usr/share/texmf/web2c/texmf.cnf
    /usr/share/texmf/web2c/mktex.cnf
    /usr/share/texmf/web2c/fmtutil.cnf
    /usr/share/texmf/tex/xmltex/xmltexfmtutil.cnf
    /usr/share/texmf/tex/jadetex/jadefmtutil.cnf
    /usr/share/doc/MySQL-server-community-5.1.22/my-innodb-heavy-4G.cnf
    /usr/share/doc/MySQL-server-community-5.1.22/my-large.cnf
    /usr/share/doc/MySQL-server-community-5.1.22/my-small.cnf
    /usr/share/doc/MySQL-server-community-5.1.22/my-medium.cnf
    /usr/share/doc/MySQL-server-community-5.1.22/my-huge.cnf

    2. 拷贝 small.cnf、my-medium.cnf、my-huge.cnf、my-innodb-heavy-4G.cnf其中的一个到/etc下,命名为my.cnf
      cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

    3. 修改my.cnf

    vi /etc/my.cnf

    在[client]下添加
      default-character-set=utf8

    在[mysqld]下添加
      default-character-set=utf8

    4.重新启动MySQL
      [root@bogon ~]# /etc/rc.d/init.d/mysql restart
      Shutting down MySQL                                        [  确定  ]
      Starting MySQL.                                            [  确定  ]
      [root@bogon ~]# mysql -u root -p
      Enter password:
      Welcome to the MySQL monitor.  Commands end with ; or \g.
      Your MySQL connection id is 1
      Server version: 5.1.22-rc-community-log MySQL Community Edition (GPL)

      Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

    5.查看字符集设置

    mysql> show variables like 'collation_%';
    +----------------------+-----------------+
    | Variable_name        | Value           |
    +----------------------+-----------------+
    | collation_connection | utf8_general_ci |
    | collation_database   | utf8_general_ci |
    | collation_server     | utf8_general_ci |
    +----------------------+-----------------+
    3 rows in set (0.02 sec)

    mysql> show variables like 'character_set_%';
    +--------------------------+----------------------------+
    | Variable_name            | Value                      |
    +--------------------------+----------------------------+
    | character_set_client     | utf8                       |
    | character_set_connection | utf8                       |
    | character_set_database   | utf8                       |
    | character_set_filesystem | binary                     |
    | character_set_results    | utf8                       |
    | character_set_server     | utf8                       |
    | character_set_system     | utf8                       |
    | character_sets_dir       | /usr/share/mysql/charsets/ |
    +--------------------------+----------------------------+
    8 rows in set (0.02 sec)

    mysql>

     
     
    其他的一些设置方法:
     
    修改数据库的字符集
      mysql>use mydb
      mysql>alter database mydb character set utf-8;

    创建数据库指定数据库的字符集
      mysql>create database mydb character set utf-8;

     

    通过配置文件修改:

    修改/var/lib/mysql/mydb/db.opt
    default-character-set=latin1
    default-collation=latin1_swedish_ci

    default-character-set=utf8
    default-collation=utf8_general_ci

    重起MySQL:
    [root@bogon ~]# /etc/rc.d/init.d/mysql restart

     

    通过MySQL命令行修改:

    mysql> set character_set_client=utf8;
    Query OK, 0 rows affected (0.00 sec)

    mysql> set character_set_connection=utf8;
    Query OK, 0 rows affected (0.00 sec)

    mysql> set character_set_database=utf8;
    Query OK, 0 rows affected (0.00 sec)

    mysql> set character_set_results=utf8;
    Query OK, 0 rows affected (0.00 sec)

    mysql> set character_set_server=utf8;
    Query OK, 0 rows affected (0.00 sec)

    mysql> set character_set_system=utf8;
    Query OK, 0 rows affected (0.01 sec)

    mysql> set collation_connection=utf8;
    Query OK, 0 rows affected (0.01 sec)

    mysql> set collation_database=utf8;
    Query OK, 0 rows affected (0.01 sec)

    mysql> set collation_server=utf8;
    Query OK, 0 rows affected (0.01 sec)


    查看:
    mysql> show variables like 'character_set_%';
    +--------------------------+----------------------------+
    | Variable_name            | Value                      |
    +--------------------------+----------------------------+
    | character_set_client     | utf8                       |
    | character_set_connection | utf8                       |
    | character_set_database   | utf8                       |
    | character_set_filesystem | binary                     |
    | character_set_results    | utf8                       |
    | character_set_server     | utf8                       |
    | character_set_system     | utf8                       |
    | character_sets_dir       | /usr/share/mysql/charsets/ |
    +--------------------------+----------------------------+
    8 rows in set (0.03 sec)

    mysql> show variables like 'collation_%';
    +----------------------+-----------------+
    | Variable_name        | Value           |
    +----------------------+-----------------+
    | collation_connection | utf8_general_ci |
    | collation_database   | utf8_general_ci |
    | collation_server     | utf8_general_ci |
    +----------------------+-----------------+
    3 rows in set (0.04 sec)

    6.修改表的字符集

    ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

    网友留言/评论

    我要留言/评论