MySQL导入导出csv文件
导出csv文件语句结构
select * from j_position_provice
into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/xx.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
导入scv文件语句结构
load data infile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/output2.csv'
into table a_test
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\r\n'(code_id,name);
注意事项
-
1.secute-file-priv
-
ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement
在导出csv的时候，有可能会抛出如上的错误，这是由于导出的时候文件夹的权限不够。
方法一：我们可以在命令行或者工具下查询路径的权限，语法如下
show variables like '%secure%'
找到secure_file_priv的路径，并在我们SELECT语句的INTO OUTFILE字句中使用该目录
SELECT * FROM xxxx WHERE XXX
INTO OUTFILE '/var/lib/mysql-files/report.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
方法二：查找可操控安全文件夹位置
找到C:\ProgramData\MySQL\MySQL Server 5.7\my.ini这个文件并打开，我们可以发现，在MySQL5.6以后，secure-file-priv这个新版本的MySQL Server中添加了一个默认值：
secure-file-priv=”C:/ProgramData/MySQL/MySQL Server 5.6/Uploads”
如果你处于非生产环境，可以尝试修改，修改之后记得重启MySQL服务，此时，我们将所需要导入的文件放到这个目录下，就可以进行导入，我安装的是MySQl Server 5.7版本，所以我对应的导入语句如下：
select * from j_position_provice
into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/xx.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
-
2.导入文件时，文件的名称和数据库表名尽量一致
-
如果数据库有多个字段，而且其中有些又有没用到，可以将具体的字段写在导入语句上，好将csv的列和表字段匹配上
load data infile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/a_position.csv' into table a_position
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'(code_id, name, parent_code_id);
