mysql导出问题_mysql导出导入文件问题整理

使用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

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编码

#其中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