Linux下MySQL 5.5的修改字符集编码为UTF8（彻底解决中文乱码问题）

一、登录MySQL查看用SHOW VARIABLES LIKE ‘character%’;下字符集，显示如下：

+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

character_set_database和character_set_server的默认字符集还是latin1。

二、最简单的完美修改方法，修改mysql的my.cnf文件中的字符集键值（注意配置的字段细节）：

1、在[client]字段里加入default-character-set=utf8，如下：

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set=utf8

2、在[mysqld]字段里加入character-set-server=utf8，如下：

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
character-set-server=utf8
init-connect=’SET NAMES utf8′
3、在[mysql]字段里加入default-character-set=utf8，如下：

[mysql]
no-auto-rehash
default-character-set=utf8

修改完成后，service mysql restart重启mysql服务就生效。注意：[mysqld]字段与[mysql]字段是有区别的。这点在网上没人反馈过。

使用SHOW VARIABLES LIKE ‘character%’;查看，发现数据库编码全已改成utf8。

+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+




4、如果上面的都修改了还乱码，那剩下问题就一定在connection连接层上。解决方法是在发送查询前执行一下下面这句（直接写在SQL文件的最前面）：
SET NAMES ‘utf8′;

它相当于下面的三句指令：

SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;


其他的一些设置方法： 
通过配置文件修改: 
修改/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命令行修改: 

1 mysql> set character_set_client=utf8; 

2 mysql> set character_set_connection=utf8; 

3 mysql> set character_set_database=utf8; 

4 mysql> set character_set_results=utf8; 

5 mysql> set character_set_server=utf8; 

6 mysql> set character_set_system=utf8; 

7 mysql> set collation_connection=utf8; 

8 mysql> set collation_database=utf8; 

9 mysql> set collation_server=utf8;

 

1 show variables like 'collation_%'; 

2 show variables like 'character_set_%';

 

1 ---1.修改数据库字符集 

2 alter database mini default character set = gb2312;

3 ----2.创建数据库设置字符集 

4 create database mydb character set gb2312;

 

1 alter table pub_logs default character set = gb2312; 2 alter table pub_logs convert to character set gb2312;

 

1 SELECT a.TABLE_TYPE,CONCAT('alter TABLE ',A.TABLE_NAME,' default character set = gb2312;') FROM INFORMATION_SCHEMA.TABLES A 2 WHERE A.TABLE_SCHEMA='MINI' 3 AND a.TABLE_TYPE='BASE TABLE' 4 ;

 

1 SELECT CONCAT(CONCAT(CONCAT('alter TABLE ',c.TABLE_NAME,' modify'),CONCAT(' ',C.COLUMN_NAME,' '),C.COLUMN_TYPE),' ',' character set gb2312 COLLATE gb2312_chinese_ci;') AS CLOU 

2 FROM

3 INFORMATION_SCHEMA.COLUMNS C,INFORMATION_SCHEMA.TABLES A 

4 WHERE c.TABLE_SCHEMA='MINI' 

5 AND A.TABLE_NAME=c.TABLE_NAME 

6 AND A.TABLE_TYPE='BASE TABLE' 

7 AND c.DATA_TYPE='varchar' 

8 ;