用sqlyog导入mysql中文数据乱码问题

ゝ一纸荒年。 2022-05-30 07:28 378阅读 0赞

我一直都使用sqlyog客户端进行操作mysql数据库,导入导出都是非常方便操作的,一开始导入excel(.xlsx)以及csv文件还遇到一些困难,后来经常摸索也就解决了,但是这两天在导入数据的问题上遇到了一个难题,以至于我后来想通过程序读取excel文件然后写入数据库,但是我工作的时候被各种其他需求给打断了,写了一段代码就半途中断了,我想总有办法解决的.

每次导入excel,即.xlsx结尾的文件,从来没有过乱码的问题,但是供应商给的文件是csv的,按道理说改为.xlsx然后再导入也没有问题,但是这个csv文件的大小是1个多G,行数超过了200多万,而在win平台下,打开excel文件最多显示104万多行,转为excel后就丢失了后面的数据,导入也是缺失的数据,因此这是没用的.

我尝试改变了数据库的编码和数据表以及字段的编码,照着网上的一些做法,操作了一些下面的命令:

SHOW VARIABLES LIKE 'character%'

![70][]

于是网上说csv文件可能是gbk编码,我操作了下面

SET character\_set\_client='gbk';  
SET CHARACTER\_SET\_CONNECTION='gbk';  
SET character\_set\_database='gbk';  
SET character\_set\_results='gbk';  
SET character\_set\_server='latin1';  
不管用,于是改回来  
SET character\_set\_client='utf8';  
SET CHARACTER\_SET\_CONNECTION='utf8';  
SET character\_set\_database='utf8';  
SET character\_set\_results='utf8';  
SET character\_set\_server='utf8';

改为utf8不行,改为gbk,又改为gb2312,改什么my.ini配置文件和data目录下的相应的数据库的db.opt都是不行,我就奇怪了,到底哪里的问题,我同事用mac导入,根本都没有这个问题,到底问题出在哪里,我始终没解决,最后尝试用cmd导入,命令如下:

**LOAD DATA LOCAL INFILE 'D:\\\\w0307\\\\11.csv'  
INTO TABLE \`历史数据导出 - 广东\_csv\` CHARACTER SET utf8**

**FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'**

**LINES TERMINATED BY '\\r\\n';**

'D:\\\\w0307\\\\11.csv':是我本地csv文件的路径,

\`历史数据导出 - 广东\_csv\`:是导入的数据库的表名,这个表要提前新建好的,但是导入excel是不需要提前建表的,这里就有点弱了

下面说到的就是CHARACTER SET utf8,我还是不明白,因为我在用sqlyog进行设置表字段的时候,也是把表字段设置为了utf8的编码的,当时导入的数据确实是乱码,不然我也不会想到用cmd进行操作了,如果上述提醒你ERROR 1300 (HY000): Invalid utf8 character string: '',那么你就把CHARACTER SET utf8 改为CHARACTER SET gbk,别问我为什么,我不知道,亲测可行,并且建表的语句是default charset=utf8;截图为证明:

![70 1][]

![70 2][]

而且此时插入表里的数据没有一点乱码,截图为证:

![70 3][]

然后我又用ueditor打开看了一些csv文件的编码,却发现了这样的内容:

![70 4][]

难道说导入语句的character set utf8是指csv文件的编码吗?于是我又改变了csv文件的编码为utf8,导入的时候character set utf8果然是可以的了,看来这个编码是指csv文件的编码

但是这样导入,确实可以了,由于工作比较忙,就赶紧对数据进行curd等操作了,等有空还要仔细研究一下到底为什么,这里暂记一下.

接下来测试用命令行导出csv文件的问题,命令如下:

select \* from wz0308

into outfile 'D:\\\\wz0308.csv'

fields terminated by ',' optionally enclosed by '"' escaped by '"'

lines terminated by '\\r\\n';

但是结果会出现以下错误:

# The MySQL server is running with the --secure-file-priv option so it cannot execute this statement #

在网上查的一下原因,大概是没有mysql配置问题,网上这么说:

show global variables like '%secure%';

结果是:secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"

那么在我的my.ini里面的配置是这样的,网上说前面加\#注释掉就行了,我测试并非如此,查了原因,网上这么说:

*  secure\_file\_priv的值为null ，表示限制mysqld 不允许导入|导出
 *  当secure\_file\_priv的值为D:/XXXX ，表示限制mysqld 的导入|导出只能发生在D:/XXXX目录下
 *  当secure\_file\_priv的值没有具体值时，表示不对mysqld 的导入|导出做限制

那么我此时是

![70 5][]

那么说明我用mysqld根本都没有权限了,奇怪我设置了给注释了,变成了null,本来是想不限制目录的,现在是直接把权限给关了,

转念一想,null和没有值是不一样的,于是在关闭mysql服务后,在my.ini中设置为了下面一行:

![70 6][]

现在是这样,重启mysql服务后:

![70 7][]

再次执行:

![70 8][]

表示导出已经成功了.

但是查看文件:

![70 9][]

发现是没有表头的,也就是字段名没有标上去,查看网上的教程,那么就是要查询的时候先查出一行,把表字段名给手动查出来,语句如下:

![70 10][]

执行是成功的,那么看看效果:

![70 11][]

看来是有了

[70]: /images/20220530/d67e5ddd313b4e578bb1e66c8ec7e7c3.png
[70 1]: /images/20220530/12fcd397ab4d498a9d3e57988faeac6b.png
[70 2]: /images/20220530/8c4c415d67c440e6bfc905c5f75beadb.png
[70 3]: /images/20220530/dcc730d7547e4901901d69e9a8918804.png
[70 4]: /images/20220530/59cd7c57cbaa4359925d2e556343675c.png
[70 5]: /images/20220530/128961d2672b4dd49c8a9cf8b3fba013.png
[70 6]: /images/20220530/e547b555cd254cadb37abbeecd23d812.png
[70 7]: /images/20220530/be3aa09abcb2429182228da665c0fe4b.png
[70 8]: /images/20220530/2a2bd1941f1448f3a54995d2800314b4.png
[70 9]: /images/20220530/f78fb6f07b8a4cef9568d6196c58dcdd.png
[70 10]: /images/20220530/e0fcaf82bef94716b67cbcbf63e19c9b.png
[70 11]: /images/20220530/1faec2da81f1446ebf74f9b49100143c.png