MySQL导出导入csv文件

本打算用navicat for mysql（version10.0.11）直接导出csv文件，但发现‘导出数据’对话框中并无’.csv’选项，只好选择命令语句导出csv，但在这个过程中遇到了一些问题，特作此记录。

导出csv文件

导出数据到csv基本语句

SELECT * FROM 表名

INTO OUTFILE '导出目录及文件名'   

FIELDS TERMINATED BY ','　　　#字段间以,号分隔

OPTIONALLY ENCLOSED BY '"'　　#字段用"号括起

ESCAPED BY '"'   　　　　　　#字段中使用的转义符为"

LINES TERMINATED BY '\r\n';　　#行以\r\n结束

"secure-file-priv"错误原因及解决方法

secure_file_priv参数用于限制LOAD DATA, SELECT …OUTFILE, LOAD_FILE()传到哪个指定目录。

在我运行上述导出语句后，提示ERROR:
The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

经查询，错误原因是在安装MySQL的时候限制了导入与导出的目录权限。
我们可以通过show variables like '%secure%';命令查看‘secure-file-priv’ 当前的值是什么。

• secure_file_priv 为 NULL 时，表示限制mysqld不允许导入或导出
• secure_file_priv 为 /tmp时，表示限制mysqld只能在/tmp目录中执行导入导出，其他目录不能执行
• secure_file_priv 没有值时，表示不限制mysqld在任意目录的导入导出

可以看到，我的secure_file_priv 为 NULL，表示限制不能导入导出。为了改变MySQL的导入导出权限，需要打开my.cnf 或 my.ini（一般在MySQL安装路径主目录下），加入以下语句，重启MySQL即可。
secure_file_priv='' #不限制MySQL在任意目录的导入导出

MySQL的重启

MySQL的重启不是关闭cmd或navicat在打开就好，必须依次执行以下命令才能够重启：
net stop mysql #关闭mysql服务
net start mysql #打开mysql服务

mysql导出目录的进一步说明

重新执行下图的导出csv命令后，没有报错，说明文件导出成功，但是在桌面我却没有看到‘person.csv’文件，最终在C盘的根目录下发现‘UsersAdministratorDesktopperson.csv’文件，说明程序将‘Users\Administrator\Desktop\person.csv’整个视为文件名。然后我将语句中的目录路径中的‘\’换为’/’，成功在桌面导出‘person.csv’文件。可对比下面两张图。


因此，在导出数据过程中，导出目录需要使用斜杠‘/’而非反斜杠‘\’。此外还可以直接输出INTO OUTFILE '\文件名' ，这是文件直接输出到该数据库目录下，即’\MySQL安装目录\data\数据库名\文件名’。

导入csv数据

导入数据基本LOAD DATA 语法

LOAD DATA INFILE '导入目录及文件名'   

INTO TABLE 表名

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

ESCAPED BY '"'  

LINES TERMINATED BY '\r\n';

Duplicate entry ‘1’ for key 'PRIMARY’错误及解决方法

如上图所示，在向person表中导入数据时，提示出错“Duplicate entry ‘1′ for key ‘PRIMARY’ ”，错误原因是在一张数据表中是不能同时出现多个相同主键的数据。解决方法有：

1. IGNORE
   load data infile "目录及文件" ignore into table 表名;
2. REPLACE
   load data infile "目录及文件" replace into table 表名;

replace和ignore关键词控制对现有的唯一键记录的重复的处理。如果你指定replace，新行将代替有相同的唯一键值的现有行。如果你指定ignore，跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项，当找到重复键时，出现一个错误，并且文本文件的余下部分被忽略。

在网上找了好久解决方法，但大多数时INSERT插入数据时的解决方法，在这里分享一篇LOAD导入数据的文章：mysql导入数据load data infile用法

我分别试验了IGNORE与REPLACE关键字，下面两张对比可以看到，IGNORE受影响的行数为0；而REPLACE受影响的行为3。