最近需要导出线上的某些数据给业务人员,由于不能直接操作生产库,自己写了sql之后请DBA帮忙导出生产数据
得到了两个excel,但是dba跟我说其中一个文件太大导致无法打开(三十多万行的数据),我尝试了下,确实一打开就会提示文件已损坏, 如下:
从网上查了下excel单sheet完全是可以存储一百多万的数据,按理说30多万的数据量不应该影响文件的打开。由于无法直接将数据导出到excel文件给我(可能是使用的自带导出工具无法支持这么大数据量的导出),DBA 提供了一个txt文件,并让我自己使用Load File命令导入到本地数据库
// fields关键字指定了文件记段的分割格式
// terminated by分隔符:意思是以什么字符作为分隔符, enclosed by描述的是字段的括起字符。
// lines 关键字指定了每条记录的分隔符默认为'\n'即为换行符
LOAD DATA INFILE 'xx/data.txt' INTO TABLE xxxxx FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
尝试了下导入,一开始我将文件存放在桌面上,执行导入命令就会提示没有权限
于是我将文件存储位置改为D盘根目录,权限是有了,但是会报如下错误 :
感觉是文件的格式出了问题, 我看了下txt文件的内容,我的load file命令的意思是以","
为字段间的分隔符,"\n"
为行分隔符, 字段内容使用' " '
双引号括起来,但是txt的文件的行分隔符明显有问题,行分隔符和字段分隔符都使用了"\n"
, 这与dba说的不符。
猜测是由于表有text类型字段,导致最后生成的file文件格式有点问题, 尝试过将一行记录的字段间的换行符去掉,再次执行那一行就不会报错了,但明显不可能手动将所有的字段的分隔符修正掉。于是只能将生产环境的数据按照sql的方式导出并在本地数据库执行
找dba提供了下表内所有数据的insert sql, 选择使用SQLyog 工具导入 操作如下: 数据库–>导入–>执行SQL脚本,然后选择对应的sql文件执行即可
最后应该只要导出即可
选择 工具–>导出表中所有数据行/结果
但是导出的csv文件存在两个问题,乱码和格式不正确
乱码应该是由于文件编码不一致造成的(虽然不明白为什么我指定了csv的文件格式为utf-8还会出现乱码),我用记事本打开csv文件,选“另存为”
,在下方保存按钮旁边的"编码"框处选择utf-8, 然后再打开就没有乱码了,但是文件的格式还是有问题,出现了很多奇怪的""和空行
最后我使用了MySQL Workbench来导入sql脚本数据再导出,得到最终正确的数据文件
Workbench 导入数据 :
选择 File --> Run SQL Script, 然后选择db和对应的字符集执行,如下图
数据导出 :
查询出全表数据后 选择 Query–> Export Results, 就可以得到导出的csv文件了,同样该文件有乱码的问题,按照上面的步骤重新设置下文件的编码格式就可以了
终于成功将数据导出了(T_T)