一、前言
最近遇到一个问题,需要查库并导出报表;
由于报表比较特殊,程序没有实现,因此准备先查询生产库、复制为insert语句,然后在本地Navicat里执行、处理、再导出xls,这样快一些。
但是,没想到复制出来的insert语句包含emoji特殊符号,本地Navicat执行报错:
Incorrect string value: '\xF0\x9F\x89\x90\xE4\xB8...' for column 'reply' at row 1
好不容易解决了之后,导出报表又遇到了一系列问题。
以下是踩坑过程与解决方法。
二、踩坑过程
1.首先,复制出来的insert语句样例如下:
INSERT INTO report (`name`, `reply`) VALUES ('晓明', '[爱心][爱心]\n1.第一点\n2.🈶第二点\n1.\"A+B\"\n“C+D”\n3.第三点');INSERT INTO report (`name`, `reply`) VALUES ('新人', '🔆太阳🔆,\n📍苹果🍎\n📍礼物🎁\n📍核心🎗\n📍典范🏅\n❤️心');INSERT INTO report (`name`, `reply`) VALUES ('张某', '🈶');
2.本地Navicat中,建了一个Mysql数据库、一个表、设置了表的列,都用了utf8mb4
:
CREATE TABLE `report` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) DEFAULT NULL,`reply` varchar(2000) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
3.然后,Navicat里执行insert语句,报错,无法插入数据:
Incorrect string value: '\xF0\x9F\x89\x90\xE4\xB8...' for column 'reply' at row 1
4.百度发现,只是设置了数据库、表、列为utf8mb4,还不够,还需要修改mysql配置文件,于是就准备修改mysql配置文件。
5.找到windows中mysql的配置文件的位置:
//本人的在这里
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
6.在my.ini
中新增或修改为以下配置:
[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'sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
可以搜索[client]
、[mysql]
、[mysqld]
,然后在这些标签的下方,新增或修改为上面的配置;
大部分是把utf8改为了utf8mb4;
其中sql-mode
里去掉了STRICT_TRANS_TABLES
,主要是这个检查导致的,去掉后sql的insert就能执行成功了。
7.重启windows的mysql:
8.然后,重新打开下Navicat,执行insert语句插入,是不报错了;但是打开表,看到的数据都是问号,还是不对。
三、解决方法
现在的问题是,Navicat中执行insert语句插入emoji特殊符号,插入的都是问号,导出xls时也是问号,还是不对。
下面直接上解决方法:
1.Navicat连接本地数据库后,需要这样执行sql:
#必须先选择数据库,不能省略(本人的数据库名是test)
use test;set names utf8mb4;INSERT INTO report (`name`, `reply`) VALUES ('晓明', '[爱心][爱心]\n1.第一点\n2.🈶第二点\n1.\"A+B\"\n“C+D”\n3.第三点');INSERT INTO report (`name`, `reply`) VALUES ('新人', '🔆太阳🔆,\n📍苹果🍎\n📍礼物🎁\n📍核心🎗\n📍典范🏅\n❤️心');INSERT INTO report (`name`, `reply`) VALUES ('张某', '🈶');
2.查看是否保存成功时,不能直接双击打开表,也需要执行sql:
use test;set names utf8mb4;#本人的表名是report
select * from report;
可以看到,后续插入的3条是正确的:
3.想导出xls时,不能右击表导出,必须从结果集导出,才能正确导出emoji特殊符号。
可以看到与结果集相同,后面插入的3行可以成功导出emoji特殊符号。
四、总结
Navicat中想插入emoji特殊符号的步骤如下:
1.数据库、表、列
要改为utf8mb4格式
2.my.ini中去掉STRICT_TRANS_TABLES
,重启mysql
3.执行insert
语句前,要先执行use mydb;
选择数据库,然后执行set names utf8mb4;
设置字符格式,然后执行insert才能成功
4.查看是否插入成功,也需要执行select
语句,同样需要先执行use mydb;
选择数据库,然后执行set names utf8mb4;
设置字符格式
5.想导出xls报表时,需要先用select
查询出来,然后点结果集的导出按钮,才能正确导出emoji特殊符号。