Ubuntu修改mysql编码格式
今天在Ubuntu系统上部署了第一个net core的web网站,遇到了mysql入库数据乱码的情况。无奈,ubuntu系统不熟悉,mysql命令不熟悉,只得在网上查找各种资料。还是老规矩,主要参考的网站列表如下:
1. http://www.cnblogs.com/ndxsdhy/archive/2011/11/19/2255111.html
2. http://www.jb51.net/article/74769.htm
3. http://www.2cto.com/database/201501/372615.html
大致的了解了修改编码格式的方法之后,开始着手调整。首先,使用两条命令查看了数据库与服务端的字符集设置。结果如下:
show variables like "%colla%" ;
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
=============================
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/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
因为我安装mysql是默认安装,根据网上的资料,在/etc/mysql/文件夹下找到了my.cnf(在Windows下是my.ini)。vi my.cnf命令进入编辑,在[client]和[mysqld]中分别添加内容;如下
[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
init_connect='SET NAMES utf8'
这里需要特别注意,[mysqld]中,不是default-character-set=utf8,而是character-set-server=utf8,不然,重新启动mysql的时候,就会悲剧了,没错,我就是这么悲剧了,才来写这篇文章的。好吧,重启mysql的时候,异常信息如下:
/etc/init.d/mysql restart
stop: Unknown instance:
start: Job failed to start
也出现过如下错误:
/etc/mysql# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
这个错误是不是修改my.cnf引起的就不知道了,出现的这个错误后续我也执行了如下命令:
chown -R mysql:mysql /var/lib/mysql
差不多就这些了,有遗漏的再补充吧~希望对想我这样的新手有所帮助!