使用mysql处理文件导入导出如下
#导出文件
SELECT * FROM MY_TABLE INTO OUTFILE 'FILE_PATH';
#导入文件
LOAD DATA INFILE 'FILE_PATH' INTO TABLE MY_TABLE;
一般情况下会遇到如下问题
The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
在此记录解决办法，以便以后查阅（主要参考How should I tackle --secure-file-priv in MySQL?）
- Move your file to the directory specified by secure-file-priv
- Disable secure-file-priv: This must be removed from startup and cannot be modified dynamically. To do this check your MySQL start up parameters (depending on platform) and my.ini.
方法1. 把导入导出的文件存放到 secure-file-priv 路径下即可
非常详细的方法2步骤（亲测可行）：
- Stop the MySQL server service by going into services.msc
- Go to C:\ProgramData\MySQL\MySQL Server 5.6 (ProgramData was a hidden folder in my case).
- Open the my.ini file in Notepad.
- Search for 'secure-file-priv'.
- Comment the line out by adding '#' at the start of the line.
- Save the file.
- Start the MySQL server service by going into services.msc
附1：
#更详细的导出文件
SELECT * FROM MY_TABLE
INTO OUTFILE 'FILE_PATH'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
#更详细的导入文件
LOAD DATA INFILE 'FILE_PATH'
INTO TABLE MY_TABLE
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
附2：
解决导出csv中文乱码问题（亲测可行）：将csv用txt打开，另存为，选择utf8编码即可
解决导入中文乱码问题（未测）：mysql安装目录下的my.ini（参考上面），增加如下参数：
[client]
default-character-set=utf8
[mysqld]
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci
同理，你也需要将你要导入的csv用上面提到的方法另存为utf8编码
事实上我把csv编码改为utf8后用如下方法导入并没有遇到中文乱码的问题（同时记录一下当导入字段中有自增字段该如何处理：How to LOAD DATA INFILE in mysql with first col being Auto Increment?）
#其中id为自增的primary key
LOAD DATA INFILE 'train_utf8.csv'
INTO TABLE train
FIELDS TERMINATED BY ','
(province, station, station_code)
SET id = NULL;
导出所有数据
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqldump -h ipAddress -u userName -p DBName > fileName
