写于2015-05-25,请根据时间进行参考。
今天做毕设遇到了一个问题,先大概说明一下情况:
我做的毕设是一个Android的校园社团app,其中用户在手机app上编辑完信息需要上传后台服务器,然后服务器再写入数据库中,我用的数据库是mysql。服务器可以正常接收中文,然而向mysql中写入时却写入乱码(mysql中可以正常存储中文,前台可以正常获取数据库中的中文,就是向里写有问题),最后推测应该是mysql的问题。
我在终端中进入mysql后用status查看是这样的:
--------------
/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.6.23, for osx10.8 (x86_64) using EditLine wrapper
Connection id: 10
Current database:
Current user: forif@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.23 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 1 hour 7 min 11 sec
Threads: 1 Questions: 24 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.005
--------------
开始也试过直接在终端进入mysql后用SET character_set_server = utf8 ;直接修改变量值,但这样改重新连接数据库后变量值又回到默认值了。
在网上查基本是说mysql在安装时选择字符集的问题,但问题是我在mac os x上安装mysql(版本是5.6.23,官网下的)时候根本就没出现那个选项,然后网上说如果安装完也可以改,在win下是在mysql目录下运行MySQLInstanceConfig.exe就可重新进行设置,但mac下没有这个文件。然后网上又说mac下把mysql目录里的support-files中的my-medium.cnf拷贝到/etc下并改名my.cnf并进行修改,但我依然没找到my-medium.cnf这个文件,里面只有一个my-default.cnf,于是我打开这个文件,里面最开始的注释是这样说的:
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
他说的很明白,不要直接编辑这个文件,如果想要修改就去看
http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html,那就去看看。
进去这个网站,第二项5.1.2.2 Using a Sample Default Server Configuration File,在最上面他说道:
As of MySQL 5.6.8, on Unix platforms, mysql_install_db creates a default option file named my.cnf in the base installation directory. This file is created from a template included in the distribution package named my-default.cnf. You can find the template in or under the base installation directory. When started using mysqld_safe, the server uses my.cnf file by default. If my.cnf already exists, mysql_install_db assumes it to be in use and writes a new file named my-new.cnf instead.
大概就是说my-default.cnf是作为模版文件来生成my.cnf的,然后往后看他说:
The my-default.cnf template replaces the older sample option files formerly supplied with MySQL distributions (my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf). As of MySQL 5.6.8, these older files are no longer distributed.
就是说从5.6.8开始my-medium.cnf已经被my-default.cnf替代了,旧文件都不再发布了。后面又说如果想获得更多信息就看这个4.2.6 Using Option Files,点进去再看,开头是这样说的:
Most MySQL programs can read startup options from option files (also sometimes called configuration files). Option files provide a convenient way to specify commonly used options so that they need not be entered on the command line each time you run a program. For the MySQL server, MySQL provides a number of preconfigured option files.
To determine whether a program reads option files, invoke it with the --help option. (For mysqld, use --verbose and --help.) If the program reads option files, the help message indicates which files it looks for and which option groups it recognizes.
他说用--help可以查看这个program是否读取配置文件,如果读取的话,这个帮助信息将指示它会寻找哪些文件,哪些option groups它能识别。
那就去终端试试,在终端下输入mysql --help(之前需要先用alias设置一下将mysql指向mysql的路径,要不每次输入整个路径麻烦,方法网上查),我的显示是这样的(截取中间一部分):
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
The following groups are read: mysql client
所以我的配置文件的读取顺序是/etc/my.cnf --> /etc/mysql/my.cnf --> /usr/local/mysql/etc/my.cnf --> ~/.my.cnf,而在我的系统中这4个文件都没有(=_=),那就自己建一个吧,当然你可以像网上说的那样在/etc中创建my.cnf,我是在/usr/local/mysql下新建etc文件夹,然后把my-default.cnf拷贝到这里并改名my.cnf,然后再修改它(修改之前要先关闭mysql进程!),按网上说的
在[client]部分加入:
default-character-set=utf8
在[mysqld]部分加入:
character-set-server=utf8
其中[client]和[mysqld]就是option groups,没有就自己写上,保存退出。然后在终端进入mysql,输入show variables like '%char%';(分号也要)查看变量值,我的就变成了是这样:
+--------------------------+--------------------------------------------------------+
| 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/local/mysql-5.6.23-osx10.8-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------+
8 rows in set (0.00 sec)
修改成功。
P.S.
今天太晚就没有测试mysql写入中文乱码的问题到底是不是由于mysql字符集的原因,明天测试一下,不过目前暂时把这个小问题弄清了,网上查的许多都不符合我的情况,最好的方法还是去官网的文档里寻找答案。
======================================
======================================
2015-05-26 补充:
今天测试安卓上传中文信息到服务器,服务器写入mysql中文成功,说明确实是这个原因(我是在作了上述修改之后把之前的数据库重新导入了mysql)。