mysql编码集的问题

今天在使用load data命令的时候

load data local  infile '/xxx.txt' ignore into table `im_account_compatible` fields ESCAPED By "`"

报了以下错误

ERROR 1300 (HY000): Invalid utf8 character string: ''

然后查看了一下自己的数据库编码集

show variables like 'character%';
character_set_client	utf8mb4
character_set_connection	utf8mb4
character_set_database	utf8
character_set_filesystem	binary
character_set_results	utf8mb4
character_set_server	utf8
character_set_system	utf8
character_sets_dir	/usr/share/mysql/charsets/

查看了以下xxx.txt文件，发现里面有表情包，这才意识到，mysql5.7版本要存入表情包需要utf8mb4编码集，所以需要将character_set_database的编码集改成utf8md4编码

alter database database character set utf8mb4; 

这个时候在使用load data local infile导入带有表情包的文件就不会报错了

记录一下我理解的编码集

如果在navicat中创建的数据库什么都不选

它的编码集会是

show variables like 'character%';
character_set_client	utf8mb4
character_set_connection	utf8mb4
character_set_database	utf8
character_set_filesystem	binary
character_set_results	utf8mb4
character_set_server	utf8
character_set_system	utf8
character_sets_dir	/usr/share/mysql/charsets/

这些编码集如果在创建数据库的时候不选，那么就根据你my.cnf配置文件去默认确定

使用navicat创建一个test4的库

查看它的编码机集

show variables like 'character%';
character_set_client	utf8mb4
character_set_connection	utf8mb4
character_set_database	ascii
character_set_filesystem	binary
character_set_results	utf8mb4
character_set_server	utf8
character_set_system	utf8
character_sets_dir	/usr/share/mysql/charsets/

这里有一篇介绍编码集的文章

https://blog.csdn.net/sun8112133/article/details/79921734