最近在回顾Java Web开发,又用回了MySql数据库,但是乱码问题又成了必踩的一坑....
好了,直入主题:
0. 环境:
Windows 7 64位 + MySql 5.6.34
1. 安装配置步骤:
我是参照百度经验的mysql5.6安装配置经验教程进行安装配置的。
我下载的是免安装版的:mysql-5.6.34-winx64.zip,解压后,将mysql解压目录(%MYSQL_HOME%)下的my-default.ini修改为my.ini;
a) 在系统环境变量下添加
MYSQL_HOME=D:\Software\MySql\MySql5.6\mysql-5.6.34-winx64//需修改为实际解压的路径
【path】后添加%MYSQL_HOME%\bin
b) 并在 [mysqld]中添加:
basedir = D:\Software\MySql\MySql5.6\mysql-5.6.34-winx64#此处的路径修改为实际解压后mysql目录的路径
datadir = D:\Software\MySql\MySql5.6\mysql-5.6.34-winx64\data
c) 用管理员身份启动cmd.exe,然后运行如下命令:
$ cd D:\Software\MySql\MySql5.6\mysql-5.6.34-winx64\bin \\ 进入mysql的bin目录
$ mysqld -install \\安装mysql
$ net start mysql \\启动mysql
$ mysql -u root -p \\登录mysql
就可以正常进入mysql,并进行建库建表的操作了。
2. 问题:
在通过JDBC取数据时,却在控制台显示乱码,因此怀疑为数据库编码设置问题。通过网上搜索,发现在mysql执行如下语句时:
show variables like '%character%';
show variables like '%collation%';
其设置的编码为latin1,因此按照其他博客的说明对my.ini配置文件进行修改:
[client]
default-character-set = utf8
[mysql]
#default-character-set = utf8
default-character = utf8
[mysqld]
#default-character-set = utf8
#default-character = utf8
init-connect = 'SET NAMES UTF8'
init-connect = 'SET collation_connection = utf8_unicode_ci'
character-set-client = utf8
character-set-server = utf8
collation-server = utf8_unicode_ci
skip-character-set-client-handshake
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
basedir = D:\Software\MySql\MySql5.6\mysql-5.6.34-winx64
datadir = D:\Software\MySql\MySql5.6\mysql-5.6.34-winx64\data
# port = .....
# server_id = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysql.server]
default-character-set = utf8
[mysqld_safe]
default-character-set = utf8
在修改的时候,按照网上的一些配置在我的环境下没有生效。执行 $ net start mysql 命令出现如下错误:
于是,我到mysql安装目录下 \data\*.err 错误日志文件查看,原来是一些配置项未被识别...
最终我通过排除法,将一些无法识别的配置项去掉,最终得到上述的配置文件,在数据库中查看编码,已经变为utf8。
主要注释掉的是 [mysql]下的default-character-set = utf8,以及[mysqld]下的default-character-set = utf8 ,default-character = utf8
3. 感想
MySql不同版本之间的配置存在差异,比如配置文件的生效顺序,这种应该在前期避免;
遇到问题时,网上的很多解决方法,并不一定在自己的环境下能生效。这个时候需要去官网找手册,并且自己尝试,同时日志是程序员配置和开发过程中的最最最基本的排查途径。