mysql数据库编码问题_mysql数据库字符集编码问题

工作和之前的学习过程中都是在使用mysql数据库，最近换了新的电脑，就装了mysql的数据库，结果感觉被坑。我开始装的版本是mysql-5.7.17-winx64，是使用安装包的形式安装的，结果在项目中创建数据库，表之后，向数据表中插入数据的时候，jsp页面显示的字符是乱码，项目中使用的字符集编码格式是utf-8,查看mysql数据库的编码格式，发现字符集编码形式不统一，于是以下是解决的方法。

1，mysql -u root  -p 登陆数据库之后，

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 character_set_system=utf8 ;执行以上命令，然后

show variables like 'collation_%';

show variables like 'character%';

发现字符集编码格式已经被修改，

quit

net stop mysql

net start mysql

重新登陆之后，发现字符集并没有修改。

2，修改文件目录下的my-default.ini配置文件。向文件中添加如下内容

[mysqld]

character-set-server=utf8

collation-server=utf8_general_ci

[mysql]

default-character-set = utf8

[mysql.server]

default-character-set = utf8

[mysqld_safe]

default-character-set = utf8

[client]

default-character-set = utf8

然后删除当前的mysql服务，重新注册mysql服务。

C:\Windows\system32>sc delete mysql

[SC] DeleteService 成功

C:\Windows\system32>e: cd

E:\>cd E:\0Develop\mysql-5.7.15-winx64\bin

E:\0Develop\mysql-5.7.15-winx64\bin>mysqld --install mysql --defaults-file="E:\0Develop\mysql-5.7.15-winx64\my-default.ini"

The service already exists!

The current server installed: E:\0Develop\mysql-5.7.15-winx64\bin\mysqld MySQL

E:\0Develop\mysql-5.7.15-winx64\bin>net stop mysql

MySQL 服务正在停止..服务名无效。

请键入 NET HELPMSG 2185 以获得更多的帮助。

E:\0Develop\mysql-5.7.15-winx64\bin>mysqld --install mysql --defaults-file="E:\0Develop\mysql-5.7.15-winx64\my-default.ini"

Service successfully installed.

E:\0Develop\mysql-5.7.15-winx64\bin>

启动服务，查看数据库的编码，发现

+--------------------------+---------------------------------------------------------+

| Variable_name            | Value                                                   |

+--------------------------+---------------------------------------------------------+

| character_set_client     | gbk                                                   |

| character_set_connection | gbk                                                  |

| character_set_database   | utf8                                                    |

| character_set_filesystem | binary                                                  |

| character_set_results    | gbk                                                  |

| character_set_server     | utf8                                                    |

| character_set_system     | utf8                                                    |

| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |

+--------------------------+---------------------------------------------------------+

character_set_client  ,character_set_connection,character_set_results 的编码格式并没有修改。失败over。

3，卸载之前zip安装的数据库，重新下载mysql-installer-community-5.7.17.0.msi数据库，成功安装之后，发现数据库的编码格式是这样的。

+--------------------------+---------------------------------------------------------+

| 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       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |

+--------------------------+---------------------------------------------------------+

没有进行任何设置。

总结：需要研究以下zip安装和msi安装。

参考文章：http://blog.csdn.net/u013474104/article/details/52486880