上周配合同事在测试环境中解决插入emoj表情包失败的问题。在度娘上查了很久,确定解决问题方向是增加应用系统对utf8mb4的支持。经过多次的尝试,总结如下步骤:
1 修改MYSQL的配置文件
在my.cnf配置文件中添加以下信息,让MYSQL数据库支持utf8mb4。
[client]
default-character-set = utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-client-handshake = FALSE
collation-server = utf8mb4_general_ci
init-connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
2 重启数据库
systemctl restart mysqld
3,修改数据库与表的字符集
更改数据库编码:ALTER DATABASE caitu99 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
更改表编码:ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATEutf8mb4_general_ci;
3.1 进入mysql
mysql -uroot -p123456
3.2 生产更改数据集脚本
select DISTINCT concat("ALTER DATABASE " , TABLE_SCHEMA , " CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;") from information_schema.columns where TABLE_SCHEMA in ('database1','database2',。。。'databasen') into outfile'/var/lib/mysql-files/updateAllDatabases.sql';
select DISTINCT concat("ALTER TABLE " , TABLE_SCHEMA ,"." , TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;") from information_schema.columns where TABLE_SCHEMA in ('database1','database2',。。。'databasen') into outfile'/var/lib/mysql-files/updateAllTables.sql';
3.3执行脚本
source /var/lib/mysql-files/updateAllDatabases.sql
source /var/lib/mysql-files/updateAllTables.sql
4. 更改MYCAT 配置文件
在Mycat server.xml中的<system>段中加入一条支持utf8mb4的属性:
<property name="charset">utf8mb4</property>
并重启MYCAT
网友评论