Mysql中编码的问题

在Mysql中，经常由于编码的问题搞得焦头烂额，要想搞定编码的问题，首先得理解编码的相关的知识．理论的东西我就不再缀述了．

　要知道自己的mysql现在用的是什么编码方式很简单，可以执行status命令查看．

   如下面信息：

 

　
mysql> status
--------------
mysql  Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id:          655616
Current database:       vvop
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.77 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    utf8
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 184 days 6 hours 6 min 51 sec

Threads: 6  Questions: 65587658  Slow queries: 0  Opens: 274  Flush tables: 1  Open tables: 64  Queries per second avg: 4.120

 

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

 

可以看到这里除了db的编码为utf8,其他的都是 latin1编码，这样往往在查询的时候，会导致乱码的出现．

要解决这种情况可以把开mysql的配置文件，一般在 /etc/my.cnf.

 

打开这个文件，如下形式：

 

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
max_allowed_packet=20485760
#skip-locking

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

修改其默认的编码格式方法：

1.在mysqld中加入 default-character-set =utf8
2.加入一个context为[mysql]，并加入 default-character-set=utf8

 

如下：

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
max_allowed_packet=20485760
#skip-locking
default-character-set =utf8

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysql]
default-character-set=utf8

 

 

最后，重启一下mysql： service mysqld restart

 

 

=======================================

 

再以status查看的时候，可以看到如下信息：

 

mysql> status
--------------
mysql  Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id:          5
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.77 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 7 min 22 sec

Threads: 3  Questions: 122  Slow queries: 0  Opens: 48  Flush tables: 1  Open tables: 42  Queries per second avg: 0.276

 

 

所有的编码都已经变成了utf8格式了.

 

================================

如果Db characterset 也不是utf8格式时，可以用以下的办法解决：

 

mysql -> use vvop

mysql -> alter database vvop character set utf8;

 

这样就ok了．