mysql的乱码问题主要出现在, 以下4方面:
1、jdbc 的url设定的字符编码问题
2、Mysql server编码问题
3、table 设定的编码问题
4、Mysql Client编码问题
一、 规避出现乱面的方式:
1、在创建数据库时制定编码集
CREATE DATABASE `zkcfg`
CHARACTER SET 'utf8'
COLLATE 'utf8_general_ci';
2、创建数据表时制定数据集
CREATE TABLE tb_datacfg (
dataIdVarchar (200) not null,
data Text not null,
createTime TIMESTAMP null,
creatorVarchar (32) null,
modifyTime TIMESTAMP null,
modifierVarchar (32) null,
primary key (dataId)
)ENGINE=InnoDB default charset utf8 collate utf8_general_ci;
二、问题排查
1、 如果已经创建好数据表,这时出现乱码问题,就需要自己排查,方法如下:
mysql> show variables like "%char%";
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+
上面的结果表明database 与 server的编码都是latin1编码需要修改配置,使用以下语句:
SET character_set_database='utf8';
SET character_set_server='utf8';
2、 查看你所创建数据库的编码
mysql> show create database zkcfg;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| zkcfg | CREATE DATABASE `zkcfg` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
结果表明zkcfg数据的编码为latin1,需要修改为utf-8,语句如下:
ALTER DATABASE `zkcfg` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
3、修改my.cnf文件添加如下配置
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set=utf8
[mysql]
default-character-set=utf8
[server]
character_set_server=utf8
[database]
character_set_database=utf8
修改完my.cnf文件之后需要重启mysqld服务;
service mysqld restart
完结