大数据表高效导入导出解决方案，mysql数据库LOAD DATA命令和INTO OUTFILE命令详解

一.问题背景

java项目基于sql>mysql实现大数据表跨服务器导入导出功能。之前整理过百万数据导入内存溢出的解决方案，这次情况又不一样了， 21万包含longtext字段的数据导入导出，解决了内存溢出的问题，速度却很慢，即使开启批量插入，sql解析也需要耗费很长时间。经过一番查阅资料，使用sql>mysql的load data infile/select into outfile命令直接操作csv文件，可以很好的解决大数据表导入导出的效率问题。

二.解决方案

1.load data infile/select into outfile命令

(1)简单导入导出sql

sql">-- 导出csv
SELECT * INTO OUTFILE '绝对路径.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM table_name;-- 导入csv
LOAD DATA INFILE '绝对路径.csv' into table table_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

注意：如果用Navicat执行命令，发现导出成功但找不到文件，或者明明目录存在却报错找不到指定路径，是因为sql>mysql默认加载路径为sql>mysql服务器路径，指定 LOCAL 可加载客户端文件。

(2)详细语法

sql">LOAD DATA[LOW_PRIORITY | CONCURRENT] [LOCAL]INFILE 'file_name'[REPLACE | IGNORE]INTO TABLE tbl_name[PARTITION (partition_name [, partition_name] ...)][CHARACTER SET charset_name][{FIELDS | COLUMNS}[TERMINATED BY 'string'][[OPTIONALLY] ENCLOSED BY 'char'][ESCAPED BY 'char']][LINES[STARTING BY 'string'][TERMINATED BY 'string']][IGNORE number {LINES | ROWS}][(col_name_or_user_var[, col_name_or_user_var] ...)][SET col_name={expr | DEFAULT}[, col_name={expr | DEFAULT}] ...]

参考官网：
https://dev.sql>mysql.com/doc/refman/8.0/en/load-data.html

2.变量secure-file-priv设置

使用以上命令做导入导出操作时，通常会遇到The MySQL server is running with the --secure-file-priv option so it cannot execute this statement报错，原因是执行此命令的文件所在的路径不在sql>mysql配置的指定范围内，通过改变系统变量secure-file-priv的值，可以变更可操作的文件范围。
secure-file-priv变量是只读的，不能通过set命令设置，需要修改sql>mysql的配置文件my.ini或my.cnf，重启sql>mysql服务生效。

(1)secure-file-priv变量值查询

sql">SHOW VARIABLES LIKE 'secure_file_priv';

(2)secure-file-priv变量值设置

(3)secure_file_priv可以设置如下：

设置值	操作范围	备注
空	可以操作所有文件	不安全。服务器在启动时检查secure_file_priv的值，如果该值不安全，则将警告写入错误日志。
绝对路径	该目录下的文件可执行导入导出操作	服务器不会自动创建目录，设置的目录必须存在。若目录不存在，服务器会将错误消息写入错误日志并退出。
NULL	禁用导入和导出操作

参考官网：
https://dev.sql>mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_secure_file_priv

三.权限及环境问题

1.权限问题

执行导入导出命令出现Can't create/write to file '/var/lib/myFile.csv' (OS errno 13 - Permission denied)报错，是目录操作权限不足。可直接通过命令赋予权限，或通过代码赋予权限。

java代码段

java">String osName = System.getProperty("os.name").toLowerCase();if (osName.contains("win")) {log.info("当前操作系统是Windows");File ff = new File(outPath);ff.setReadable(true, false); // 设置文件可读权限ff.setWritable(true, false); // 设置文件可写权限ff.setExecutable(true, false); // 设置文件可执行权限} else if (osName.contains("nix") || osName.contains("nux") || osName.contains("aix")) {// 这里的"nix"可以匹配Linux，"nux"可以匹配Solaris，"aix"可以匹配AIXlog.info("当前操作系统是Linux");Set<PosixFilePermission> permissions = new HashSet<>();permissions.add(PosixFilePermission.OWNER_READ);permissions.add(PosixFilePermission.OWNER_WRITE);permissions.add(PosixFilePermission.OWNER_EXECUTE);permissions.add(PosixFilePermission.GROUP_READ);permissions.add(PosixFilePermission.GROUP_WRITE);permissions.add(PosixFilePermission.GROUP_EXECUTE);permissions.add(PosixFilePermission.OTHERS_READ);permissions.add(PosixFilePermission.OTHERS_WRITE);permissions.add(PosixFilePermission.OTHERS_EXECUTE);Files.setPosixFilePermissions(Paths.get(outPath), permissions);} else {log.info("无法识别的操作系统: " + osName);}

2.环境问题

踩过以上坑之后，Linux环境不出意外可以顺利进行导入导出了。此处Windows环境还需注意一点，路径不能用单反斜杠\，需要转换为双反斜杠\\ 或斜杠/。单反斜杠会被转义掉，变成文件名，如下图所示。

sql示例

sql">-- 不能按指定路径导出文件
SELECT * INTO OUTFILE 'C:\Users\user\Desktop\demo\table_name.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM table_name;-- 可以按指定路径导出文件
SELECT * INTO OUTFILE 'C:\\Users\\user\\Desktop\\demo\\table_name.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM table_name;-- 可以按指定路径导出文件
SELECT * INTO OUTFILE 'C:/Users/user/Desktop/demo/table_name.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM table_name;

至此，问题解决。

以上为个人观点，仅供学习记录，欢迎交流讨论。