1. 设置数据库,表,字段的编码格式及排序规则
在此之前需要删除表的外键,因为在修改表的编码时用到了convert to,如果不用convert to就不需要删除外键了。convert to这个有待本人研究!
>alter table notice drop foreign key FK_notice;
>alter table notice drop foreign key FK_notice_menu;
>alter table roply drop foreign key FK_roply_notice;
>alter table roply drop foreign key FK_roply;
开始修改编码
>ALTER DATABASE zhxq CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
>ALTER TABLE notice CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
>ALTER TABLE notice CHANGE content content VARCHAR(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
>ALTER TABLE roply CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
>ALTER TABLE roply CHANGE content content VARCHAR(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
再加上外键
>alter table notice add constraint FK_notice foreign key(username) references userinfo(username);
>alter table notice add constraint FK_notice_menu foreign key(menuId) references menu(menuId);
>alter table roply add constraint FK_roply_notice foreign key(noticeId) references notice(noticeId);
>alter table roply add constraint FK_roply foreign key(username) references userinfo(username);
修改配置文件my.cnf,内容如下
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
#character-set-client-handshake = FALSE 不是必须的
character-set-server = utf8mb4
#collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
重新启动msyql
service mysql restart
进入mysql查看字符集编码
>show variables like ‘%char%’;
>show create table notice;
>show create table roply;
2. 更新JDBC驱动
已经下载了mysql-connector-java-5.1.31-bin.jar,只需将它替换ZHXQ项目中lib包下的mysql-connector-java-5.1.1.jar即可。
3. 修改jdbc.properties配置文件
文件所在路径:/ZHXQ/WEB-INF/classes/jdbc.propertie,修改以下内容:
将jdbc.url=jdbc\:mysql\://101.226.243.241\:3306/zhxq后面的编码格式设置去掉,因为mysql-connector-java-5.1.31不需要在这里设置,如果写了会报原来的错误。
4. 最后重新启动项目
#ps –ef | grep java
#kill -9 ****
#/usr/local/tomcat/bin/startup.sh