MYSQL导入数据出现The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
这个原因其实很简单,是因为在安装MySQL的时候限制了导入与导出的目录权限
只能在规定的目录下才能导入
我们需要通过下面命令查看 secure-file-priv 当前的值是什么
mysql> show variables like ‘%secure%’;
该图中 secure-file-priv 为空,表明可以任意保存路径
通过mysql客户端shell连接到服务器,选择使用的数据库,输入sql代码:
select 字段 from 表格 (筛选条件)
into outfile ‘/路径/路径.csv’
fields terminated by ‘,’ ------字段间以,号分隔
optionally enclosed by ‘"’ ------字段用"号括起
escaped by ‘"’ ------字段中使用的转义符为"
lines terminated by ‘\r\n’; ------行以\r\n结束
USE myemployees;SELECT*FROM employees;#查询下有什么SELECT*FROM employees INTOOUTFILE'C:/Users/Shineion/Desktop/tu/shuju.csv'FIELDSTERMINATEDBY','ESCAPEDBY'"'OPTIONALLYENCLOSEDBY'"'LINESTERMINATEDBY'\r\n';
SELECT*FROM employees WHERE job_id='AD_VP'INTOOUTFILE'C:/Users/Shineion/Desktop/tu/shuju.csv'FIELDSTERMINATEDBY','ESCAPEDBY'"'OPTIONALLYENCLOSEDBY'"'LINESTERMINATEDBY'\r\n';
解释下:SELECT ‘表头别名1’,‘表头别名2’ UNION SELECT 字段1,字段2 FROM 表格 筛选条件
1.SELECT ‘表头别名1’,‘表头别名2’ :手动写表头字段别名
2.UNION:联合
3.SELECT 字段1,字段2 FROM 表格 筛选条件 : 筛选数据
即左表头 右 数据,中间union
算例
SELECT*INTOOUTFILE'C:/Users/Shineion/Desktop/tu/shuju2.csv'FIELDSTERMINATEDBY','FROM(SELECT'员工编号','邮箱'UNIONSELECT employee_id,email FROM employees) b;
1
2
3
运行成功
查看文件
中文乱码 还是改用英文吧
添加筛选条件
SELECT*INTOOUTFILE'C:/Users/Shineion/Desktop/tu/shuju2.csv'FIELDSTERMINATEDBY','FROM(SELECT'员工编号','邮箱'UNIONSELECT employee_id,email FROM employees WHERE job_id='AD_VP') b;