MySQL中数据导入导出问题总结

2018-12-26更新

#使用into outfile参数可将MySQL表中数据导出CSV

select a,b into outfile 'C:/tmp/user.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n' from user
#fields terminated by '字段间分隔符'
 [optionally] enclosed by '字段包围符',只能是一个字符(optionally-char/varchar被包含)
 lines terminated by '行间分隔符',\r\n表示ctrl+回车
 escaped by '\\'   用'\'作为转义字符(当字段中出现制表符,新行或\时)
#注:Excel转CSV时,有特殊字符(逗号/顿号等)的字段,会自动用双引号引起来



#输出CSV带上列名
select * into outfile 'C:/home/gg_tem.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n' from (select '编号','用户名' union all select Order_No,Buyer_Member_Name from order_details_15 limit 30) tem

###error

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

解决方案:secure_file_priv参数用于限制LOAD DATA, SELECT... into OUTFILE, LOAD_FILE()传到哪个指定目录;secure_file_priv为NULL表示限制mysqld不允许导入导出,

secure_file_priv为/tmp表示限制mysqld只能在/tmp目录中执行导入导出,其他目录不能执行

secure_file_priv没有值表示不限制mysqld在任意目录的导入导出

show global variables like '%secure_file_priv%'

##打开my.cnf或my.ini,加入以下语句后重启mysql

secure_file_priv=''

 

#error

显示ERROR 1261 (01000):Row 1 doesn't contain data for all columns这个错误是因为数据行不匹配(默认不能有空)用下列命令解决set sql-modul = 0(建议去my.ini修改)

 

##使用SQL导入CSV数据到MySQL数据库

load data infile '\要导入的文件名'
into table 表名
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n'
IGNORE 1 LINES

 

--举例1
LOAD DATA INFILE 'F:/worksp/mysql/discounts.csv' INTO TABLE discounts FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS
##导入时转换数据
LOAD DATA INFILE 'F:/worksp/mysql/discounts_2.csv' INTO TABLE discountsFIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS
(id,title,@expired_date,amount)
SET expired_date = STR_TO_DATE(@expired_date, '%m/%d/%Y')

--举例2
load data infile 'C:/home/order_details_18.csv' into table order_details_18
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n'
IGNORE 1 lines

--举例3
LOAD DATA INFILE 'D:\\ports.csv' INTO TABLE ports character set gbk2312  FIELDS TERMINATED ',' ENCLOSED BY '”' LINES TERMINATED BY '\r\n'