mysql数据库乱码处理方案

MYSQL数据库乱码问题处理方案

MySQL中默认字符集的设置有四级:服务器级，数据库级，表级，字段级 。注意前三种均为默认设置。字段级得字符集是在建表是定义的。数据库乱码问题大多数都是由字符集不一致导致的。故一般只要把所有字符集设置一致就能解决乱码问题。

（character_set_client，character_set_results，character_set_connection三个运行变量是造成乱码的关键。mysql把客户端提交的查询由character_set_client转换为character_set_connection）

在服务器端查看设置字符集：

SHOW VARIABLES LIKE 'character_set_%';

如：

mysql> show variables like 'character_set_%';

Variable_name |Value character_set_client |latin1 -- 客户端字符集character_set_connection|latin1 character_set_database |latin1 -- 数据库字符集character_set_filesystem|binary character_set_results |latin1 character_set_server |latin1 -- 服务器字符集

character_set_system |utf8 character_sets_dir |D:\MySQLServer5.0\share\charsets\ SHOW VARIABLES LIKE 'collation_%';

mysql> SHOW VARIABLES LIKE ’collation_%’;

| Variable_name | Value |

| collation_connection | latin1_swedish_ci |

| collation_database | latin1_swedish_ci |

| collation_server | latin1_swedish_ci |

可见里面的多项默字符集都是latin1,这不可是通用字符集,于是将数据库里的字符集修改成utf8.

方法一：

1.关闭mysql服务：service mysql stop;.

2.在mysql根目录下找到my.ini文件,(虽然英文多,但别怕),往下拉,找到:

[client]

port=3306

…

[mysql]

default-character-set=latin1

…

将"default-character-set=latin1",改成"default-character-set=utf8".

3. 按"ctrl+s"保存文件

4. 在MySQLAdministrator.exe里的"启动变量---高级"里"默认字符集设置里,也将"其值改成utf8.之后按"应用更改".

5.开启mysql服务:service mysql start;

方法二：

set character_set_client='utf8';

set character_set_connection='utf8';

set character_set_database='utf8';

set character_set_results='utf8';

set character_set_server='utf8';

set collation_connection='utf8_general_ci';

set collation_database='utf8_general_ci';

set collation_server='utf8_general_ci';

方法三：

SET NAMES ’utf8’;

最后使用：

SHOW VARIABLES LIKE 'character_set_%';

SHOW VARIABLES LIKE 'collation_%';

可以看到我的编码均是 utf8

查看表的编码格式

show create table tablename ;

或show full fields from tableName;

如果查看表的字符集不是: utf8，通过一下方法修改表字符集。 alter table tablename convert to character set utf8; 查看服务器配置文件

#vi /etc/f

[client]

default-character-set = utf8

[mysqld]

character-set-server = utf8

collation-server = utf8_general_ci

init-connect='SET NAMES utf8'

log_bin = /usr/local/mysql/data/mysql-bin.log

log_bin_index = /usr/local/mysql/data/mysql-bin.log.index relay_log = /usr/local/mysql/data/mysql-relay-bin relay_log_index

/usr/local/mysql/data/mysql-relay-bin.index

expire_logs_days = 10

max_binlog_size = 100M

log_slave_updates = 1

bind-address = 0.0.0.0

如果配置文件有问题，通过修改此文件解决。 =

本文来源：https://www.bwwdw.com/article/aym4.html