Navicat设置utf8mb4后保存emoji仍然报错的解决方法

布满荆棘的人生 2024-04-25 08:42 3阅读 0赞

## 一、前言 ##

最近遇到一个问题，需要查库并导出报表；  
由于报表比较特殊，程序没有实现，因此准备先查询生产库、复制为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`：  
![在这里插入图片描述][98a5d306df37453f8549af101aa7f138.png]

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;

![在这里插入图片描述][fb15b6e21d5744b58ba5425df217e78f.png]

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

![在这里插入图片描述][1bb068c8a1fb4619a9e14b5b8df86fea.png]  
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：  
![在这里插入图片描述][c20ca56f1555432c95cf75cec3a5ce33.png]

8.然后，重新打开下Navicat，执行insert语句插入，是不报错了；**但是打开表，看到的数据都是问号，还是不对。**  
![在这里插入图片描述][3d89d02070ab4e7987a129d39fe5b125.png]  
![在这里插入图片描述][2d7e6b6ded5f4d80a2ebc6d4301a7cfb.png]

## 三、解决方法 ##

**现在的问题是，Navicat中执行insert语句插入emoji特殊符号，插入的都是问号，导出xls时也是问号，还是不对。**  
![在这里插入图片描述][eeaed83e4b19469eab9727d4e0623322.png]  
**下面直接上解决方法：**

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条是正确的：  
![在这里插入图片描述][87d2fdab9cef4066823f335e391d9e58.png]

3.想导出xls时，不能右击表导出，必须从结果集导出，才能正确导出emoji特殊符号。  
![在这里插入图片描述][ec139450848a4c31bee52c208bde1772.png]  
![在这里插入图片描述][ac7504a2d62040299675e8d2d2b5b19a.png]

可以看到与结果集相同，后面插入的3行可以成功导出emoji特殊符号。  
![在这里插入图片描述][57ab08a1436e4811a1c50b553e543343.png]

## 四、总结 ##

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特殊符号。

[98a5d306df37453f8549af101aa7f138.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/02e1565c93224c168ca10f5de02c38ac.png
[fb15b6e21d5744b58ba5425df217e78f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/fa8c652c6b6548ff9852793c9a9210f1.png
[1bb068c8a1fb4619a9e14b5b8df86fea.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/e9c50cca44c54d8d9761018492dabcd0.png
[c20ca56f1555432c95cf75cec3a5ce33.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/194e9175394746e193b031b2aa4eacea.png
[3d89d02070ab4e7987a129d39fe5b125.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/5c749afee3fc44a1af2a5e959389b48b.png
[2d7e6b6ded5f4d80a2ebc6d4301a7cfb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/2f4a4f8ec0844f61901a29660db1382a.png
[eeaed83e4b19469eab9727d4e0623322.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/aff1a6b70a6f46218c01e15c413a3c79.png
[87d2fdab9cef4066823f335e391d9e58.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/32ab3a52bd5a4262ae978afdf94a68ec.png
[ec139450848a4c31bee52c208bde1772.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/7c8540e743e545b59260d81b3063f534.png
[ac7504a2d62040299675e8d2d2b5b19a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/b60ea379cdc64fb297771f8e38a9855e.png
[57ab08a1436e4811a1c50b553e543343.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/e02ebea129cf4aadacac1d99ff3b04bb.png